2

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?

Improve this question

5 Answers 5

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

7 Comments

By doing this, you've secured credentials for the production database, but now you need to secure credentials and communication channels used for access to the password retrieval web service. Which becomes an additional point of failure as well as a potentially high-value target.
Each password is encrypted with a different salt (one salt for each website/database). The web service returns the encrypted password which the website is responsible for decrypting, using the salt which, ideally, only it knows. Gaining access to the password database (or web service) would not be of use because the returned values would be gobbly-gook. Even if the password is decrypted, the salt would add further obfuscation. (Not that you wouldn't want to make the web service as difficult to reach as possible)
(Also, the password web service would be strictly internal-use only - no external attacker should have access to this database. Ultimately, a clever programmer could still uncover the password if he/she wanted to - but again, why are you hiring people you can't trust to build production websites for you?)
"no external attacker should have access to this database" - famous last words... When you say "... salt, which, ideally, only it knows", then this is yet another secret to manage.
Just an update to this old answer. In 4.5, .NET added SecureString as the type for the connection string property for SqlCredential class which can be used to build SqlConnection instances. However, I haven't found a way to get a secure string from a web.config file. Therefore, if storing the connection string in the web.config, it should still probably be encrypted. I really like the idea of having a remote web service own the connection strings though. This answer is still very relevant in my opinion in the battle to protect connection strings.
|
0

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

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

You're right that encryption and decryption is easy. The hard part is managing the cryptographic keys.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.