|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
How to: Access SQL Server Using a Mapped Windows Domain User
By default, in Microsoft Windows 2000 and Microsoft Windows XP, ASP.NET applications run in the context of the local user account named ASPNET and in Windows Server 2003 in the context of the local user account named NETWORK SERVICE. These user accounts have limited access rights. However, the ASPNET account is local to the Web server. Because the ASPNET account is local to the Web server, it is not recognized as a user on remote computers. To work around this limitation, you can have your application run in the context of a Windows domain user who is recognized on both the Web server and the computer that is running Microsoft SQL Server.
Mapping your application process to a Windows domain user account requires that you configure the following:
The Web server.
You must make sure that the Windows domain user account that you specify has sufficient user rights (but no more) to run a Web application.
You must configure the Web.config file for ASP.NET to recognize the domain user account name.
For information about the Machine.config and Web.config files, see ASP.NET Configuration Overview.
A connection string.
When you create connection strings for connection objects in your application, you have to specify that the connection strings will use Windows integrated security.
You must add the specified domain user account as a SQL Server login user.
To set user rights for the Windows domain user account
On the Web server, use Windows administrative tools to make sure that the mapped Windows domain user account has the required user rights.
For detailed information, see ASP.NET Required Access Control Lists (ACLs).
Run aspnet_regiis.exe with the -ga switch to grant the common user rights that are required by the identity that you will use for application impersonation.
After establishing the correct user rights for the domain user account, configure the application identity impersonation.
When you place user credentials in your Web.config file, there are potential security threats. Users with access rights to the directory containing the Web.config file can read the file, and thus see the credentials. For details on how to protect against this threat, see Encrypting Configuration Information Using Protected Configuration.
To configure the Web application for impersonation
Open the Web.config file for your application, and then add the following identity impersonation code:
<identity impersonate="true" userName="domain\username" password="********"/>
Substitute the correct password for the value listed in the previous example.
Elements in the Web.config file are case sensitive.
Finally, when you create connection strings for database access, configure the connection strings to use Windows integrated security.
To use Windows integrated security in a connection string
When you create a connection string for your application, do not include a user name and password. Instead, for the connection string, set the Integrated Security attribute to SSPI.
The following example shows a connection string that includes the appropriate attributes:
data source=myserver;initial catalog=northwind;Integrated Security=SSPI
To configure SQL Server for integrated security
In Windows, click Start, point to Microsoft SQL Server, and then click Enterprise Manager.
Open the node for the server, and then expand the node for the database to which you want to grant user rights.
Right-click Users, and then click New Database User.
In the Database User Properties dialog box, in the Login name box, enter domain\username, and then click OK.