You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

<UPDATE Date=November 26, 2007>

I find that a lot of people have been finding this article a little useful and at the same time finding it hard to get to the second part of the article.  So here below is the link for the Part II of this article. 

http://geekswithblogs.net/ranganh/archive/2005/05/25/40503.aspx

</UPDATE>

Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.

Choosing integrated authentication for ASP.NET applications is a good choice because no credentials are ever stored within your connection string for your application.

However, you may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" while trying to use Integrated Windows Authentication and your application needs to access the SQL Server Database using Trusted Connection.

Lets examine two scenarios where this error can occur.

1. Your IIS (Webserver) and SQL Server are on the same machine.

The first thing to check is that whether the SQL Server is configured to use Mixed Mode Authentication or Integrated Authentication.

To do that, do the following steps :-

1. Click Start - Programs - Microsoft SQL Server - Enterprise Manager to open the Enterprise Manager.

2. Connect to the appropriate Server if the SQL is a client installation.

3. Right click on the Server Node and click on Properties.

4. This opens the Properties Dialog.

5. Switch to the Security Tab.

6. Under the Security section, make sure the Option SQL Server and Windows is selected.

7. This ensures that your SQL Server is running under Mixed Mode Authentication.

The Next step is to ensure that the ASPNET account (IIS_WPG in case of Windows server 2003) has the appropriate access to the Database.

To do that, do the following steps:

1. Open SQL Server Enterprise Manager (Start - Programs - Microsoft SQL Server - Enterprise Manager), select the appropriate server, and expand the Security folder.

2. In the Logins check whether the IIS_WPG is listed.

3. If it is not listed, right-click on Logins and select New Login

4. In the Name: textbox either enter [Server/Domain Name]\IIS_WPG or click on the ellipses button to open the Windows NT user/group picker.

5. Select the current machine’s IIS_WPG group and click Add and OK to close the picker.

6. You then need to also set the default database and the permissions to access the database. To set the default database choose from the drop down list,

7. Next, click on the Database Access tab and specify the permissions.

8. Click on the Permit checkbox for every database that you wish to allow access to. You will also need to select database roles, checking db_owner will ensure your login has all necessary permissions to manage and use the selected database.

9. Click OK to exit the property dialog.

Your ASP.NET application is now configured to support integrated SQL Server authentication.

I will explain the next scenario of Webserver and SQL Server residing in different machines, in my
next article.

Read
Part II

Cheers and Happy Programming.

Print | posted on Wednesday, May 25, 2005 8:12 AM

Comments on this post

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Thanks a lot for the article!!!
It works wonderfully!
Left by Irma on Jun 07, 2005 10:37 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Could you email me when part 2 is out?
sqlerr.1.zdux0012[at]xoxy.net
Left by drew on Jun 27, 2005 7:14 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
You're awesome! I've been looking everywhere for a solution... yours was the only helpful one! thank you sooo much!
Left by part 1 on Jul 06, 2005 10:07 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Please email part 2 when it is out.
Left by msummerlin@wabtec.com on Jul 14, 2005 2:43 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
When the application is on the same computer as an instance of SQL Server 2000 then okay this is working fine but When the application is on a separate client, a network IPC, such as TCP/IP or Named Pipes, is used to communicate with SQL Server. then i have geting problem.
Please give me solution.


Left by Ajay Sharma on Jul 25, 2005 8:34 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
can you e-mail me part2. That's the situation here at work.

joe.lima@starz.com
Left by Joe Lima on Aug 02, 2005 8:29 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Thank you, I searched the web for quite a while, your blog was the only one that explained it properly. Thanks
Left by Mark Robinson on Aug 05, 2005 5:53 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Did anyone ever get part 2????

Thanks.

lnn714@hotmail.com
Left by LN on Aug 05, 2005 11:11 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Would really appreciate if you could expand this to include the second scenario: WebServer and SQL Server are on separate machines.

Many Thanks
Left by Faisal on Aug 16, 2005 8:33 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Hi, could someone email me 2. part, please.

selamiozlu@hotmail.com
sozlu@banktrade.com
Thank you
Left by Selami Ozlu on Aug 19, 2005 6:41 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
we are also experiencing th same issue can someon email me the 2nd part of this @ oa90@hotmail.com .... thanks a lot
Left by pinoydip on Sep 07, 2005 1:58 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Hi,
Can someone send me the part 2 ?
Or does anyone fix the problem when Web Server and SQL Server are on 2 different machine ?

thanks
melune23@msn.com
Left by Melanie on Sep 28, 2005 11:34 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
where is part Two ?
Left by Hopeisontheway on Oct 03, 2005 4:23 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Hi I am using WINDOWS XP but I always get the same error: Login failed for user even with doing all the changes you suggested in this article.
The error is Login failed for user 'Administrator'.]

This is the connection string I am using in ASP.NET (visual studio 2003):

Dim cn1 As New SqlConnection("Workstation ID=TUTUS;Data Source=TUTUS;Initial Catalog=atworkwebportal; user ID=Administrator")

where Tutus is my computer, and arworkportal is the database.

I d appreciate your help guys.
Left by R.Tutus on Oct 04, 2005 8:35 PM

# Integrated authentication SQL Server to leverage Windows NT authentication

Requesting Gravatar...
&nbsp;
Part I:
http://geekswithblogs.net/ranganh/archive/2005/05/25/40489.aspx
Part II:
http://harishmvp.blogspot.com/2005/05/you-may-receive-error-login-failed-for_25.htm...
Left by SQL Server on Oct 28, 2005 9:02 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
The error occurs only in asp.net with c# but in asp using macromedia it's working fine.
Left by balamurugan on Nov 24, 2005 2:00 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
In the browser try enabling Integrated Authentication. You may also wish to check your Kerberos setup - NTLM is expected to fail in a dblhop.

Skanda & Sunil
Left by Skanda Subramanian; Sunil Kumar on Dec 10, 2005 12:43 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
This is unbelievable. I've been trying to get this to work for i dont know how long. I always resort to sql authentication instead. All the other guides talk about adding the asnet-account and never mentiones iis_wpg. This worked as a charm first try. Thanks a million.
Left by Jonatan Rullman on Dec 25, 2005 8:49 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Sorry, i face problem at part 2. I don't know how to "Create a Domain Account with priveleges similar to ASPNET or NETWORKSERVICE.". Can someone help me?
Left by iris on Jan 06, 2006 6:17 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
did not work for me.. still get same error.. can sum1 give me a conn string they used.. my sql has a password.. thjs
Left by L on Jan 18, 2006 12:04 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
It did not work for me as I have a 2tier arch and the server is protected by firewall.
I tried using impersonating the logged in user with the account the firewall accepts but I get same frustrating error ... Please help me.
Left by Soumendra on Feb 23, 2006 2:47 AM

# SQL SERVER error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection"

Requesting Gravatar...
Connection failed:
SQLState: '28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'.. Reason: Not associated with a trusted SQL Server connection.

Note: This Error comes only in a single PC rest three PC Working properly
Left by Sanjay Chauhan on Feb 27, 2006 4:22 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...

I'm using SQL Server 2005 Express Edition, C# and Visual Web Developer and facing the same problem. Can anyone help me figure out in SQL Server 2005 Express Edition?
Left by Timorthy on Mar 05, 2006 2:20 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...

One more thing concerning is that I run those tools in the same computer. In SQL Server 2005 Express Editon, you can choose Either:

From the internet: Select this option if users will access your web site from the public internet. Users will be required to log on using a web form. The site will use forms authentication to identify users according to user information that you store in a database.

Or:

From a local network: Select this option if users will access your web site only from a private local network. The site will use built-in Microsoft Windows authentication to identify users. Users with a valid Windows user name and password will be able to access your site.

In the sql server, you cannot choose "Mixed Mode Authentication or Integrated Authentication".

Left by Timorthy on Mar 05, 2006 2:26 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Could anyone send me the Part II?

Thank you in advance.
Left by Pete on Mar 21, 2006 8:52 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
I am using XP Pro, IIS 5, MSDE (SQL Server) in which you dont have much facility to create or change options etc... so cant give access to ASP account... what should I do??
Both MSDE & IIS are on same machine...
Please help...
Left by rakesh on Mar 30, 2006 10:42 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
I get this error using a 3rd Party MS Access application. When I run from my machine, I am fine (I have admin rights on the SQL Server). When I run from a user's machine as the user, the access Application fails and gives me the Login '(null)' error this thread talks about.

Any solutions for MS Access??
Left by Jim on Mar 30, 2006 4:45 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Thanks a lot.It was really helpful...
How can I check ur messages continously or a forum where I can add myself and ask my other problems
Left by gudiya on Apr 20, 2006 11:53 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
This is just brilliant!!! Thank you so very much! I have really been at this forever!!! I had done bits and peices of it... but this article just puts it all together and tells you what you are missing!
Cheers!
Left by Munira on May 17, 2006 12:06 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
can someone send me the Part 2 i.e. scenario of Webserver and SQL Server residing on different machines. My email address: khawaja_hammad@acl.com.au
Thankx in advance.
Left by Khaaj on Jun 21, 2006 9:28 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
I hope this works, I am gonna try it out and then reply here........i have wracked my head over the past 2 days...........fingers crossed IT SHOULD WORK
Left by gajee on Jul 07, 2006 6:55 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
i'm interested this web actually they are solution as a quide in sql and dot.net problem... can someone give me idea to solve this problem..i already try part 1 but it still have a same problem...please help me...

email : kazwanm@modenas.com.my
Left by cayrol on Jul 12, 2006 2:28 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
in sql server 2005 epxress dosn't have security tab :(
please help me !!!!
Left by iliev on Jul 18, 2006 8:30 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Works for me; thank you!
Left by Mikey on Aug 15, 2006 6:44 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Thank you very much...

This article is very informative...and useful...
Left by Byock on Aug 23, 2006 10:04 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Incredibly helpful...

I have been experiencing the error message in the title infrequently but regularly in a web application I developed for a customer. I had no idea how to fix it, as the connection worked most of the time, and even when the user got the error, if they retried the action it would work.

The only other information I could find online other than using mixed authentication in SQL 2000 was using named pipes... my application is using a production database however, and I did not want to go jacking around with that, or have to reconfigure everyone's ODBC data connections!

I had pretty much given up on this problem that has been bothering the customer for weeks/months now...

The IIS_WPG group was the key! They are using windows 2003 server, which I have little experience with and could not test in the office before deployment.

Your detailed, step-by-step instructions saved my application :) It has been two days now without any "Login failed for user '(null)'" errors, whereas they were previously experiencing 2-3 per day.

Thank you so much for posting this walkthrough!!!
Left by Rebecca Weathersby on Sep 07, 2006 8:33 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
thank u
i am getting an error( keyword not supported provider)
Left by manimegalai.s on Sep 14, 2006 3:45 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
yes you should replace the name by the IP address but make sure when u go To IIS and create new web site to assign an IP Address
jawishm@yahoo.fr
Left by Mohammed Jawish on Oct 29, 2006 11:38 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Hi,
I am also getting the same error in MSDE.But using osql.exe I am able to login as 'sa' into the server.
Left by Alex Steward on Dec 06, 2006 9:09 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Thank you for your help!
Left by Hannele on Dec 07, 2006 2:32 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
could someone email me part 2 please? My email id: pnn07@hotmail.com

Thank you.
Left by ray on Dec 07, 2006 4:52 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
I am using SQL Server 2005 and I have done what was explained here but still I am getting this error. Can anyone help me. My email id is joshimahen@gmail.com.
Please send soultions in mail only
Left by Mahen on Dec 28, 2006 6:13 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Its all about database connection string. I mean, connection should not be windows autheticated type, rather it should be with user name and password. This property has to be set in IIS also.
Left by Kaviraju on Mar 14, 2007 1:56 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
UGH!! I wasted a whole day on this problem. The solution was quite simple once I realized that the problem was not with my sql server permissions, but with the local IIS server settings on my .Net development machine.

I had a suspicion that this might have to do with how IIS was attempting to connect to the sql server in question, but I didn’t have a clue as to where to go to change how it was submitting the login credentials.

The answer was in the IIS manager by selecting the properties of the website in question (I used the Default Web Site), then selecting the ASP.NET tab in the website properties dialog.

There are two buttons on the bottom of the tab screen one for global one for that particular web site node.

I chose global, because I don’t want to do this more than once. At this point another dialog will pop up (ASP.NET Configuration Settings) with 7 tabs.

Choose the “Application” tab. At the bottom of that tab screen there is a group called Identity settings. Make sure the Local impersonation checkbox is checked.

Don’t put anything in the User name and Password if you want it to use your PC login credentials.

Click OK all the way back and restart IIS. You should be golden.
Left by Todd Kandaris on Mar 14, 2007 11:50 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
Todd Kandaris - Your reply ROCKED! I've been banging my head with these user permissions for days!!! I have My DB and Website on seperate networked servers and this worked just like it is suppose to!! --Thanks!!!
Left by Roger Crockett on Mar 19, 2007 3:31 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Part I

Requesting Gravatar...
thank youuuu soooo muchhhh! i have this problem for a few days and i was confuse,
but only your blog help me.thankssssss.have a good time.
Left by MAS on Mar 23, 2007 10:37 PM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Pa

Requesting Gravatar...
-->IF YOU ARE USING WINDOWS 2003 SERVER!!!!!<--

You must enable DTC access. By default it is not installed on Windows 2003 Server (all flavors).

Step One: Steps to Enable Network DTC Access (Run on all machines requiring DTC access)
1. Click Start, point to Control Panel, and then click Add or Remove Programs.
2. Click Add/Remove Windows Components.
3. Select Application Server, and then click Details.
4. Select Enable network DTC access, and then click OK.
5. Click Next.
6. Click Finish.
7. Stop and then restart the Distributed Transaction Coordinator service.
8. Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).
Left by Art Z. on Jun 19, 2007 11:27 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Pa

Requesting Gravatar...
Hi,
i had the Windows XP Embedded without Service packs up and running and today I've updated my XP Embedded Environment first to SP1 and than to SP2. The sevice packs have also updated the database and the databade engine. XP Embedded installs a MS SQL Database (I assume it is the version 2000) but there is no Database Manager installed. First I found that no communication methods are enabled. I started the "SQL Server Utility" and activated the methods TCP/IP and "Named Pipes" after this change were my applications (Target Designer, Component Designer and Component Database Manager) able to start becouse they can connct to the database. But I had backed up my old database which includes a lot of self defined devices and now I want to restore my old database but I'am not able to connect to the database via a the database manager "DbaMGR2k" I tried as "sa" or "install" user to login an I get always the error message "Not associated with a trusted SQL Server". I also tried to login via "osql" with same result.
I inserted also in registry
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
a DWORD with value 0 according to hint on page "http://support.microsoft.com/default.aspx?scid=kb;en-us;285097"
But it does not solve my login problem.

Can some one help me?

Thanks in advance
Ivan
Left by Ivan Delerdivan on Sep 05, 2007 5:56 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Pa

Requesting Gravatar...
Thanks a lot!!! the article was really really helped me out. Great explanation
Left by Nil on Jan 19, 2008 1:06 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Pa

Requesting Gravatar...
That helped a lot , thanks

www.CodeExplore.com

Make money writing articles
Left by A.s on Apr 09, 2008 5:51 AM

# re: You may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" - Pa

Requesting Gravatar...
Thanks Dude u solved one of my big problems
Left by Deepesh on Apr 25, 2008 4:40 PM

Your comment:

 (will show your gravatar)