The user is not associated with a trusted SQL Server Connection
Posted by decipherinfosys on November 14, 2008
If you have worked with SQL Server for some time, you must have faced this error at some time or the other:
This error can come due to a couple of reasons. The most common reason is that the instance that you are trying to connect to has been set up as one with only Windows Only Authentication rather than Windows and SQL Server Authentication. You can check that easily by either by SSMS (SQL Server Management Studio) or in the registry settings as well (in case you do not have the client tools loaded up on the instance where you are getting this error and you inherited this set up from someone).
In SSMS, after you log in using your Windows Authentication, right click on the server and choose “Properties” and then go to the Security page and change the “Server Authentication” to be “SQL Server and Windows Authentication mode” as shown in the image below:
Once that is done, you will need to re-start the MSSQLServer service in order for the changes to take place.
The place where it gets stored in the registry is:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.2005\MSSQLServer
LoginMode – if it is 1, then it is Windows mode and if it is 2, it is mixed mode (Windows and SQL Server).
Now, other times you can also get this error with a blank user name i.e:
Login failed for user ”. The user is not associated with a trusted SQL Server connection
You can get that error in a couple of scenarios:
1) If the server name has a space after it’s name, example: in the connection string you have specified: servername ,1433 i.e. that extra space after the servername (the instance name).
2) Not being able to resolve the Windows account that is trying to connect to the SQL Server instance. Here are some scenarios that we have seen at client sites when this happens:
a) When using Windows authentication and the machine from where the connection is being made, instead of using a domain account, a local account on that machine is being used for making the connection to the SQL Server instance. This is obviously because the domain controller and SQL Server cannot recognize the local account on other machines.
b) In the case of web applications, the account that runs IIS does not have access to SQL Server. You have to impersonate a domain user account in ASP.Net for this. This KB article covers this in detail.
c) Another common reason is when communication is being made across domains which do not have a trust relationship set up between them.
d) We have also seen this in scenarios where because of heavy network load, communication between SQL Server and the Domain Controller is hindered.
hector gardea said
Muy buena ayuda!!!!!
skk said
Good. really helped
Cyril Coombs said
Can this also occur if authenticating using windows authentication and the account is locked on the domain?
decipherinfosys said
Haven’t run into that. Will create a domain account and lock it out and then see if we get the same error. Will post the results…
Kris said
Have you ever run across this from the client side, with a Samba PDC?
decipherinfosys said
Haven’t tried that Kris. Will see if we can set that up in our test lab and simulate it. And will post back about the results…
Erik Bitemo said
Hey, nice blog! A short addition: you may also experience this issue when the network access is restricted in the local security policy/group policy. Unfortunately I wrote it in Hungarian but you may find the Windows eventlog entry in English: http://blog.rollback.hu/2008/11/sql-integrate-authentication-es-a-szigoru-policy/. To resolve this issue, you should ensure that the person has the “Access this computer from the network ” privilege granted.
decipherinfosys said
Thanks Erik
Erik Bitemo said
So I took my time and translated the article to English: http://blog.rollback.hu/2009/07/sql-authentication-and-strict-computer-policy/.
Venkateswarlu Cherukuri said
Hi, I faced this problem number of times. but this it helps me to solve my problem.
John said
Thanks a lot, such a simple one, but a real show stopper
mahantesh said
hi,,,,
i tried with this option ,, still it gives the error…
Mandrake said
Good tips – certainly helped me out
serg said
Thanks. This article was very useful for me!
Kenny said
Thanks! After a re-format and a bunch of reinstalls, this was my last little hiccup.
Hans Wollerstein said
Thank’s for help. I thought I have mind all things, but the easiest point (the number 2a from your list) broke my neck. Now the installation runs very well. Thank you.
Giorgio said
What if the client machine (WinXP Pro) and the SQL SERVER machine (Also WinXP Pro)are members of a WorkGroup (No Domain controller)? BTW, I’m running SQL SERVER 2005 and I DO NOT WANT TO USE SQL SERVER AUTHENTICATION. I developed the client application in Visual Studio 2008 with VB.