Suggested methods to secure connection strings with web.config transformations

Question

We have a situation where our internal security requires that our developers not have knowledge of the usernames and passwords for our production databases.

We are trying to move toward using web.config transformations to reduce the number of man-made errors that occur during deployment from manually editing the web.config files. Ideally, we would like the web.configs to be completely automated, with no human intervention needed.

It is my understanding that this would require the developers (who are the only folks in our organization who know how to create and modify the web.config transformations) to know the connection string information for the production environment.

Is there a way that we can keep the production connection strings unknown to the developers, and yet have them be solely responsible for maintaining the web.config transformations?

Answer 1

Here is the solution I came up with and I am proposing to my team.

Problem: We would like to implement the a new standard requiring the use of web.config transformations to eliminate the man-made configuration errors that occur during deployments between environments. This requires that the persons responsible for creating the web.config transformation files, know all the configuration values for each of the environments, including the usernames and passwords for connection strings to the other environments. The infrastructure team has a policy that no one other than they can know the usernames and passwords for database users with write access. Hence, we have a conflict.

Proposed Solution: Any configuration section within a web.config can be pointed to an external file containing the configuration for that section, instead of defining it explicitly in the web.config file itself.
For example, instead of defining the connection string section as:

<connectionStrings>
    <add name="conn_ExceptionManagement" connectionString="Data source=mydb;database=App_Error_Logging;uid=user;pwd=password" providerName="System.Data.SqlClient" />
  </connectionStrings>

We could define it like so:

<connectionStrings configSource="myApp.ConnectionStrings.config"/>

And then create a file called “myApp.ConnectionStrings.config” with the following content:

<?xml version="1.0"?>
<connectionStrings>
    <add name="conn_ExceptionManagement" connectionString="Data source=mydb;database=App_Error_Logging;uid=user;pwd=password" providerName="System.Data.SqlClient" />
  </connectionStrings>

So… we can create a folder in the root of the web server called “CONFIG” and place within it a file for each application with a name like “myAppName.ConnectionStrings.config”. In that file, we put all the connection strings we use for the application, in that environment. Then, in our web.config tranformations, instead of updating the connection strings explicitly, we instead change the connection string config section to point to that file on the web server. This way, the infrastructure team is the only one who ever actually sees the connection strings for those environments, and we eliminate the need for them to modify the connection strings within the web.config during deployment. In addition, they would rarely need to update the referenced file (only when changing passwords or adding/removing connection strings) so that also reduces the man-made configuration errors that occur during deployment.

Answer 2

Automating updates to web.config is definitely a good approach, but I don't really see much value in using web.config transformations for this purpose. There are XML APIs that are callable from scripting languages (e.g. VBScript, PowerShell) and this would be just as easy and more flexible.

If you're using database credentials in web.config, I would consider some or all of the following.

• Ideally use Windows authentication for the connection to the database, if your database supports it (e.g. SQL Server). This frees you from the task of managing database credentials.

• Write a small utility that can update a password in a connection string in web.config. This is easy to do with standard XML APIs. Admins can use this to update the password when deploying to the production server.

• Encrypt web.config on the production server using Protected Configuration. By doing the encryption directly on the server, you avoid the need for the cryptographic keys to be copied anywhere else. For example, use DPAPI if it's a single server; or RSA if you need the web.config to be used on more than one server (e.g. web farm).

  Your password update utility could do this automatically.

Answer 3

Consider this:

• A database with encrypted passwords (using SHA-2, for example) which include some "salt". The passwords are identified via a plaintext "key".

• A webservice which returns the encrypted password for a given key.

• A standard library which decrypts the password (given the salt) and returns a plaintext password.

Your web application could store the connection string in its entirety but use a placeholder for the password. When a connection is ready to be made, the application calls the webservice, gets the encrypted password, decrypts the password (EDIT: to decrypt the password, the salt, which is stored in the web application, must be used), amends the connection string, makes the connection, then forgets the password.

While not a perfect solution (if you can't trust your IT people, then why are the working for you?), but it does shield the programmers from knowing the passwords. As an added benefit, you can manage the passwords separately (for example, making them expire) without needing to update the connection string in the web config.

You may also be interested in SecureString if you really want to go "all-out".
(Here's a good article discussing the use of SecureStrings, although given that you have to convert the SecureString to plaintext in order to build the connection string, it's really kind of pointless: http://blogs.msdn.com/b/fpintos/archive/2009/06/12/how-to-properly-convert-securestring-to-string.aspx)

EDIT
Ultimately, the most secure option would be to wrap the database connection in a web service, then use your own custom credentialling system (or something like Active Directory) to access the web service. The web service would be solely responsible for querying the database and could be centrally maintained. The websites which use it (and the programmers for those websites) would have no knowledge of the database details.

There are many, many benefits to this approach, including the ability to completely re-architect your database (table/stored-procedure changes would only concern the web service), switching between servers (go from SQL Server to MySQL without a hiccup from the consuming website), load balancing, an extra layer of security, etc.

Answer 4

Use a class that get encrypt and decrypt the webconfig connection strings. Everytime a get is performed on the connection string you decrypt and use... the encrypt would more just be a tools method you would use to initially encrypt the connection string to put it into the web.config file

There are a number of two way cryptographic providers out there you could use to do this.

Answer 5

Yes, take a look at encrypting connection strings: http://msdn.microsoft.com/en-us/library/ms254494(v=vs.100).aspx