How can I safely store and access connection string details?

Question

I'm currently working on a ASP.NET MVC web site, and I've come up to a point where I need to integrate a database into the website.

Normally I would simply add the appropriate connection string to the Web.config file:

<add name="MainDB" 
    connectionString="Server=localhost; Database=TopSecretData; User Id=Joe;
    password=password" providerName="System.Data.SqlClient" />

But there's obviously a glaring security flaw if I leave my User Id and password right in the Web.config, especially when it's under source control.

In short: How can I store my connection string details without having it publicly visible?

Answer 1

Best practice is to encrypt your connection strings section. Use aspnet_regiis.exe, which can be found in various places:

• Start – Visual Studio – Visual Studio Tools – Visual Studio Command Prompt
• C:\Windows\Microsoft.NET\Framework\v4.0.30319 (making sure you run as an administrator)

Before:

<configuration>
<connectionStrings>
<add name="MainConnectionString" 
 connectionString="data source=Ratbert;database=Sales;username=ASPNET;password=$Double_Rainbow2011" 
 providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>

Run this command:

aspnet_regiis –pef connectionStrings c:\PathToWebSite

Or, if the above command doesn't work (and you get the aspnet_regiis help text), try

aspnet_regiis -pe connectionStrings -app "/" -site 6

where the "6" is the ID of the site as reported in IIS.

After:

<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
  <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
   xmlns="http://www.w3.org/2001/04/xmlenc#">
   <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
   <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
    <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
     <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
     <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
      <KeyName>Rsa Key</KeyName>
     </KeyInfo>
     <CipherData>
      <CipherValue>Bf677iFrUFW ... +4n4ZZKXCTUAu2Y=</CipherValue>
     </CipherData>
    </EncryptedKey>
   </KeyInfo>
   <CipherData>
    <CipherValue>UDEZ ...QfXUmM5rQ==</CipherValue>
   </CipherData>
  </EncryptedData>
 </connectionStrings>

Now that it is garbled, you can't edit it. Decrypt like this:

aspnet_regiis –pdf connectionStrings c:\PathToWebSite

Or

aspnet_regiis -pd connectionStrings -app "/" -site 6

And then change and re-encrypt.

To read the connection string, use the ConfigurationManager static class.

string connStr = 
ConfigurationManager
.Connectionstrings["MainConnectionString"]
.ConnectionString.ToString();

var myConnection = new SqlConnection(connStr);

myConnection.Open();

Answer 2

One approach is to use whatever integrated security your DB offers, so the password is not an issue. The server gets direct access to the server without having to use a password, but you have to set up a user that only has access from the web server itself.

eg. DBs like MySQL allow you to specify which servers have access to it, restrict access from anywhere else - so a hacker cannot get to your DB except from the web server. This reduces the security surface quite a lot and allows you to store your connection string files in your SCM.

Its still not 100% secure as the hacker could (often easily) hack your web server and view the DB from it. You could store the password elsewhere, but that's just obscuring the problem - if the web server can get access to the password, your hacker can too. (note, other places to store the password include the registry, a separate file like a .udl file or something in /etc). You can secure this file so only the web server user can read it, but a hacked web server can obviously, read it!

So the next step is to abstract the DB connection so it is outside the web server, the usual method is to have a separate process to store your business logic (eg a service) that exposes fixed methods - the web server simply calls the service which does the work and returns data to the web server code.

If a hacker defeats your web server, all they can do is call methods on the service, they would not have direct access to the DB so could not corrupt or modify it. Usually there would be few hints to the hacker of what the service methods were or did, and the service would have a fair amount of validation code to all inputs so a hacker-created message would (hopefully) be rejected. (use timestamps, counters, etc to try to defeat custom-crafted messaged to the service).

This is the approach we used for a high-security system (there's a lot more you can do to secure each segment of this chain using standard OS security mechanisms). The reasons for doing this became very clear to us once our security chap demonstrated a IIS hack that gave him a remote shell with admin privileges. Whatever you do to secure your configs on the web server is pointless if a hacker gets that. (and it was trivially easy to do - since fixed, but there are 0-day exploits being found all the time)