Search code examples
sql-serverwindows-authentication

Windows authentication works by default with SQL Server, when empty username/password are passed

I'm implementing Windows Authentication feature with SQL Server, for my project and faced this strange problem.

In the connection string, I have added a new parameter Trusted_Connection and if we have to enable the Windows authentication feature I'm setting it as yes otherwise setting it explicitly no. Even it should be by default no.

The feature was working fine until I tried passing an empty username and password in the connection string:

Driver={SQL Server};Server=localhost;Database=test;UID=;PWD=;Trusted_Connection=no

Strangely, this established a connection with the database and it seems to use the Windows user authentication. Tested by running from some other user who doesn't have required permissions and it failed.

The API used to connect to the SQL Server is:

SQLDriverConnect(
    hDbc,
    NULL,
    ConnStr,
    SQL_NTS,
    NULL,
    0,
    NULL,
    SQL_DRIVER_COMPLETE
);

Is this expected behaviour of this API? If so, is there any link where I could get this info?

Thanks in advance.

Solution

  • The "SQL Server" ODBC driver that ships with Windows is a legacy driver provided only for backwards compatibility. It interprets an empty UID as a request for Windows authentication regardless of the Trusted_Connection specification. The modern drivers (SQL Server Native Client and ODBC Driver n for SQL Server) honor the Trusted_Connection specification.

    Thus using a "ODBC Driver 11 for SQL Server" solved my problem.