Systems Engineering and RDBMS

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:

sec_21

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:

sec_22

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:

sec_23

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.

About these ads

36 Responses to “The user is not associated with a trusted SQL Server Connection”

  1. hector gardea said

    Muy buena ayuda!!!!!

  2. skk said

    Good. really helped

  3. Cyril Coombs said

    Can this also occur if authenticating using windows authentication and the account is locked on the domain?

  4. Kris said

    Have you ever run across this from the client side, with a Samba PDC?

  5. 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.

  6. Hi, I faced this problem number of times. but this it helps me to solve my problem.

  7. John said

    Thanks a lot, such a simple one, but a real show stopper :)

  8. mahantesh said

    hi,,,,
    i tried with this option ,, still it gives the error…

  9. Mandrake said

    Good tips – certainly helped me out

  10. serg said

    Thanks. This article was very useful for me!

  11. Kenny said

    Thanks! After a re-format and a bunch of reinstalls, this was my last little hiccup.

  12. 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.

  13. 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.

  14. Rambhopal said

    Helps me lot this article

  15. [...] for a particular SQL Server InstanceFunctional difference between "NOT IN" vs "NOT EXISTS" clauses The user is not associated with a trusted SQL Server ConnectionDisable/Enable Foreign Key and Check constraints in SQL ServerTemporary Tables – MS SQL ServerMoving [...]

  16. ccc said

    Thanks :D

  17. Anonymous said

    thanks !

  18. Anonymous said

    thank!

  19. Sammy said

    Didn’t work for me, any user I add under the logins, and when i try to login with that account, it gives me same error message. Am i doing something wrong? I cant even use the sa account. Is there a default pw for that account?

  20. Endy said

    Great post.. thanks for sharing :D

  21. Sachin said

    Great post…. This article was very useful for me..

  22. Sally said

    Great – thanks!

  23. Anonymous said

    thank you for help!!!!

  24. Anonymous said

    This can also happen if the correct client connectivity software isn’t installed. In my case, I was trying to connect FRx Data Refresher via Microsoft OLE DB for SQL server. I ended up having to use the SQL native client instead.

  25. Anonymous said

    Very good article, it helped me a lot. Thank you!

  26. Richard. said

    Ran into the “Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)” error after a AD migration to a new domain; servers were left in the old domain but user account migrated to a new AD with shared trust in place between the two. A resolution for us involved forcing connections to use named pipes as per “http://idunno.org/archive/2007/01/24/Using-SQL-Trusted-Connections-from-a-machine-not-in-a.aspx” simply by prefixing the server name with “np:”.

    • B said

      @Richard. Sick dude thanks for that fix. Resolved and issue here at work today. Awwwww yeah~

    • Anonymous said

      Richard, you’re a life-saver. We’re going through an AD migration and this saved me an a lot of other people some serious stress. Thank you!

  27. ROBERT said

    Thanks a lot —- you’re my HERO!!!

  28. My name said

    thanks a lot

  29. thillai said

    thankssir….

  30. Shah MOHAMOD said

    Another reason which may cause the error is the expiry of the Windows AD account

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: