Error message when you create a trusted data connection from ASP.NET to SQL Server: "Login failed for user: 'AccountName'" (316989)

SYMPTOMS

When you create a trusted connection from Microsoft ASP.NET to Microsoft SQL Server, you may receive the following error message:

For computers that run Internet Information Services (IIS) 6.0, you may receive the following error message:

Note You receive either of these error messages specifically when you use integrated security (when you include the integrated security=sspi attribute in a connection string).

CAUSE

When you use ASP.NET, the default security context is the ASPNET account (or NetworkService account, for an application that runs on IIS 6.0) for both Aspnet_wp.exe (or W3wp.exe, for an application that runs on IIS 6.0) and the request to SQL Server. By default, the ASPNET account (or NetworkService account, for an application that runs on IIS 6.0) does not have any permissions in SQL Server, and therefore it cannot access the database.

RESOLUTION

To resolve this issue, use one of the following methods:

Method 1 Programmatically change the security context of the ASP.NET worker process to a user who has the correct SQL Server permissions.
Method 2 Change the default configuration of ASP.NET so that the ASP.NET worker process starts and runs under the context of a user who has the correct permissions in SQL Server.
Method 3 Grant the correct permissions in SQL Server so that the ASPNET account (or NetworkService account, for an application that runs on IIS 6.0) has the appropriate access to the required resources.

Note This method will make all the Web applications on the server have the corresponding right on the computer that is running SQL Server.

MORE INFORMATION

Steps to reproduce the issue

Create a new Microsoft Visual Basic .NET Web application on a computer that is running both ASP.NET and SQL Server.

Add the following code to the Page_Load event of the Webform1.aspx page that was created by using the project:

Dim con As New System.Data.SqlClient.SqlConnection("data source=localhost;integrated security=sspi;database=northwind")
 con.open()
 con.close()

Build the project, and then open the Webform1.aspx page in the browser.
You receive the error message that is mentioned in the "Symptoms" section.

REFERENCES

For more information about how to programmatically change the security context of the ASP.NET worker process, click the following article numbers to view the articles in the Microsoft Knowledge Base:

306158 How to implement impersonation in an ASP.NET application

307002 ASP/ODBC/SQL Server error 0x80040E4D "Login failed for user '(null)'"

253500 "Client unable to establish connection" error message when connecting from ASP to SQL Server

306586 Troubleshooting error 80004005 "Login failed" in ASP

247931 Authentication methods for connections to SQL Server in Active Server Pages

306518 Troubleshooting guide for 80004005 errors in Active Server Pages and Microsoft Data Access Components

315158 FIX: ASP.NET does not work with the default ASPNET account on a domain controller

824308 BUG: IWAM account is not granted the impersonate privilege for ASP.NET 1.1 on a Windows 2000 domain controller with SP4

For more information about how to change the default configuration of the ASP.NET security context, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/authaspdotnet.asp

For more information about how to add the ASPNET account to SQL Server, see the "Adding a Windows User or Group" topic in SQL Server Books Online.