How to: Access SQL Server Using a Mapped Windows Domain User

Article
12/04/2014

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.
Your application.

You must configure the Web.config file for ASP.NET to recognize the domain user account name.

Note

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.
SQL Server.

You must add the specified domain user account as a SQL Server login user.

Configuring a User Account on the Web Server

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.

Mapping to the Windows User Account and Enabling Impersonation

After establishing the correct user rights for the domain user account, configure the application identity impersonation.

Security Note
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.

Note

Elements in the Web.config file are case sensitive.

Using Windows Security in the Connection String

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.

Partager via