encrypt sql connection string

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




patterns & practices Developer Center
J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Andy Wigley, Kishore Gopalan

Microsoft Corporation

August 2005

Applies To
ASP.NET version 2.0
Microsoft® SQL Server™ 2000
Summary
This How To shows you how to connect an ASP.NET application securely to Microsoft® SQL Server™ when database access authentication uses native SQL authentication. Windows authentication is the recommended way to connect to SQL Server because you avoid storing credentials in connection strings and you avoid passing passwords over the network to your database server. You can also use Microsoft Active Directory® directory service for unified account management and to enforce strong password policies. If you do need to use SQL authentication, then you need to protect stored credentials in your connection strings by encrypting the connection strings. In addition, you might need to secure the credentials as they are passed over the network from your application to the database server. Also, you should not use the sa account. Instead, use a least privileged account with a strong password.

Contents
Objectives
Overview
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
Step 5. Secure the Communications Channel to the Database
Additional Resources

Objectives
Connect to SQL Server using SQL authentication.
Restrict your account in the database.
Protect database credentials.
Protect your database connection string in a Web farm.
Overview
You should only use SQL authentication to connect to SQL Server if you cannot use Windows authentication. Windows authentication is recommended for the following reasons:

You avoid storing user names and passwords in database connection strings.
Credentials are not passed over the network to the database server.
You can enforce Active Directory password policies.
Active Directory provides a single identity store with a single set of management tools. You do not have to maintain a separate set of SQL accounts.
You might not be able to use Windows authentication if a firewall between your application server and database server prevents Windows authentication or if your application server and database server are in separate domains without trust. In some of these situations, you might be able to use mirrored local accounts, where you connect using local accounts with the same user name and password on both computers. However, if you must use SQL authentication, then you need to:

Protect database connection strings in storage.
Optionally, provide channel security to protect user names sent over the network to the database server.
Avoid using the sa account, and instead use a least privileged SQL account with a strong password.
For more information about how to use Windows authentication, see How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0.

Summary of Steps
Follow these steps to create a test ASP.NET application using secured SQL Server access, encrypted connection information, and secure communications:

Step 1. Configure a connection string.
Step 2. Encrypt the connection string.
Step 3. Configure SQL Server security.
Step 4. Test security access.
Step 5. Secure the communications channel to the database.
Step 1. Configure a Connection String
For ASP.NET 2.0 applications, you should store connection strings in the <connectionStrings> section of the application's Web.config file. The connection string used with SQL authentication must include the user name and password of the database user, as shown here.

Copy Code
<configuration>
<connectionStrings>

<add name="MyDbConn"
connectionString="Server=MyServer; Database=pubs; User Id=MyUser; password= P@ssw0rd"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>Step 2. Encrypt the Connection String
To help make sure that the SQL account credentials remain confidential, you should encrypt the connection string in the Web.config file. To do so, you use the Aspnet_regiis utility with either the Windows Data Protection API (DPAPI) or RSA protected configuration providers.

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

The following command shows how to encrypt the <connectionStrings> section of the Web.config file 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 encrypted connection string in your application, simply access the string value at run time as shown in the following example.

Copy Code
string dbConn = ConfigurationManager.ConnectionString["MyDbConn"].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:

How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA
Step 3. Configure SQL Server Security
To access a SQL Server database using SQL Server authentication, SQL Server must be configured for SQL authentication and you must create a login account.

To verify the SQL Server authentication mode

Start SQL Server Enterprise Manager.
Locate the SQL Server instance within the SQL Server group.
Right-click your SQL Server and click Properties.
Click the Security tab and make sure that authentication is set to SQL Server and Windows.
To create a new SQL Server login

Expand the Security folder in the SQL Server Enterprise Manager, right-click Logins, and click New Login.
Select the SQL Server Authentication option for Authentication.
Enter a login name and strong password. You should use a combination of letters, numbers, and non-alphanumeric characters for the password and use at least 8 characters total.
SQL Server can now authenticate the new login, but the login has no permission to access any of the user-defined databases.

To grant database access

Locate the Users folder for the database you want to grant access to, right-click, and click New Database User.
Select the Login name from the drop-down list and click OK. The database user name defaults to the same name.
Select Database Roles, right-click and then click New Database Role.
Avoid granting permissions to individual database users because the database user name may change and/or additional users may require the same permissions. Instead, grant permissions to database roles.

Enter a role name and select the Database Role type as Standard Role. Click the Add button, add the new user you created, and click OK.
Select the new role, right-click, and then click Properties.
Click Permissions. Locate the relevant stored procedures, and grant the EXEC (execute) permission to these objects.
By default, a new database role has no permission to read the base tables or views in the database and cannot execute stored procedures. It is best practice to grant permissions to stored procedures and views because then you can control what users can read and update.

As an alternative to using Enterprise Manager, you can run the following database script to perform the steps outlined above.

Copy Code
-- Create a new SQL Server Login
exec sp_addlogin @loginame = 'MyUser', @passwd = 'P@ssw0rd'
-- Grant the SQL login access to your database.
-- Create a database user called WebAppUser to which the login is associated
Use YourDatabase
GO
exec sp_grantdbaccess 'MyUser'
-- Create a user-defined database role.
exec sp_addrole 'WebAppUserRole'
-- Add the database user to the new database role.
exec sp_addrolemember 'WebAppUserRole', 'MyUser'
-- Grant the role execute permissions on the stored procedure called sprocname
grant execute on sprocname to WebAppUserRole
Step 4. Test Security Access
To test database access, create a test ASP.NET Web application and add the following .aspx page.

Copy Code
<%@ 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 runat="server">
protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].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 >
<head runat="server">
<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 such as the following:

Copy Code
SELECT permission denied on object 'authors', database 'pubs', owner 'dbo'.
Step 5. Secure the Communications Channel to the Database
You might consider it necessary to encrypt the user name and data transmission, particularly if it contains sensitive application data between SQL Server and your ASP.NET application server. If your application is in a physically secured Internet data center, then this might not be necessary. In other situations, you might want to secure the communication channel to mitigate the risk posed by network eavesdropping.

There are two options for securing the data transmission:

Internet Protocol Security (IPSec)
Secure Sockets Layer (SSL) using a server certificate on SQL Server
IPSec
IPSec is a transport-level service that provides port filtering, authentication, encryption, and integrity for all traffic between two computers. IPSec is transparent to applications; therefore, it can be used to secure applications without requiring their modification. However, IPSec is not intended as a replacement for application-level security. Consider using IPSec to provide additional infrastructure security to secure the communication channel between your Web application and database server and to restrict the computers that can communicate with your database server. For example, you can help secure a database server by establishing a policy that permits requests only from a trusted client computer, such as an application or Web server. You can also restrict communication to specific IP protocols and TCP/UDP ports.

For detailed information about using IPSec, see:

"How To: Use IPSec to Provide Secure Communications Between Two Servers," at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod32.asp
"How To: Use IPSec for Filtering Ports and Authentication," at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/HTUseIPSec.asp
SSL
SSL uses cryptographic functions to provide an encrypted channel between client and server applications, and can be used to secure data transmission between a SQL Server and an ASP.NET application server. With SSL, each application can choose whether or not to create an encrypted communications channel; therefore, it offers more granularity than IPSec.

To configure SQL Server to use SSL, you must install a server certificate on the server, and the clients must have a root certificate authority that matches the server certificate.

For detailed information about how to install SSL, see:

"How To: Use SSL to Secure Communication with SQL Server 2000," at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod33.asp
"How To: Enable SSL Encryption for SQL Server 2000 with Microsoft Management Console," at http://support.microsoft.com/default.aspx?scid=kb;en-us;316898
"How to Enable SSL Encryption for SQL Server 2000 with Certificate Server," at http://support.microsoft.com/default.aspx?scid=276553
You can choose to configure SQL Server to force all clients to use SSL by running the SQL Server Network Utility (Svrnetcn.exe) tool and checking the Force protocol encryption option for TCP/IP. However, you may prefer to choose when to use SSL at an application level on a per-connection basis. For example, your application can use an unencrypted connection when it is not handling sensitive data, and only use an encrypted connection when necessary.

If you are using the ADO.NET SQL Server managed data provider and a SqlConnection object to connect to SQL Server, then you can enable encryption by adding the encrypt=true parameter to the connection string as shown here.

Copy Code
<configuration>
<connectionStrings>
<add name="MyDbConn"
connectionString="Server=MyServer;Database=pubs;User Id=MyUser; password=P@ssw0rd;encrypt=true"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Note that encryption is off by default.

Additional Resources
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA
How To: Protect From SQL Injection in ASP.NET
How To: Use IPSec to Provide Secure Communications Between Two Servers
How To: Use SSL to Secure Communication with SQL Server 2000
Feedback
Provide feedback by using either a Wiki or e-mail:

Wiki. Security Guidance Feedback page: http://channel9.msdn.com/wiki/default.aspx/Channel9.SecurityGuidanceFeedback
E-mail. Send e-mail to secguide@microsoft.com.
We are particularly interested in feedback regarding the following:

Technical issues specific to 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 product support information, please visit the Microsoft Support Web site at http://support.microsoft.com.

Community and Newsgroups
Community support is provided in the forums and newsgroups:

MSDN Newsgroups: http://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 would 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, TinaTech Inc.
Release Management: Sanjeev Garg, Microsoft Corporation