ASP.NET, SQL Server, WPF, C#, VB.NET

Remember the old commercials where two guys would argue over whether a light beer was "less filling" or had "great taste"? As far as they were concerned, you had to have it one way or the other. But then a friend would point out that this wonderful beer, no doubt the very elixir of life, actually possessed both qualities. 

Architects of ASP.NET applications have been conducting a similar argument over strategies to authenticate to Microsoft SQL Server. Some have been arguing for convenience: just modify web.config to set the Windows account the ASP.NET worker process impersonates, then configure Windows Authentication mode in SQL Server to give that account the appropriate permissions. Clean and convenient. 

But then the crowd on the other side of the room starts shouting about security. The account's credentials are being stored as plain text in web.config; they cannot be stored in encrypted form. True, you can set NTFS file permissions to deny access to unauthorized users, and by default *.config files cannot be viewed via requests to IIS. But this is small comfort to the security-conscious crowd, whose mantra is "defense in depth"; they want to encrypt credentials.

This security-zealous group doesn't hard code a connection string, because it can be decompiled by any number of readily-available tools. Instead, they write a little app that encrypts a SQL connection string. They store the encrypted string in an arbitrary location in web.config. They write code in their ASP.NET app to decrypt the string and use it to instantiate a System.Data.SqlClient.SqlConnection object. Sounds like a lot, but they're just getting started. Unlike Windows credentials, SQL credentials are transmitted in plain-text form across the network. So the security-conscious have to configure IPSec between the web server and the SQL Server host in order to encrypt the connection. Only then is the task complete.

Though it is secure, this solution is obviously not simple. So ASP.NET application architects have faced the dilemma: choose simplicity, or choose defense in depth.

The next version of ASP.NET, to be released with the next version of VS.NET and Windows .NET Server 2003, has new capabilities that help resolve the dilemma. It includes a tool called aspnet_setreg, with which you may store an encrypted username and password in a secure area of the registry (i.e., it can only be accessed by an administrator, the system account, or creator-owner). The tool also provides the text that needs to be inserted in the web.config <identity> section; once you insert the text into web.config, IIS will know which identity to use for the ASP.NET worker process. You can then grant the account appropriate permissions on the database. Finally, configure your connection object to use a trusted connection; the ASP.NET worker process will use its Windows credentials to access the database.

You can see the advantages of this approach: you do not need to store a secret (encrypted or clear-text) in web.config in order to make an authenticated connection to a remote database server.  Of course, if a hacker gains access to administrator or system credentials, s/he may be able to compromise your database security by decrypting the registry entry.  On the other hand, if an intruder has access to admin or system credentials, the wolf is already in the henhouse.

Please note one caveat, however: you cannot install the string by exporting/importing the key via a *.reg file. The tool that encrypts the string, and the process that decrypts it, use a symmetric key based on a machine key. If you encrypt a username and password on ServerA using ServerA's machine key, you cannot export it to ServerB's registry; ServerB will attempt to use its own machine key to decrypt the username and password, and will of course fail. 

The Microsoft .NET Framework team recently backported this tool, along with the ASP.NET modifications necessary to use it, to the version 1.0 of the .NET Framework. To implement this strategy on the current version, follow these steps:

1. Contact Microsoft Product Support to obtain the update mentioned in Microsoft Knowledge Base article http://support.microsoft.com/default.aspx?scid=KB;en-us;329250. Install this update so that ASP.NET will know how to use the registry keys/values you create in subsequent steps. Note that it will not install if you have not previously installed .NET Framework SP2. As of this writing, Microsoft intends to include the update in .NET Framework SP3.
2. Download the aspnet_setreg.exe tool from Microsoft Knowledge Base article http://support.microsoft.com/default.aspx?scid=KB;en-us;329290.
3. Create a Windows account whose credentials ASP.NET will use to access your database. Grant the account appropriate permissions in your database.
4. Add a key/value pair to the <appSettings> section of your web.config to store a SQL connection string that uses Windows authentication mode. For example, 

<add key="ConnectionString" value="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=myDBServer;Connect Timeout=30 " />

You will access this key/value pair when you are creating a SQLConnection. Here's a snippet of C# sample code: 

String strConnection = ConfigurationSettings.AppSettings("ConnectionString");
Data.SqlClient.SqlConnection myConn = new   Data.SqlClient.SqlConnection(strConnection);

5. Encrypt the account's credentials and store them in an arbitrary registry key using the aspnet_setreg tool. This sample command-line stores encrypted credentials at HKLM\Software\MyASPApp\Identity:

aspnet_setreg -k :Software\MyASPApp\Identity -u:mydomain\user -p:password

Be sure to store the command's output into a text file. The relevant lines will be something like: 

userName="registry:HKLM\Software\MyASPApp\Identity\ASPNET_SETREG,userName" password="registry:HKLM\Software\MyASPApp\Identity\ASPNET_SETREG,password"

You now have a choice of two paths to complete the process. 

6. To change the default identity for all ASP.NET apps running on the server, change the identity under which the ASP.NET worker process runs. Modify the <processModel> section of machine.config to include the userName and password attributes that you stored in step 4. Make sure that any ASP.NET application that will use this identity sets <identity impersonate="false"> in its web.config file. As a result, the app will access resources such as SQL Server with the credentials under which aspnet_wp runs.
   
7. Grant the following NTFS permissions to your account: 

- OR -

6. Change the identity that your application will impersonate when it accesses SQL Server and other resources. Modify the <identity> section of machine.config to include the userName and password attributes that you stored in step 4. For example, 

<identity impersonate="true" userName="registry:HKLM\Software\MyASPApp\Identity\ASPNET_SETREG,userName" password="registry:HKLM\Software\MyASPApp\Identity\ASPNET_SETREG,password" />

7. Using regedt32, grant the local ASPNET account read permission on the HKLM\Software\MyASPApp\Identity\ASPNET_SETREG key. Revoke the permissions for System, Administrator, and Creator-Owner in order to tighten security as much as possible. (Of course, an administrator can always re-grant himself permissions - but why make life any easier for a hacker?) 

You're done. You didn't have to write any cryptographic code, and you didn't have to twiddle with the networking stack on your servers. Convenient. And you haven't exposed any passwords in plain-text, either in the file system or on the network. Secure. Now that you've resolved the dilemma between convenience and security, some of you will be tempted to retreat to your cubicle and celebrate with a great-tasting, less-filling beverage! 

N.B. the following URL has more information, as well as a link to download the ASPNET_SETREG.EXE, which was not provided by Microsoft in the original material: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q329290

Chris has spent over 8 years in software development, serving as a developer, as an Escalation Engineer at Microsoft, and most recently as an Application Development Consultant at Microsoft. A graduate of Princeton University's class of 1983, Chris has also taught high-school students, baked bagels, played jazz piano in restaurants, and lived among the poor in West Africa. Chris can be reached at chrisfalter@hotmail.com.