The ultimate guide to connection strings in web.config

Connection strings in web.config can be both powerful and challenging. The times where you would just remote desktop to production and change settings inside your ASP.NET web.config file is long gone. So, should you keep specifying connection strings using the connectionStrings element in web.config? Stay tuned to learn everything there is to learn about the subject.

Let me start this post by providing an example to make sure that everyone follows. Inside the web.config file (and app.config file too), there's a root element named connectionStrings. The element works a bit like appSettings where you can add one or more connection strings for your application to use on runtime. Configuration looks similar to this:

<?xml version='1.0' encoding='utf-8'?>  
<configuration>  
  <connectionStrings>  
    <add name="myConnection" connectionString="server=localhost;database=mydatabase;" />
  </connectionStrings>
</configuration>

Fetching the connection string is done using the ConfigurationManager class:

string conn = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;

At first glance, this looks like a more complicated version of ConfigurationManager.AppSettings and when used this way, it actually is. Clever things happen when database vendors or ORMs built-in support for the connectionStrings element. Like Entity Framework where connection string names can be referenced when creating the context:

public class DatabaseContext : DbContext
{
    public DatabaseContext() : base("myConnection")
    {
    }
}

Connection strings for popular databases

SQL Server

Since most questions I get about connection strings are related to SQL Server, let's start by looking at the possibilities there. The connection string will be identical no matter if you are connecting through Entity Framework, NHibernate, or using the raw SQL connection available in .NET.

The simplest SQL Server connection string looks like this:

<connectionStrings>  
  <add
    name="sqlServer"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;" />
</connectionStrings>

There are a couple of new things to notice. The providerName attribute tells users of the connection string which .NET Framework Data Provider to use when communicating with the database. The content of the connectionString attribute tells them which server to communicate with and the name of the database. The Integrated Security=True part will use the Windows user executing the program to log onto SQL Server (Windows Authentication using Integrated Security). To make sure the current user is authenticated, open Management Studio, expand the Security node and verify that the current user is added:

Before we move on, I want to mention a couple of commonly used settings.

Connection Timeout

Being able to change the connection timeout through web.config can be very usable when deploying the same application on different environments. Luckily, this is achievable through a simple modification:

<connectionStrings>  
  <add
    name="sqlServer"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;Connection Timeout=60" />
</connectionStrings>

In the example, I've added Connection Timeout=60 to allow the SQL connection 60 seconds to connect to SQL Server (the default value is 15 seconds). Notice that this setting doesn't control command timeout, which is the time allowed for querying one or more tables. Command timeout is not accessible through web.config.

Username/password

<connectionStrings>  
  <add
    name="sqlServer"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=localhost;Initial Catalog=MyDatabase;User Id=user;Password=pwd;" />
</connectionStrings>

MySQL

Connecting to MySQL is as easy as SQL Server. After installing either the MySQL .NET Connector or the MySQL NuGet packages, there's a new provider available through config:

<connectionStrings>  
  <add
    name="mySql"
    providerName="MySql.Data.MySqlClient"
    connectionString="Server=localhost;Database=MyDatabase;Uid=user;Pwd=pwd;" />
</connectionStrings>

There are a lot of options available through the connection string to MySQL. Here are a few of the ones I have used in the past.

Integrated security

<connectionStrings>  
  <add
    name="mySql"
    providerName="MySql.Data.MySqlClient" connectionString="Server=localhost;Database=MyDatabase;IntegratedSecurity=yes;Uid=auth_windows;" />
</connectionStrings>

Connection pool size

<connectionStrings>  
  <add
    name="mySql"
    providerName="MySql.Data.MySqlClient"
    connectionString="...;MinimumPoolSize=10;maximumpoolsize=50;" />
</connectionStrings>

Encryption

<connectionStrings>  
  <add
    name="mySql"
    providerName="MySql.Data.MySqlClient"
    connectionString="...;SslMode=Required;" />
</connectionStrings>

Connection string encryption

In case you cannot use Integrated Security/Windows Authentication, you may have username and password in clear text inside the web.config file. For obvious reasons, you really don't want that. If a hacker gets access to your web server, he/she now has access to your database as well. Encryption to the rescue.

Encrypting connection strings is pretty straight-forward, using the aspnet_regiis tool part of the .NET framework. In most cases, you want encrypted settings on your staging and production environments only, why this step could be part of your deployment pipeline.

To encrypt the entire connectionStrings element, run the following command:

aspnet_regiis -pef "connectionStrings" "c:\path\to\the\folder\containing\webconfig"

The pef parameter tells aspnet_regiis to encrypt the content of the connectionStrings element only and the last parameter points out the folder containing the web.config file. Notice that the path should be to a folder and not the full path of the web.config file. Also, make sure to run cmd as Administrator.

If you have already deployed your application to IIS, you can encrypt it with a similar command:

aspnet_regiis -pe "connectionStrings" -app "/"

Both commands modify the web.config file to now contain encrypted values:

<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>A long cipher value</CipherValue>
        </CipherData>
      </EncryptedKey>
    </KeyInfo>
    <CipherData>
      <CipherValue>Another cipher value</CipherValue>
    </CipherData>
  </EncryptedData>
</connectionStrings>

There's no need to change any C# code to make encrypted connection strings work. ConfigurationManager automatically decrypts connection strings from web.config.

Encrypted data can even be converted back to their original values by using the decryption command:

aspnet_regiis -pdf "connectionStrings" "c:\path\to\the\folder\containing\webconfig"

I know, pdf looks totally weird in this context, but it is short for provider decrypt file or something like that :)

Would your users appreciate fewer errors?

elmah.io is the easy error logging and uptime monitoring service for .NET. Take back control of your errors with support for all .NET web and logging frameworks.

➡️ Error Monitoring for .NET Web Applications ⬅️

This article first appeared on the elmah.io blog at https://blog.elmah.io/the-ultimate-guide-to-connection-strings-in-web-config/