News, examples, tips, ideas and plans.
Thoughts around ORM, .NET and SQL databases.

Thursday, January 13, 2011

Making connection strings secure

Recently, Paul Sinnema from Diartis AG asked on the support website how to make connection strings, listed in application configuration files (app.config or web.config), more secure.


For that time the only thing we could suggest was to use a workaround like that:

1. Encrypt the required connection string with encryption method you prefer and put it somewhere in web.config/app.config file in encrypted form.

2. Load DomainConfiguration through standard API:

var config = DomainConfiguration.Load("mydomain");

3. Set manually config.ConnectionInfo property with decrypted connection string

config.ConnectionInfo = new ConnectionInfo("sqlserver",
  DecryptMyConnectionString(encryptedConnectionString));

4. Build Domain with the config.

Paul answered that although the workaround is useful, he'd prefer that DataObjects.Net could be integrated somehow with standard .NET configuration subsystem that provides easy encrypting/decrypting of connection strings.

Let me explain the approach, Paul was talking about. For example, if plain connection string configuration looks like that;

  <connectionStrings>
     <add name="cn1" connectionString="Data Source=localhost\SQL2008;Initial Catalog=DO40-Tests;Integrated Security=True;MultipleActiveResultSets=True" />
  </connectionStrings>

then after executing "aspnet_regiis.exe -pef "connectionStrings" %PATH_TO_PROJECT%" the encrypted one will look like the following:

  <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>TJETPde0GVrSAKk0VpUs3EsV8XFRQrVU1lGlrX8aEhvfa4MaMpN3O6KLo3I9e5oujXBImKtmXHWxnF6KRcSWteuQXD4I37Dq6yE1cZ4qDHqNMkcQK+lgi6jbRLcPzYQdFuOLagsAVpif8OvY0dJkiKd+Srqjwz7ZO225VCipaQs=</CipherValue>
          </CipherData>
        </EncryptedKey>
      </KeyInfo>
      <CipherData>
        <CipherValue>6bQyCUdH9rqdNAD4IwmSXtAQIEuRfSjpQ/Py5ovz2ZpwJdEXa4W3OKWMm52g1ARXPqMYw1thfQae//+1JWTAPQwrE3JTZEdcgu47KE2x8uqdWeJfoXSecp6berSPc4qAa4F2B5x8weUGPiAsBldVfBXwUBPniKWbnnXViJWmcSn16kaAtIkq5GtNOp7YhPt9WKZG0QkFRpEaRAWqLgxK6nXUwK8BgbGEBI4B27QSFj2ME1/JI+o//RrchntEuW6VolEA7PAejMujsIn1xb0hqg==</CipherValue>
      </CipherData>
    </EncryptedData>
  </connectionStrings>

As a result, the connection strings get encrypted and therefore, much more secure. Working with them is as simple as usual: they can be accesses through standard ConfigurationManager without any special tricks:

ConfigurationManager.ConnectionStrings["cn1"].ConnectionString
And this simplicity makes the whole approach extremely useful.

So, what could be done to get DataObjects.Net compatible with all this shiny stuff, keeping in mind that connection strings section is outside DataObjects.Net configuration?

After thinking for a while on the problem, we've got the following idea: what if we put in domain configuration not a connection string itself, but a reference to the connection string, that is actually defined in connectionStrings section? For instance:

<Xtensive.Orm>
    <domains>
      <domain provider="sqlserver" connectionString="#cn1" ... />
    </domain>
  </domains>
<Xtensive.Orm>
Where:
  • cn1 is a name of a connection string defined in connectionStrings section;
  • # sign is used to inform that actually this is a reference to a connection string and DataObjects.Net must resolve it through ConfigurationManager.
Having this feature implemented, DataObjects.Net will provide support for encrypted connection strings out-of-the-box.

And now, we'd like to ask you some important questions:
  • What would you say about the feature?
  • Will it be useful for you and should it be implemented?
  • Is the "#name" notation for setting a reference to a connection string is more or less clear or wouldn't it better to change it to something else?

Dear users of DataObjects.Net, we need your feedback. Please, post a comment or two.

Thanks in advance.

4 comments:

  1. * What would you say about the feature?
    Nice feature.
    Usually i'm using custom ConfigurationSections and ConfigurationSection.Encrypt/Decrypt methods to hide some sensitive info so if i'll have it out-of-the-box it will be nice (because i'll need to do less things:))

    * Will it be useful for you and should it be implemented?
    Definitly

    * Is the "#name" notation for setting a reference to a connection string is more or less clear or wouldn't it better to change it to something else?
    As for me it is clear.

    ReplyDelete
  2. Definitly nice and usefull feature!

    ReplyDelete