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

Article
05/02/2008

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

Comments

Anonymous
May 02, 2008
a wonderful explanation about the error message: “Login failed for user ''. The user is not associated
Anonymous
June 11, 2008
The comment has been removed
Anonymous
July 02, 2008
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.
Anonymous
July 04, 2008
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.
Anonymous
September 07, 2008
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?
Anonymous
September 08, 2008
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.
Anonymous
September 24, 2008
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.
Anonymous
November 16, 2008
@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?
Anonymous
November 17, 2008
For me, adding the NT AUTHORITYNETWORK SERVICE user solved the problem
Anonymous
December 03, 2008
The comment has been removed
Anonymous
May 12, 2009
The comment has been removed
Anonymous
May 14, 2009
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 !
Anonymous
May 28, 2009
Good listing of the probable causes of this error msg.
Anonymous
June 17, 2009
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.
Anonymous
July 09, 2009
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.
Anonymous
July 10, 2009
This helped a bunch. Saved me about a hour of time! The solution was to add a domain account in my situation.
Anonymous
August 12, 2009
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.
Anonymous
September 23, 2009
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

Open Command Prompt
Type the following command: rundll32.exe keymgr.dll, KRShowKeyMgr
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.

Anonymous
September 29, 2009
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
Anonymous
October 20, 2009
++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.
Anonymous
October 28, 2009
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. Thanks.
Anonymous
November 18, 2009
Question for reason# 5, do you mean the machine has dual booting and has multiple names which can cause the problem? I have this problem, my machine is member or a workgroup and the application uses windows auth which fails each and every time.
Anonymous
April 24, 2010
even i encountered the same problem with login failed for sql server 2005. at last got the solution: for your database in any particular directory use the below code: Dim con As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersM K SinghDesktopBook StoreBookStore.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True") In any other directory: use AttachDBFilename:|DataDirectory|Database.mdf Gadi.Sridhar APIIT SD INDIA HAPPY CODING.
Anonymous
May 04, 2010
Thanks k2ace for your solution, it worked for me!
Anonymous
September 22, 2010
My resolution of this problem was: open regedit

go to path: HKLMSystemCurrentControlSetControlLSA
add DWORD „DisableLoopbackCheck”
set value to 1

Anonymous
October 06, 2010
I have installed sql server 2005 in mixed mode authentication with domain as user account as administrator of the domain as instance. so everything is fine on server Now i am trying to connect the client pc to sql server thru odbc connection. i am using dsn so while creating dsn i have selected the server as sql server instance and with sql server authentication using login id and password entered by user . then i have clicked the checkbox connect to...there i have given the username as sa and given the password but it is giving me error as user sa is not associated with a trusted sql connection. Then i have tried with user as client user and his domain password still it is giving me the same error can you help me out?
Anonymous
January 18, 2011
I received this error message because the web server was not resolving the sql server's server name to an FQDN. Modified the host file to do so and it fixed the problem.
Anonymous
January 26, 2011
The comment has been removed
Anonymous
February 10, 2011
Spot on, k2ace. I had changed my AD password on another computer, but never locked my local computer (that I was trying to connect from) for the credentials to take effect. Thanks much!
Anonymous
March 24, 2011
I'm getting this error from my local SQL Express only when connected to my corporate VPN. If I disconnect from the VPN it connects and it connects fine from the office. Any ideas?
Anonymous
March 24, 2011
Also, this problem didn't occur with the same code running under the .NET Framework 3.5 but started hyappening under .NET Framework 4.
Anonymous
April 27, 2011
The comment has been removed
Anonymous
July 25, 2011
One more possible reason for this: if you assign client server alias based on computer IP instead on its (net) name, therefore do not use IP(s) for client aliases, just type acccessable net name.
Anonymous
August 01, 2011
The comment has been removed
Anonymous
August 08, 2011
I am trying to connect to a database from my C# application on another machine. It is not part of the same workgroup. how do I change my SQL 2008 R2 instance or my connection string in the C# app to fix this error?
Anonymous
October 26, 2011
Please try to give the username and password while getting the connection. For Example : Connection conn = DriverManager.getConnection("jdbc:odbc:student_dsn","testadmin","admin"); Regards, I.Manoharan.
Anonymous
November 22, 2011
Hi, we receive "SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed" after our domain controllers was restarted due to WSUS updates (not all DC:s at once). We are running SQL 2008 SP1. Is this a default design in SQL not using other DC for authentication if one is down? This mean that our BizTalk servers is "loosing" the SQL and all hosts is going down. Could you config SQL to use multiple DC? Regards, Pontus
Anonymous
March 10, 2012
The comment has been removed
Anonymous
April 04, 2012
I have also getting same prob "Login failed for user"
Anonymous
June 24, 2014
My server has 2 names, Changing the name of server solved it.
Anonymous
September 07, 2014
Very useful information. We took advantage of the suggestions. http://interlex.com.pl/. Thank you for help.
Anonymous
September 22, 2014
This problem can also happen if the user's password has expired, but they are still logged in. It is resolved when they change their password.
Anonymous
November 26, 2014
Stuck on this "Login failed for user'' for couple month. This error occurs when i try to load windows form that contains tableadapter object. If i'm using sql query to load data on datagridview, i dont get that error. Pls help..thanks !! Note: This error pop up on client PC which is different machine where the sql server installed.
Anonymous
February 22, 2015
I was really satisfy by your information. It's well-written, to the point, and relative to what I need. thank you for providing information on . <a href="www.skillquotient.net/"><b>Qlikview Training</b></a> | <a href="www.skillquotient.net/"><b>Microsoft Dynamics CRM Training</b></a>
Anonymous
March 10, 2015
The comment has been removed

Share via

Comments

Additional resources