Connection Strings and Configuration Files

Embedding connection strings in your application's code can lead to security vulnerabilities and maintenance problems. Unencrypted connection strings compiled into an application's source code can be viewed using the Ildasm.exe (IL Disassembler) tool. Moreover, if the connection string ever changes, your application must be recompiled. For these reasons, we recommend storing connection strings in an application configuration file.

Working with Application Configuration Files

Application configuration files contain settings that are specific to a particular application. For example, an ASP.NET application can have one or more web.config files, and a Windows application can have an optional app.config file. Configuration files share common elements, although the name and location of a configuration file vary depending on the application's host.

The connectionStrings Section

Connection strings can be stored as key/value pairs in the connectionStrings section of the configuration element of an application configuration file. Child elements include add, clear, and remove.

The following configuration file fragment demonstrates the schema and syntax for storing a connection string. The name attribute is a name that you provide to uniquely identify a connection string so that it can be retrieved at run time. The providerName is the invariant name of the .NET Framework data provider, which is registered in the machine.config file.

<?xml version='1.0' encoding='utf-8'?>  
  <configuration>  
    <connectionStrings>  
      <clear />  
      <add name="Name"
       providerName="System.Data.ProviderName"
       connectionString="Valid Connection String;" />  
    </connectionStrings>  
  </configuration>  

Using External Configuration Files

External configuration files are separate files that contain a fragment of a configuration file consisting of a single section. The external configuration file is then referenced by the main configuration file. Storing the connectionStrings section in a physically separate file is useful in situations where connection strings may be edited after the application is deployed. For example, the standard ASP.NET behavior is to restart an application domain when configuration files are modified, which results in state information being lost. However, modifying an external configuration file does not cause an application restart. External configuration files are not limited to ASP.NET; they can also be used by Windows applications. In addition, file access security and permissions can be used to restrict access to external configuration files. Working with external configuration files at run time is transparent, and requires no special coding.

To store connection strings in an external configuration file, create a separate file that contains only the connectionStrings section. Do not include any additional elements, sections, or attributes. This example shows the syntax for an external configuration file.

<connectionStrings>  
  <add name="Name"
   providerName="System.Data.ProviderName"
   connectionString="Valid Connection String;" />  
</connectionStrings>  

In the main application configuration file, you use the configSource attribute to specify the fully qualified name and location of the external file. This example refers to an external configuration file named connections.config.

<?xml version='1.0' encoding='utf-8'?>  
<configuration>  
    <connectionStrings configSource="connections.config"/>  
</configuration>  

Retrieving Connection Strings at Run Time

The .NET Framework 2.0 introduced new classes in the System.Configuration namespace to simplify retrieving connection strings from configuration files at run time. You can programmatically retrieve a connection string by name or by provider name.

Working with the Configuration Classes

Starting with .NET Framework 2.0, ConfigurationManager is used when working with configuration files on the local computer, replacing the deprecated ConfigurationSettings. WebConfigurationManager is used to work with ASP.NET configuration files. It is designed to work with configuration files on a Web server, and allows programmatic access to configuration file sections such as system.web.

You can use the ConnectionStringSettingsCollection to retrieve connection strings from application configuration files. It contains a collection of ConnectionStringSettings objects, each of which represents a single entry in the connectionStrings section. Its properties map to connection string attributes, allowing you to retrieve a connection string by specifying the name or the provider name.

Example: Listing All Connection Strings

This example iterates through the ConnectionStringSettingsCollection and displays the ConnectionStringSettings.Name, ConnectionStringSettings.ProviderName, and ConnectionStringSettings.ConnectionString properties in the console window.

using System.Configuration;

static class Program
{
    static void Main()
    {
        GetConnectionStrings();
        Console.ReadLine();
    }

    static void GetConnectionStrings()
    {
        ConnectionStringSettingsCollection settings =
            ConfigurationManager.ConnectionStrings;

        foreach (ConnectionStringSettings cs in settings)
        {
            Console.WriteLine(cs.Name);
            Console.WriteLine(cs.ProviderName);
            Console.WriteLine(cs.ConnectionString);
        }
    }
}

Imports System.Configuration

Class Program
    Shared Sub Main()
        GetConnectionStrings()
        Console.ReadLine()
    End Sub

    Private Shared Sub GetConnectionStrings()

        Dim settings As ConnectionStringSettingsCollection = _
            ConfigurationManager.ConnectionStrings

        If Not settings Is Nothing Then
            For Each cs As ConnectionStringSettings In settings
                Console.WriteLine(cs.Name)
                Console.WriteLine(cs.ProviderName)
                Console.WriteLine(cs.ConnectionString)
            Next
        End If
    End Sub
End Class

Example: Retrieving a Connection String by Name

This example demonstrates how to retrieve a connection string from a configuration file by specifying its name. The code creates a ConnectionStringSettings object, matching the supplied input parameter to the ConnectionStrings name. If no matching name is found, the function returns null (Nothing in Visual Basic).

// Retrieves a connection string by name.
// Returns null if the name is not found.
static string? GetConnectionStringByName(string name)
{
    // Look for the name in the connectionStrings section.
    ConnectionStringSettings? settings =
        ConfigurationManager.ConnectionStrings[name];

    // If found, return the connection string (otherwise return null)
    return settings?.ConnectionString;
}

' Retrieves a connection string by name.
' Returns Nothing if the name is not found.
Private Shared Function GetConnectionStringByName( _
    ByVal name As String) As String

    ' Assume failure
    Dim returnValue As String = Nothing

    ' Look for the name in the connectionStrings section.
    Dim settings As ConnectionStringSettings = _
       ConfigurationManager.ConnectionStrings(name)

    ' If found, return the connection string.
    If Not settings Is Nothing Then
        returnValue = settings.ConnectionString
    End If

    Return returnValue
End Function

Example: Retrieving a Connection String by Provider Name

This example demonstrates how to retrieve a connection string by specifying the provider-invariant name in the format System.Data.ProviderName. The code iterates through the ConnectionStringSettingsCollection and returns the connection string for the first ProviderName found. If the provider name is not found, the function returns null (Nothing in Visual Basic).

// Retrieve a connection string by specifying the providerName.
// Assumes one connection string per provider in the config file.
static string? GetConnectionStringByProvider(string providerName)
{
    // Get the collection of connection strings.
    ConnectionStringSettingsCollection? settings =
        ConfigurationManager.ConnectionStrings;

    // Walk through the collection and return the first
    // connection string matching the providerName.
    if (settings != null)
    {
        foreach (ConnectionStringSettings cs in settings)
        {
            if (cs.ProviderName == providerName)
            {
                return cs.ConnectionString;
            }
        }
    }
    return null;
}

' Retrieve a connection string by specifying the providerName.
' Assumes one connection string per provider in the config file.
Private Shared Function GetConnectionStringByProvider( _
    ByVal providerName As String) As String

    'Return Nothing on failure.
    Dim returnValue As String = Nothing

    ' Get the collection of connection strings.
    Dim settings As ConnectionStringSettingsCollection = _
        ConfigurationManager.ConnectionStrings

    ' Walk through the collection and return the first 
    ' connection string matching the providerName.
    If Not settings Is Nothing Then
        For Each cs As ConnectionStringSettings In settings
            If cs.ProviderName = providerName Then
                returnValue = cs.ConnectionString
                Exit For
            End If
        Next
    End If

    Return returnValue
End Function

Encrypting Configuration File Sections Using Protected Configuration

ASP.NET 2.0 introduced a new feature, called protected configuration, that enables you to encrypt sensitive information in a configuration file. Although primarily designed for ASP.NET, protected configuration can also be used to encrypt configuration file sections in Windows applications. For a detailed description of the protected configuration capabilities, see Encrypting Configuration Information Using Protected Configuration.

The following configuration file fragment shows the connectionStrings section after it has been encrypted. The configProtectionProvider specifies the protected configuration provider used to encrypt and decrypt the connection strings. The EncryptedData section contains the cipher text.

<connectionStrings configProtectionProvider="DataProtectionConfigurationProvider">  
  <EncryptedData>  
    <CipherData>  
      <CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAH2... </CipherValue>  
    </CipherData>  
  </EncryptedData>  
</connectionStrings>  

When the encrypted connection string is retrieved at run time, the .NET Framework uses the specified provider to decrypt the CipherValue and make it available to your application. You do not need to write any additional code to manage the decryption process.

Protected Configuration Providers

Protected configuration providers are registered in the configProtectedData section of the machine.config file on the local computer, as shown in the following fragment, which shows the two protected configuration providers supplied with the .NET Framework. The values shown here have been truncated for readability.

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">  
  <providers>  
    <add name="RsaProtectedConfigurationProvider"
      type="System.Configuration.RsaProtectedConfigurationProvider" />  
    <add name="DataProtectionConfigurationProvider"
      type="System.Configuration.DpapiProtectedConfigurationProvider" />  
  </providers>  
</configProtectedData>  

You can configure additional protected configuration providers by adding them to the machine.config file. You can also create your own protected configuration provider by inheriting from the ProtectedConfigurationProvider abstract base class. The following table describes the two configuration files included with the .NET Framework.

Both providers offer strong encryption of data. However, if you are planning to use the same encrypted configuration file on multiple servers, such as a Web farm, only the RsaProtectedConfigurationProvider enables you to export the encryption keys used to encrypt the data and import them on another server. For more information, see Importing and Exporting Protected Configuration RSA Key Containers.

Using the Configuration Classes

The System.Configuration namespace provides classes to work with configuration settings programmatically. The ConfigurationManager class provides access to machine, application, and user configuration files. If you are creating an ASP.NET application, you can use the WebConfigurationManager class, which provides the same functionality while also allowing you to access settings that are unique to ASP.NET applications, such as those found in <system.web>.

App.config Example

This example demonstrates how to toggle encrypting the connectionStrings section in an app.config file for a Windows application. In this example, the procedure takes the name of the application as an argument, for example, "MyApplication.exe". The app.config file will then be encrypted and copied to the folder that contains the executable under the name of "MyApplication.exe.config".

The code uses the OpenExeConfiguration method to open the app.config file for editing, and the GetSection method returns the connectionStrings section. The code then checks the IsProtected property, calling the ProtectSection to encrypt the section if it is not encrypted. The UnprotectSection method is invoked to decrypt the section. The Save method completes the operation and saves the changes.

static void ToggleConfigEncryption(string exeFile)
{
    // Get the application path needed to obtain
    // the application configuration file.

    // Takes the executable file name without the
    // .config extension.
    var exePath = exeFile.Replace(".config", "");

    try
    {
        // Open the configuration file and retrieve
        // the connectionStrings section.
        Configuration config = ConfigurationManager.
            OpenExeConfiguration(exePath);

        var section =
            config.GetSection("connectionStrings")
            as ConnectionStringsSection;

        if (section != null)
        {
            if (section.SectionInformation.IsProtected)
            {
                // Remove encryption.
                section.SectionInformation.UnprotectSection();
            }
            else
            {
                // Encrypt the section.
                section.SectionInformation.ProtectSection(
                    "DataProtectionConfigurationProvider");
            }
        }
        // Save the current configuration.
        config.Save();

        Console.WriteLine("Protected={0}",
            section?.SectionInformation.IsProtected);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Shared Sub ToggleConfigEncryption(ByVal exeConfigName As String)
    ' Takes the executable file name without the
    ' .config extension.
    Try
        ' Open the configuration file and retrieve 
        ' the connectionStrings section.
        Dim config As Configuration = ConfigurationManager. _
            OpenExeConfiguration(exeConfigName)

        Dim section As ConnectionStringsSection = DirectCast( _
            config.GetSection("connectionStrings"), _
            ConnectionStringsSection)

        If section.SectionInformation.IsProtected Then
            ' Remove encryption.
            section.SectionInformation.UnprotectSection()
        Else
            ' Encrypt the section.
            section.SectionInformation.ProtectSection( _
              "DataProtectionConfigurationProvider")
        End If

        ' Save the current configuration.
        config.Save()

        Console.WriteLine("Protected={0}", _
        section.SectionInformation.IsProtected)

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
End Sub

Web.config Example

This example uses the OpenWebConfiguration method of the WebConfigurationManager. Note that in this case you can supply the relative path to the Web.config file by using a tilde. The code requires a reference to the System.Web.Configuration class.

static void ToggleWebEncrypt()
{
    // Open the Web.config file.
    Configuration config = WebConfigurationManager.
        OpenWebConfiguration("~");

    // Get the connectionStrings section.
    var section =
        config.GetSection("connectionStrings")
        as ConnectionStringsSection;

    // Toggle encryption.
    if (section.SectionInformation.IsProtected)
    {
        section.SectionInformation.UnprotectSection();
    }
    else
    {
        section.SectionInformation.ProtectSection(
            "DataProtectionConfigurationProvider");
    }

    // Save changes to the Web.config file.
    config.Save();
}

Shared Sub ToggleWebEncrypt()
    ' Open the Web.config file.
    Dim config As Configuration = WebConfigurationManager. _
      OpenWebConfiguration("~")

    ' Get the connectionStrings section.
    Dim section As ConnectionStringsSection = DirectCast( _
        config.GetSection("connectionStrings"), _
        ConnectionStringsSection)

    ' Toggle encryption.
    If section.SectionInformation.IsProtected Then
        section.SectionInformation.UnprotectSection()
    Else
        section.SectionInformation.ProtectSection( _
          "DataProtectionConfigurationProvider")
    End If

    ' Save changes to the Web.config file.
    config.Save()
End Sub

For more information about securing ASP.NET applications, see Securing ASP.NET web sites.

See also

• Connection String Builders
• Protecting Connection Information
• Using the Configuration Classes
• Configuring Apps
• ASP.NET Web Site Administration
• ADO.NET Overview