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

<UPDATE November 26, 2007>

I find that a lot of people found this article a little useful.  This article is in continuation to earlier one which deals with IIS and SQL Server on the same machine.  The link for the same is

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

</UPDATE>

This article is in continuation with Part I where I explained the scenarios where this error occurs when the Webserver and SQL Server are running on the same machine.

In case you havent read that, please
Read Part I of this article, before proceeding.

Let us examine the 2nd scenario as follows:-

2. IIS (Webserver) and SQL Server are on different machines.

Check the SQL Server Authentication Mode and set is to Mixed Mode Authentication, as explained in
Part I of this article.

Then comes the actual interesting part. Though you configure your SQL Server to Mixed Mode Authentication and add ASPNET or NETWORK SERVICE account to the Databases as explained in
Part I of this article, you will still get the error.

The reason is that, the local accounts/credentials (ASPNET in Windows 2000 Server and Windows XP Pro., Network Services in Windows Server 2003) under which ASP.NET worker process runs are local accounts (local to the web server). Therefore the database server on a separate machine will not be able to see/recognize these accounts. So if you try using the same steps mentioned above to configure a trusted connection between the web server and the SQL server, you will get the error still.

The resolution for this, is as follow:-

1. Create a Domain Account with priveleges similar to ASPNET or NETWORKSERVICE.

2. Grant this Domain Account (DomainName/UserName) access in the SQL Server Database for the necessary databases.

3. Use Impersonation in your web.config (setting identity impersonate="true") in the web.config of your application.

Now, while enabling impersonation, you can either set the username and password in the web.config itself as follows:-

<identity impersonate="true" userName="DomainName\UserName" password="password" />

However this defeats the process of security as you are again storing the password in the web.config file.

The second method is to simply set identity impersonate to true and assign the username and password in the IIS.

To do that, do the following steps

1. Type inetmgr from your command prompt and give enter.

2. This would open the IIS Control Panel.

3. Expand the appropriate nodes and select the Virtual Directory of your application.

4. Right Click and select Properties.

5. Switch to the Directory Security Tab.

6. Under Anonymous access and authentication control click Edit

7. Check the Enable Anonymous access in case you want people to access the application without logging in with Windows Logon Screen.

8. Uncheck the Allow IIS to control password and enter the DomainName/UserName and Password in the respective boxes. Usually IIS uses IUSER_MACHINENAME credentials for Anonymous access.

9. Uncheck if any other authentication mode is checked and then press Ok twice to exit.

Now the application should serve and the error "Login failed..." shouldnt appear.

Cheers and Happy Programming !!!


Note:
The steps and guidelines provided in this article have been explained in many other online resources, MSDN and KB Articles and this is just an initiative to further spread the awareness and provide help for people who get stumped by this error.

I have freely referred many resources and taken steps for writing this articles and am extending my thanks to the respective authors who have helped me in compiling this article.

Print | posted on Wednesday, May 25, 2005 10:01 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 II

Requesting Gravatar...
1. Create a Domain Account with priveleges similar to ASPNET or NETWORKSERVICE.

This statement is rather unclear. What are those privileges that we need to grant to this account? Could you please list down all. (Windows 2003). In this case should be IIS_WPG right? Where we can grant this permission? In Web Server? Database server? Your help is definitely appreciated.
Left by Joel on Jun 05, 2005 5:38 PM

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

Requesting Gravatar...
Do you have any solution if the two machines are not on a domain? "Mirrored" accounts (identical username/password on both machines) worked fine in IIS5, but in IIS6 it produces the "Login failed" error.
Left by Eric on Aug 09, 2005 1:04 PM

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

Requesting Gravatar...
Sounds to me like you again have one specific account being used to query. I have several apps that require the user credentials be passed all the way through to the sql server. How do we go about that? We had things working great untill we moved to a 2003 server. No we have many issues related to security we are trying figure out including this one.
Left by Eric on Aug 12, 2005 10:52 PM

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

Requesting Gravatar...
You may want to add a piece to this thread about having the latest service packs installed on SQL Server. If you install SQL Server 2000 on Windows Server 2003 and don't have at least SP3 this same error occurs (when running an ASP 3.0 app). You will also see an error if attempting to connect from asp.net. The current error number in that shows in .NET 2.0 is 40. I'm not sure if that will be the error we see when .NET 2.0 goes live.
Left by Devin on Sep 22, 2005 1:57 PM

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

Requesting Gravatar...
Hi i have a question here..

I have iis & sql on two different system in two different sub domains under one.

like sql at sqlserver.abc.sachin.com
and webserver at webserver.xyz.sachin.com

i have granted all domain users like (xyz\user) the required access to to databases and web server (via Query analyser)

How can i make user access the data from sql server via webforms on the web server?
I want all user to be authenticated by their own logins and do not was a common one for every body..
Please help....


Left by Sachin on Sep 23, 2005 4:16 PM

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

Requesting Gravatar...
You want to use Windows Authentication in the Security settings in your IIS. search msdn.microsoft.com for windows authentication and you should find many articles, examples and how-tos.
Left by Devin. on Sep 26, 2005 2:04 PM

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

Requesting Gravatar...
You guys have no fu**ing idea what you are talking about
Left by Mr Knowitall on Oct 28, 2005 1:26 AM

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

Requesting Gravatar...
I second that notion!
Left by Joe Bloggs on Oct 28, 2005 1:28 AM

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

Requesting Gravatar...
Boy..I have been suffering with this error for 2 days. I have tried all the solutions avaible in net. I have installed sql-server along with .net in windows 2000 and i have created a web application and trying to access the db from it.
Left by Ksheera Sagar on Jul 31, 2006 3:52 AM

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

Requesting Gravatar...
Where is the link for PartI?
Left by imperialx on Nov 23, 2007 4:31 PM

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

Requesting Gravatar...
You are all f**king cunts
Left by Antichrist on Dec 21, 2007 11:24 PM

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

Requesting Gravatar...
Wow, you have just solved a problem for me that I have been working on for the past 3 days! I had no idea that the Windows Identity was not being passed to the remote SQL server; knowing about the <identity> tag in web.config has fixed everything.
Left by Trey Tomes on Apr 09, 2009 8:43 PM

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

Requesting Gravatar...
I like your blog so much, Mark..
Left by Ravers on Nov 04, 2009 2:01 AM

# Help required for ASP Web Application

Requesting Gravatar...
Hi,

How to do the verification in IIS for a ASP application rather than a ASP.NET application?

Regards,
Prince
Left by Prince Richard on Jan 12, 2010 7:34 PM

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

Requesting Gravatar...
From my experience with this issue, the real solution is to ensure that the Windows account that the client is using to connect to the SQL server has been assigned "Access this computer from the network" rights in the Computer Config\Windows Settings\Security Settings\Local Policies\User Rights Assignment part of the group policy that applies to the computers in the domain where Windows authentication is being attempted.

Moving to Mixed Mode is a work around. As best as I can tell, when you go to mixed mode, the client uses the Network Service account to connect to the SQL sever, and passes through the SQL credentials. However when you use Windows authentication, it uses the context of the logged in user to make the initial contact with the SQL server (before attempting to log in to the SQL database itself) and if the Windows account does not have the right to even access the SQL server computer on the network, then the request is rejected. Hence the username (null) in the response.

Some of this is just guessing, but what I know for sure is that I was getting the (null) error using Windows only authentication, and setting up a GPO to allow the user account from the client to access the SQL sever computer from the network fixed it.
Left by Luke on Jun 09, 2010 12:38 AM

Your comment:

 (will show your gravatar)