Understanding the error message: “Login failed for user ''. The user is not associated with a trusted SQL Server connection.”

Understanding the error message: “Login failed for user ''. The user is not associated with a trusted SQL Server connection.”

This exact Login Failed error, with the empty string for the user name, has two unrelated classes of causes, one of which has already been blogged about here: https://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx. In addition to an extra space in the connection string, the other class of causes for this error message is an inability to resolve the Windows account trying to connect to SQL Server. This list is not intended to be exhaustive, but here are several known root causes for this error message.

1) If this error message occurs every time in an application using Windows Authentication, and the client and the SQL Server instance are on separate machines, then ensure that the account which is being used to access SQL Server is a domain account. If the account being used is a local account on the client machine, then this error message will occur because the SQL Server machine and the Domain Controller cannot recognize a local account on a different machine. The next step for this is to create a domain account, give it the appropriate access rights to SQL Server, and then use that domain account to run the client application. Note that this case also includes the special accounts “NT AUTHORITYLOCAL SERVICE” and “NT AUTHORITYNETWORK SERVICE” trying to connect to a remote SQL Server, when authentication uses NTLM rather than Kerberos.

One very common case where this can occur is when creating web applications with SQL Server and IIS; often, the web page will work during development, then errors occur with this message after deploying the web site. This occurs because the developer’s account has access to SQL Server, but the account IIS runs as does not have access. To fix this specific problem, refer to this kb article about impersonating a domain user in ASP.NET: https://support.microsoft.com/kb/306158

2) Similar to above: this error message can appear if the user logging in is a domain account from a different, untrusted domain from the SQL Server’s domain. The next step for this is either to move the client machine into the same domain as the SQL Server and set it up to use a domain account, or to set up mutual trust between the domains. Setting up mutual trust is a complicated procedure and should be done with a great deal of care and due security considerations.

3) This error message can appear immediately after a password change for the user account attempting to login. This occurs because of caching of the client user’s credentials. The next step here is to log out the application user with the old password, and re-login with the new password before running the application.

4) If this error message only appears sporadically in an application using Windows Authentication, it may result because the SQL Server cannot contact the Domain Controller to validate the user. This may be caused by high network load stressing the hardware, or to a faulty piece of networking equipment. The next step here is to troubleshoot the network hardware between the SQL Server and the Domain Controller by taking network traces and replacing network hardware as necessary.

5) This error message can appear consistently for local connections using trusted authentication, when SQL Server’s SPN is not interpreted by SSPI as belonging to the local machine. This can be caused either by a misconfiguration of DNS, or by a machine having multiple names. If your machine has multiple names, try to work around the need for multiple names and give it a unique name. If the machine just has one name, then check your DNS configuration.

Dan Benediktson
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights