The unexpected client impersonation scenario that seemed to be only justified by the effects of black magic

  • Article

For whatever reason whose understanding has been lost over time it has been the case since SQL Server 2000, at the very least, that when a client attempted to log in into an instance of SQL Server using any data access library other than OLEDB, and neither explicitly specified in the login packet that it wanted to be authenticated using Windows authentication, nor specified a SQL Server login name, the server side code that processes that logon request would attempt to impersonate the client, by means of leveraging the authentication already made by the network library layer (provided it was named pipes, which is the only network library that requires the client to authenticate in order to be able to successfully open the pipe).

Because of that implementation, there is an scenario that returns an error whose cause is hard to diagnose given the information returned to the client plus the information written to the ERRORLOG/EventLog/XEvent/SQLTrace.

That scenario is one where:

  1. the client tries to connect to SQL Server from an application using ODBC,
  2. doesn’t explicitly specify its desire to use Windows authentication, by means of setting the SQL_COPT_SS_INTEGRATED_SECURITY connection attribute to SQL_IS_ON, or setting the Trusted_Connection attribute to yes in the connection string or in the DSN definition,
  3. doesn’t explicitly provide a user name to use as the SQL login name,
  4. uses any network library where impersonation is not supported (i.e. any but named pipes),
  5. SQL Server is configured for mixed authentication.

In such case, any attempt to connect the client will report, back to the client, error 18456 (LOGON_FAILED, Login failed for user '%.*ls'.%.*ls%.*ls) with no specific reason. In the server side, the error can be reported to ERRORLOG, Event Log, an XEvent session, a SQL Trace session, with additional details. Those details being the given reason why the logon failed. In this case, the reason reported is 18357 (PH_WINAUTHONLYERR, Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.).

The reason is definitely incorrect and causes confusion to whoever is trying to troubleshoot it.

Besides the reporting of the incorrect reason definitely doesn’t help troubleshooting this error, the problem becomes more elusive when, as it was the case in the customer who reported this problem to me, the exact same application was working from some machines but not from others. Their application didn’t explicitly say, through the corresponding connection attribute, which network library had to be used. In those cases, the ODBC driver in their case resorted to finding what the preferences (and in what order) were set for the machine from where the application was invoked. In some machines they had TCP/IP as the first option (it was in these machines where they saw the error because the TCP library on the server couldn’t impersonate the client), and in others Named Pipes (it was here where the application worked like a charm.)