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

Archives

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: http://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 AUTHORITY\LOCAL SERVICE” and “NT AUTHORITY\NETWORK 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: http://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

Posted: Saturday, May 03, 2008 1:28 AM by SQL Protocols

Comments

Hosam Kamel said:

a wonderful explanation about the error message: “Login failed for user ''. The user is not associated

# May 3, 2008 5:35 AM

Helge Rutz said:

I just had trouble with this error message after rebooting a domain controller.

For SQL Server I get the following log entries:

SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed.

But in Security Enventlog i also get the following error:

The kerberos subsystem encountered a PAC verification failure.

This indicates that the PAC from the client SVC-Prod-03 in realm LOCAL.NET had a PAC which failed to

verify or was modified. Contact your system administrator.

And I got these errors still 5 minutes after the DC was up and running again.

What is so special with authentication in SQL Server that I get this kerberos error?

# June 11, 2008 10:01 AM

greg aiken said:

can anyone point me to a definitive page that outlines exact steps to confirm a properly installed and configured 'sqloledb(.#)' client connectivity library exists on a computer?

thanks in advance.

# July 2, 2008 4:36 PM

Eric Newton said:

FYI Sometimes its as simple as "SQL Authentication" wasn't enabled when the server was setup.

To fix it (and establish the sa password), go to the server properties in the Management Studio and enable SQL Authentication.

# July 4, 2008 1:46 PM

dave choi said:

Thanks for this. But I do have an issue not listed. When I use the integrated security=true connection string my domain account works fine. However when I use my login and password in the connection string, I get "login failed for user ...". Any ideas?

# September 7, 2008 11:40 PM

Stoyko Kostov said:

If you use login and password in the connection string, the connection assumes SQL login and password, not Windows login and password. For example, if SQL authentication is enabled and there is a SQL user "user1" with password "pwd1", you can use "user id=user1;password=pwd1" in the connection string to connect. DO NOT use "integrated security=true" in this context. If you use "integrated security=true", DO NOT specify your Windows domain account credentials.

# September 8, 2008 11:17 AM

Fergal said:

I have BizTalk attempting to communicate to SQL 2005 on the same server but getting this error when it uses a connection string set in the Enterprise Library. I can verify that this connection works correctly but when executed via biztalk it fails with this error.

# September 24, 2008 12:36 PM

Chani said:

@Stoyko: so... if specifying hte name and password will make it not use windows authentication... how do I make it use windows authentication *and* give it my windows name and password?

# November 16, 2008 10:30 PM

remote said:

For me, adding the

NT AUTHORITY\NETWORK SERVICE user solved the problem

# November 17, 2008 1:23 PM

Lutz said:

There must another case where we this message is issued as well.

I have a SQL Server 2005 that has Mixed Authentication enabled and remote connection enabled.

I have users both in my domain and connection from outside the domain that I have added as users to SQL.

In my client app that uses the Microsoft Enterprise Library and .NetTiers, I have a connection string that uses Integrated security and attempts a SQL operation. If that fails, I prompt the user, change the connection string to one that uses the userid/pwd and try again.

For a user inside the network, I can connect with both variations of the connection string (I forced SQL Auth for kicks once) and I can see that in the SQL Profiler. The exact connection string I'm trying is:

Data Source=10.52.xx.xx;Initial Catalog=SomeDB;Trusted_connection=false;Integrated Security=false;User Id=Usern;Password=pwd

For a user outside the network, they see the dialog, enter their User/Pwd combo and that connection fails with the error message mentioned. The Event viewer only shows the two entries for the failed NTLM attempt, SQL Profiler shows nothing. netstat shows a connection on port 1433 for that user. I've sniffed all the network packets during a login attempt, but did not see any connection string come through (I assume it's encrypted somehow).

I'm at a loss as to how to fix this.

Any ideas?

# December 3, 2008 11:35 AM

Ted said:

For me, logging in locally was failing using Windows Auth. The hosts file had 2 names for the same box. After removing the unused name, the connection worked. Thanks for the help.

# May 12, 2009 11:32 AM

Winson said:

I got the same error: SSPI handshake error and login failed as mentioned above.

i have tried and read alot , but finally i modified the local security policy .

go to Network access: change from Guest to Classic,

that's it !

hope it can help !

# May 15, 2009 12:42 AM

TR said:

Good listing of the probable causes of this error msg.

# May 28, 2009 1:24 PM

karolanet said:

Hi, I have this problem:

I have a SQLServer 2008 installed. This is not an update from SQL2005.

If I try to connect to it using the same provider as I use with SQL 2005 (SQLOLEDB.1) It doesn't work.

I have to use this provider: SQLNCLI10.1

Using it, I can connect to SQL 2008 throw an UDL file.

But when I try to use that UDL file to connect to my VB6 app, this error message appears: "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

In the other hand, I have 3 SQL2005 instance each one updated to SQL2008. I don't have any problem with them. I can use 'SQLOLEDB.1' provider and works fine.

I cannot install SQL2005 and update it to 2008 because it's on a server which I can't modify.

The SQL2008 instance has mixed mode. I can login from the SQL Server Manager Studio 2008, but not from VB6. Finally, I've made a DSN and VB6 can connect to that database throw this DSN but I need to connect by a ConnectionString since I don´t want to make a DSN in each end user machine.

Do you have any idea?

Thanks.

# June 17, 2009 5:01 PM

Munir said:

i m using this connection string.

Data Source=server;database=DB;integrated security=true;

sometime it is connecting and sometime it replies "Login failed for user"

any idea regarding this problem.

# July 10, 2009 3:36 AM

Shohn said:

This helped a bunch. Saved me about a hour of time! The solution was to add a domain account in my situation.

# July 10, 2009 5:04 PM

Lester said:

Yep, as like Shohn. Add a domain account in Sql. In my case I added a new HCUser with password HCUser and gave it read and write permissions as dbowner.

I set this account and password into my apps connection string. I'm still able to automatically authenticate and restrict the users in my app, but use this account access the db. I'll use this until I'm able to figure out how to propagate the credentials correctly to sql.

# August 13, 2009 4:04 AM

k2ace said:

One common issue I see that causes this exact error is cached network creds in windows. The quick fix for this is to run the following

1. Open Command Prompt

2. Type the following command:

rundll32.exe keymgr.dll, KRShowKeyMgr

3. Remove all the cached passwords

This is the only solution I could find to work on our locked build since the users run locked builds with limited rights.

# September 23, 2009 7:02 PM

Count de Roads said:

Thanks k2ace,

KRShowKeyMgr worked for me after I had accessed a network drive using a colleagues userid & password in windows explorer.

And then SQL Server Enterprise Manager would not connect & message

"login failed for user" & the wrong userid

# September 30, 2009 6:06 AM

K+Runa said:

++Thanks, k2ace. We were chasing this problem for a couple of days and you saved us from an OS reload.

Oddly enough, some of our internal ClickOnce applications which touch the database crash due to an unhandled exception because of this.

# October 21, 2009 7:23 AM

Charles said:

The keymgr fix from k2ace worked for me.

I have a dual-boot server (2003 and 2008) with a common user name login but different password. After logging in to 2008 I couldn't use Windows Authentication into SQL Server 2005 on 2003. I cleared the cached credentials and all is well again.