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.