Enterprise Connection String Management in ASP.NET - Best Practice?

Enterprise Connection String Management in ASP.NET - Best Practice?

This is mainly a problem statement - it lists some solutions, but all have some pretty big downsides. I'd really like to find a good solution to this, so please comment if you have anything to add.

There are some significant problems with using keeping connection strings with SQL Server Authentication in ASP.NET web.config files. Here are a few:

1. Security - Both username, password are stored in plain text (associated with the server), so if the web.config file is compromised a hacker has the keys to the database. Config files are associated with the HttpForbiddenHandler, which mitigates the risk of hackers getting to web.config files, but only via HTTP.

2. Control - Keeping login information in web.config files makes it difficult to control developer access to production databases, since developers will likely view web.config files during production support activities or in source control.

3. Administration - Keeping login information in individual files on a per application / per webserver basis makes changing passwords (including regular password rotation) difficult.

Trusted Connections (using Windows Authentication rather than SQL Server Authentication) seem to offer a better solution - connection strings don't contain login information, and centralized account maintenance of Windows accounts is well defined and supported. Trusted connections don't send credentials over the network, so they're much more secure Several Microsoft security articles propose this approach:

http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch06.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh14.asp

However, this approach sounds like it works better in theory than in practice when it comes to ASP.NET applications. While the network communications are more secure, using Trusted Connections requires changes that make the ASP.NET application less secure.

There are two methods available for connection to a database server with Windows Authentication - Domain Accounts and Mirrored Accounts.

Trusted Connection cons (both domain account and mirrored account):

1. Impersonation still requires putting a password in a config file (machine.config and web.config) so we've got passwords in plain text again. It is possible to encrypt the impersonation identity and to store it in registry, but this complicates administration.[1]

2. Impersonation requires a little more setup on the webservers - the impersonated account needs write permissions on serveral folders (good info here). Also, upgrading .NET versions doesn't migrate machine.config information (why?) so there is additional work / risk when upgrading .NET versions.

3. Impersonation in ASP.NET complicates matters a bit. I've run into some unrelated issues with ASP.NET impersonation in the past that indicates it's a bit of a frontier.

Domain Account cons:

1. Security issues have been mentioned with running the IIS user as a user that is a member of a domain.

Mirrored Account cons:

1. Difficult to change passwords, need to edit web.config / machine.config on multiple webservers

Much more discussion here:

Alex Chang's blog: How to use Trusted Connection when SQL server and web Server are on two separate machines.
Barry Dorrans' blog: Using sql trusted Connections with asp.net

Christoph Schittko's blog: Comments on blog post

Storing Connection String in Registry cons:

1. Difficult to change passwords, need to edit registry on multiple webservers.[1 again]

Custom encryption of Connection Strings in web.config cons:

1. Makes management of multiple servers (password rotation, etc.) more difficult

2. Custom or homegrown solution - may not be supported, may not be secure, etc.

Centralized Connection String Management solution:

A central service provides applications their connection string (and possibly other configuration data) in an encrypted format. Applications would use a common component to access and decrypt the information.

Centralized Connection String Management cons:

1. "Crown Jewels" - if that system's compromised, all databases are exposed

2. Key management issues to do secure communications with central server

3. Single point of failure for all applications

4. Homegrown security solutions are likely to be insecure, and quickly turn into legacy systems that make upgrading difficult.

Domain Accounts with Trusted Connections seems the best approach (if the security issue is not such an issue), with the impersonation account stored in the registry. Microsoft documentation / recommendations kind of lay out the options but don't indicate a preferred method. It would be great to have some general guidance on how to manage database connections in an enterprise web environment.

It would also be great if impersonation were a bit more transparent - changing an encrypted registry setting is a bit complex. Changing the account an IIS Application runs under is as simple as logging into Windows; impersonation should work the same.

Visual Studio 2005 has a Connection String Manager in the IIS MMC that interfaces with web.config and supports encryption, but that's a ways off.

Comments? Guidance? What's worked for you? WWMD (What would Microsoft do)?