<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.
posted @ Wednesday, May 25, 2005 8:12 AM