How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0

Article
02/15/2011

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

patterns & practices Developer Center

J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Andy Wigley, Kishore Gopalan

Microsoft Corporation

July 2005

Applies To

ASP.NET version 2.0
Microsoft® SQL™ Server 2000
Microsoft Windows Server™ 2003 operating system

Summary

This How To shows you how to connect to SQL Server 2000 using a Windows service account from an ASP.NET version 2.0 application. You should use Windows authentication instead of SQL authentication whenever possible because you avoid storing credentials in connection strings and you avoid passing passwords over the network to your database server. You should consider encrypting your connection string to protect server connection details, such as the database server and name. By default, ASP.NET does not impersonate the caller to the database. On Windows Server 2003, you can use the network service account, which has network credentials (machine$), or you can configure an application pool identity. If you configure impersonation to use the original caller, you need to evaluate the scalability of per-user connection pools.

Objectives
Overview
Impersonation/Delegation and Trusted Subsystem Model
Summary of Steps
Step 1. Configure a Connection String
Step 2. Encrypt the Connection String
Step 3. Configure SQL Server Security
Step 4. Test Security Access
Deployment Considerations
Web Farm Considerations
Mirrored Accounts
Impersonation / Delegation vs. Trusted Subsystem
Additional Resources

Objectives

Choose between trusted subsystem and impersonation/delegation models.
Connect to SQL Server using Windows authentication.
Authorize your Web application identity in SQL Server.
Protect your database connection string.
Protect your database connection string in a Web farm.
Learn how to use mirrored accounts when required.

Overview

When you use Windows authentication to connect to SQL Server, you use your application's process identity or thread identity (if your application is using impersonation) to connect. You should use Windows authentication to connect to SQL Server when possible for the following reasons:

No credentials are passed over the network during authentication and you do not need to embed user names and passwords in the database connection string. This means that malicious users or attackers cannot obtain the credentials either by monitoring the network or by viewing connection strings inside your configuration files.
You benefit from centralized account management. User accounts are subject to normal account management security policies such as password expiration periods, minimum lengths, and account lockout after multiple invalid logon requests.

When you use Windows authentication to connect to SQL Server, you use either Kerberos or NTLM authentication depending on the configuration of your servers and domain. You might not be able to use Kerberos authentication if:

Your database client and database server are separated by a firewall which prevents Kerberos authentication.
Your application server and database server are in separate domains with no trust.

In these situations, you can use either mirrored local accounts or SQL authentication. With mirrored local accounts, you configure two accounts on each server with identical user names and passwords. You must ensure that the passwords remain the same.

If you use SQL Authentication you must:

Manage the credentials yourself.
Protect the credentials in the connection string.
(Potentially) protect the credentials passed over the network from the Web server to the database.

For more information see How To: Connect to SQL Server Using SQL Authentication in ASP.NET 2.0

Impersonation/Delegation and Trusted Subsystem Model

ASP.NET applications do not impersonate by default. As a result, when they use Windows authentication to connect to SQL Server, they use the Web application's process identity. With this approach, your front-end Web application authenticates and authorizes its users and then uses a trusted identity to access the database. The database trusts the application's identity and trusts the application to properly authenticate and authorize callers. This approach is referred to as the trusted subsystem model.

The alternative model—referred to as the impersonation/delegation model—uses the original caller's Windows identity to access the database. This approach requires that your ASP.NET application is configured to use impersonation. See the section "Impersonation / Delegation vs. Trusted Subsystem" in this document.

Summary of Steps

To connect to SQL Server using Windows authentication, perform the following steps:

Step 1. Configure a connection string.
Step 2. Encrypt the connection string.
Step 3. Configure SQL Server security.
Step 4. Test security access.

Step 1. Configure a Connection String

For ASP.NET 2.0 applications, you should store connection strings in the <connectionStrings> section of your application's Web.config file. The connection string used with Windows authentication must include either the Trusted_Connection=Yes attribute, or the equivalent attribute Integrated Security=SSPI, as shown here.

<connectionStrings>
  <add name="MyDbConn1" 
       connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
  <add name="MyDbConn2" 
      connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
</connectionStrings>

The above two strings are equivalent and both result in Windows authentication to the database.

If you are using data binding expressions in your Web pages, use the ConnectionStrings: qualifier in an ASP.NET expression to use a connection string stored in your application's Web.config file as shown here.

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
  ConnectionString="<%$ ConnectionStrings:MyDbConn1%>">
  ...
</asp:SqlDataSource>

To access a connection string in code, use ConfigurationManager.ConnectionStrings as shown here.

string connStr = ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString();
SqlConnection conn = new SqlConnection(connStr);

Step 2. Encrypt the Connection String

Even though database connection strings for Windows authentication do not contain usernames and passwords, you should still consider encrypting them in Web.config to reduce the possibility of disclosing server names and database names. To do so, you use the Aspnet**_**regiis utility with either the Windows Data Protection API (DPAPI) or RSA protected configuration providers. It is important to be aware that encrypting database connection strings involves performance and deployment tradeoffs, so these implications should be kept in mind when making the decision.

The DataProtectionConfigurationProvider uses DPAPI and the RSAProtectedConfigurationProvider uses RSA public-key encryption. Use the RSAProtectedConfigurationProvider if your application is deployed in a Web farm due to the ease with which RSA keys can be exported.

The following command shows how to encrypt the <connectionStrings> section of Web.config by using the DataProtectionConfigurationProvider:

aspnet_regiis -pe "connectionStrings" -app "/MyWebsite" -prov "DataProtectionConfigurationProvider"

Where /MyWebsite is the virtual path to your ASP.NET application.

To use the RSAProtectedConfigurationProvider, change the -prov switch as follows:

aspnet_regiis -pe "connectionStrings" -app "/MyWebsite" -prov "RSAProtectedConfigurationProvider"

The RSA protected configuration provider needs a key container to be created first to work. The NetFrameWorkConfigurationKey is the default key container used by the provider. If you wish to generate a RSA key container, then you can use the aspnet_regiis.exe tool with the -pc switch. Add the -exp option to it, if you want your RSA key container to be exported.

aspnet_regiis -pc "NetFrameworkConfigurationKey" -exp

To use the encrypted connection string in your application, simply access the string value at run time, as shown here.

string connStr = ConfigurationManager.ConnectionString["MyDbConn1"].ToString();

ASP.NET automatically decrypts encrypted sections at run time.

Note You can programmatically encrypt and decrypt connection strings and other sections of your configuration file by using the System.Configuration.SectionInformation class and the methods ProtectSection and UnProtectSection.

For more information about using the protected configuration providers to encrypt configuration file sections, see:

Step 3. Configure SQL Server Security

You need to create a SQL Server login for your application's service account, and grant restricted permissions to access your database. You should restrict access to specific database objects, such as stored procedures. Use the following pattern to grant database access and limit permissions in the database:

Create a SQL Server login for your application's account.
Map the login to a database user in the required database.
Place the database user in a database role.
Grant the database role limited permissions to only those stored procedures or tables that your application needs to access.

Ideally, you should not provide direct table access, and you should limit access to selected stored procedures only. If you must grant table access, grant the minimum access that the application requires. For example, do not grant update access if read access is sufficient.

You application's service account is usually either the Network Service account, which is the default account used to run ASP.NET application pools on Windows Server 2003 and IIS 6.0 or a custom service account.

Using the Network Service Account

To grant database access to the Network Service account

Create a SQL Server login for the Network Service account. If your database is on a separate server, create the login for the domainName\WebServerMachineName$ identity. You can use Enterprise Manager or run the following SQL statement in the osql command line tool to create the SQL login.

exec sp_grantlogin 'domainName\WebServerMachineName$'
Create a database user in the required database, and map the login to the database user. Alternatively, you can run the following SQL statements in osql:

use targetDatabase

go

exec sp_grantdbaccess 'domainName\WebServerMachineName$'

go
Place the database user in a database role.
Grant permissions to the role. Ideally, you should grant execute permissions to selected stored procedures and provide no direct table access.

Using a Custom Service Account

In situations where you run multiple Web applications on the same Web server, you can provide additional application isolation by running each application in its own application pool using a separate custom service account. By avoiding the use of a shared identity for multiple applications, you can restrict database access to your application's account only and ensure that other applications cannot access your database. This also means that any accidental change to the way that the Network Service account is authorized in the database, or to the privileges granted to the account, does not affect all Web applications.

To create a custom service account

Create a Windows domain account.
Run the following Aspnet_regiis.exe command to assign the relevant ASP.NET permissions to the account:

aspnet_regiis.exe -ga machineName\userName

On Windows 2003, running the Aspnet_regiis.exe -ga command adds the account to the IIS_WPG group. Membership in the IIS_WPG group grants the account the Log on as a batch job permission and ensures that the necessary file system and IIS metabase permissions are granted.
Use the Local Security Policy tool to grant the Windows account the Deny logon locally user right.
Use IIS Manager to create an application pool running under the new account's identity and assign the ASP.NET application to the pool.

For more information see How To: Create a Service Account for an ASP.NET 2.0 Application.

To grant database access to the custom service account

Follow the steps outlined previously in the section, "Using the Network Service Account," but create a login and grant access to the custom service account, rather than to the Network Service account.

Step 4. Test Security Access

To test database access, create a test ASP.NET Web application and add the following .aspx page.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script >
  protected void Page_Load(object sender, EventArgs e)
  {
      using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString()))
      {
          SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM authors", cn);
          cn.Open();
          SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
          rdr.Read();
          Response.Write(rdr[0].ToString()); //read a value
      }
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head >
    <title>SQL Authentication</title>
</head>
<body/>
</html>

Add a Web.config file and add a connection string entry as described in Step 1. Build and run the application. If you have not specifically permitted SELECT access to the authors table, you will see an error message similar to the following:

SELECT permission denied on object 'authors', database 'pubs', owner 'dbo'.

Deployment Considerations

When using Windows authentication to connect to SQL Server in production environments, consider the following:

Use a custom service account.
Create a SQL Server login for a Windows group.
Assign database permissions to a database role.

Use a Custom Service Account

By default ASP.NET applications run in application pools that have the Network Service identity. In production applications, it is recommended that you use a custom service account and a dedicated application pool. This offers a number of benefits:

Your application is isolated from other applications on the server, and access control lists (ACLs) in databases and on other Windows resources can be configured for your specific application.
Other applications running with the Network Service identity are not able to access your application's databases and other Windows resources.
Windows auditing can be used to track individual application activity.
Your application is shielded from permissions or privilege changes applied by an administrator to the Network Service account either accidentally or deliberately for another application.

Rather than create a SQL Server login for the network service account or your custom service account directly, add this account to a Windows group on the database server and then create a SQL Server login for the Windows group. This is better from an administration perspective and shields you from potential changes to the application's account identity.

Assign Database Permissions to a Database Role

During development, it is quick and easy to grant permissions directly to a database user based on your application account's SQL Server login. In production environments, you should add the database user to a database role and then assign permissions to the role. This protects your configuration from changes to the database user name.

Web Farm Considerations

Use the RSA Provider to protect your connection string information when your application is deployed in a Web farm. You can use RSA encryption in Web farms, because you can export RSA keys. You need to do this if you encrypt data in a Web.config file prior to deploying it to other servers in a Web farm. In this case, the private key required to decrypt the data must be exported and deployed to the other servers.

For more information, see the section "Using the RSA Provider to Encrypt a Connection String in Web.config in a Web Farm" in How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA.

Mirrored Accounts

Mirrored accounts are not recommended, but are a workaround if you need to use Windows authentication and your Web server and database server are not in the same or trusted domain or if Windows authentication is blocked by a firewall. To use mirrored accounts, create a local account with the same user name and password on each server and then create a SQL Server login for the local account on the database server.

To create a local account

Create a local Windows account.
Run the following Aspnet_regiis.exe command to assign the relevant ASP.NET permissions to the account:

aspnet_regiis.exe -ga machineName\userName

On Windows 2003, running the Aspnet_regiis.exe -ga command adds the account to the IIS_WPG group. The IIS_WPG group provides the Log on as a batch job user right and ensures that the necessary file system permissions are granted.
Use the Local Security Policy tool to grant the Windows account the Deny logon locally user right.
Use IIS Manager to create an application pool running under the new account's identity and assign the ASP.NET application to the pool.

For more information see How To: Create a Service Account for an ASP.NET 2.0 Application.

To create a mirrored account on the database server

Create a local account on the database server using the same username and password as the account you created on the Web server.

To grant database access to the mirrored account

Follow the steps outlined above in the section "Using the Network Service account," but create a login and grant access to the local account rather than to the Network Service account.

Impersonation / Delegation vs. Trusted Subsystem

Trusted Subsystem

A trusted subsystem model is where the database server trusts the Web application identity. The Web application identity is trusted to make calls on behalf of the original caller. (See Figure 1.)

Ff647396.f01paght00000801(en-us,PandP.10).gif

Figure 1. Trusted subsystem model

Trusted Subsystem Pros and Cons

The advantages of the trusted subsystem model include:

Scalability. The trusted subsystem model supports efficient connection pooling. Connection pooling allows multiple clients to reuse available pooled connections. It works with this model because all back-end resources accessed use the security context of the application's service account, regardless of the caller's identity.
Minimizes back-end ACL management. Only the service account accesses back-end resources (for example, databases). ACLs are configured for this single identity.
Users cannot access data directly. In the trusted subsystem model, only the service account is granted access to the back-end resources. As a result, users cannot directly access back-end data without going through the application (and being subjected to application authorization).

The trusted subsystem model suffers from these disadvantages:

Auditing. To perform auditing at the back end, you can explicitly pass (at the application level) the identity of the original caller to the back end, and have the auditing performed there. You have to trust the middle tier and you do have a potential repudiation risk. Alternatively, you can generate an audit trail in the middle tier and then correlate it with back-end audit trails (for this, you must ensure that the server clocks are synchronized).
Increased risk from server compromise. In the trusted subsystem model, the middle-tier service is granted broad access to back-end resources. As a result, a compromised middle-tier service could potentially make it easier for an attacker to gain broad access to back-end resources.

Impersonation/Delegation

To impersonate the original caller in ASP.NET, the simplest approach is to use Windows authentication to authenticate callers and for you to set impersonate="true" on the <identity> element in Web.config as shown here.

<configuration>
    <system.web>
        <authentication mode="Windows"/>
        <identity impersonate="true"/>
    ...
    </system.web>
</configuration>

If your database is on a remote server, you must also configure the relevant accounts in Active Directory for delegation. For example, you must ensure that your application's service account is marked as Trusted for delegation and that end user accounts are not marked as sensitive, which means that they cannot be delegated.

For more information, see How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0.

Impersonation/Delegation Pros and Cons

The advantages of the impersonation/delegation model include:

You benefit from operating system auditing. This allows administrators to track which users have attempted to access specific resources.
Auditing across tiers. The user's security context is maintained across the physical tiers of your application, which allows administrators to audit across tiers. Generally, auditing is considered most authoritative if the audits are generated at the precise time of resource access and by the same routines that access the resource.
Granular access controls can be configured in the database. Individual user accounts can be restricted independently of one another in the database.

The disadvantages of the impersonation/delegation model include:

Scalability. The impersonation/delegation model does not allow you to use database connection pooling because database access is performed by using connections that are tied to the individual security contexts of the original callers. This significantly limits the application's ability to scale to large numbers of users.
Increased administration effort. ACLs on back-end resources need to be maintained in such a way that each user is granted the appropriate level of access. When the number of back-end resources increases (and the number of users increases), a significant administration effort is required to manage ACLs.

Additional Resources

Feedback

Provide feedback by using either a Wiki or e-mail:

Wiki. Use the Security Guidance Feedback Wiki page at
https://channel9.msdn.com/wiki/securityguidancefeedback/
E-mail. Send e-mail to secguide@microsoft.com.

We are particularly interested in feedback regarding the following:

Technical issues specific to our recommendations
Usefulness and usability issues

Technical Support

Technical support for the Microsoft products and technologies referenced in this guidance is provided by Microsoft Support Services. For support information, please visit the Microsoft Support Web site at https://support.microsoft.com.

Community and Newsgroups

Community support is provided in the forums and newsgroups:

MSDN Newsgroups: https://msdn.microsoft.com/newsgroups/default.asp
ASP.NET Forums: http://forums.asp.net

To get the most benefit, find the newsgroup that corresponds to your technology or problem. For example, if you have a problem with ASP.NET security features, you should use the ASP.NET Security forum.

Contributors and Reviewers

External Contributors and Reviewers: Jason Taylor, Security Innovation; Rudolph Araujo, Foundstone Professional Services
Microsoft Services and PSS Contributors and Reviewers: Adam Semel, Nobuyuki Akama, Prash Shirolkar, Tom Christian, Wade Mascia
Microsoft Product Group Contributors and Reviewers: Mike Volodarsky
Test team: Larry Brader, Microsoft Corporation; Nadupalli Venkata Surya Sateesh, Sivanthapatham Shanmugasundaram, Infosys Technologies Ltd.
Edit team: Nelly Delgado, Microsoft Corporation; Tina Burden McGrayne, Linda Werner & Associates, Inc.
Release Management: Sanjeev Garg, Microsoft Corporation

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Share via

How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0

Applies To

Summary

Contents

Objectives

Overview

Impersonation/Delegation and Trusted Subsystem Model

Summary of Steps

Step 1. Configure a Connection String

Step 2. Encrypt the Connection String

Step 3. Configure SQL Server Security

Using the Network Service Account

Using a Custom Service Account

Step 4. Test Security Access

Deployment Considerations

Use a Custom Service Account

Assign Database Permissions to a Database Role

Web Farm Considerations

Mirrored Accounts

Impersonation / Delegation vs. Trusted Subsystem

Trusted Subsystem

Trusted Subsystem Pros and Cons

Impersonation/Delegation

Impersonation/Delegation Pros and Cons

Additional Resources

Feedback

Technical Support

Community and Newsgroups

Contributors and Reviewers

Additional resources

Share via

How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0

Applies To

Summary

Contents

Objectives

Overview

Impersonation/Delegation and Trusted Subsystem Model

Summary of Steps

Step 1. Configure a Connection String

Step 2. Encrypt the Connection String

Step 3. Configure SQL Server Security

Using the Network Service Account

Using a Custom Service Account

Step 4. Test Security Access

Deployment Considerations

Use a Custom Service Account

Create a SQL Server Login for a Windows Group

Assign Database Permissions to a Database Role

Web Farm Considerations

Mirrored Accounts

Impersonation / Delegation vs. Trusted Subsystem

Trusted Subsystem

Trusted Subsystem Pros and Cons

Impersonation/Delegation

Impersonation/Delegation Pros and Cons

Additional Resources

Feedback

Technical Support

Community and Newsgroups

Contributors and Reviewers

Additional resources