Encrypt SQL Connection Strings with DPAPI

Posted by Serge Truth on March 15, 2011 at 9:26 AM

What to Do

Encrypt SQLIf your application relies on SQL authentication, credentials must be protected in configuration files. Use a protected configuration provider to encrypt connection strings before storing them in configuration files.

Why

Connection strings contain sensitive resource access credentials (e.g., a connection string for a SQL server resource includes a username and password.) Connection strings stored in plaintext are dangerous, because an attacker that can compromise a server will be able to read those connection strings. Even if a machine is not compromised, connection strings stored in plain text are accessible to administrators and any other users with sufficient privileges on the host machine and/or Windows domain.

When

Always encrypt connection strings. The Windows Data Protection API (DPAPI) is the default provider and is an acceptable choice when choosing a protected configuration provider under most circumstances. However, in a Web farm environment, the RSA-protected configuration provider is a good choice because the RSA-based provider use asymmetric, public key encryption to encrypt and decrypt data keys that can be exported and imported across servers. If neither of these suffice, you can create your own custom provider.

How

Perform the following actions to secure the database connection strings in configuration files:

  1. Identify the configuration sections to be encrypted. Encrypting and decrypting data incurs performance overhead. To keep this overhead to a minimum, encrypt only the sections of your configuration file that store sensitive data. In this case, we are interested in encrypting the <connectionStrings> element of the Web.config file since that is where the database connection string will reside.

  2. Choose the protected configuration provider. Under most circumstances DPAPI will suffice, although the RSA protected configuration is the logical choice in web farms where multiple servers are employed. The .NET Framework includes a protected configuration feature that you can use to encrypt sensitive configuration file data by using a command line tool. The following two protected configuration providers are provided although you can also implement custom providers.

    • RSA Protected Configuration Provider. This provider uses RSA public key encryption to encrypt and decrypt data and is a good choice for web farm environments.
    • DPAPIProtectedConfigurationProvider. This is the default provider, which uses the Windows Data Protection API (DPAPI) to encrypt and decrypt data. This section describes how to use DPAPI to encrypt connection strings. Please check the related guidelines for more information on using the RSA Protected Configuration Provider and DPAPI.
  3. Choose the machine or user store. The DataProtectionConfigurationProvider supports machine-level and user-level stores for key storage. The choice of store depends largely on whether or not your application shares a server with other applications and whether or not sensitive data must be kept private for each application.

    • Machine Store. By default, the DataProtectionConfigurationProvider is configured to use DPAPI with the machine store. Use machine-level key storage if your application runs on its own dedicated server with no other applications, or if you have multiple applications on the same server that run and you want those applications to be able to share sensitive information.
    • User Store. Use user-level key storage if you run your application in a shared hosting environment and you want to make sure that your application's sensitive data is not accessible to other applications on the server. In this situation, each application should run under a separate identity, and the resources for the application—such as files and databases—should be restricted to that identity.
  4. Encrypt your configuration file data. To encrypt the connectionStrings section in Web.config using DPAPI with the Machine Store, run the following command from a .NET command prompt:

    aspnet_regiis -pe "connectionStrings"
    -app "/MachineDPAPI"
    -prov "DataProtectionConfigurationProvider"

    The above command with the -app switch assumes that there is an IIS virtual directory called MachineDPAPI. If you are using the Visual Studio .NET Web server instead of IIS, use the -pef switch, which allows you to specify the physical directory location of your configuration file.

    aspnet_regiis.exe
    -pef "connectionStrings" C:\Projects\MachineDPAPI
    -prov "DataProtectionConfigurationProvider"

Adapted from Microsoft patterns & practices guidance.

Topics: developer guidance, application security

Serge Truth

Written by Serge Truth

Serge is a Content Lead here at Security Innovation. He is an IT and Information Security professional, certified by the Committee on National Security Systems Instruction.