The Ultimate Guide to Connection Strings in Web.config | Cheap ASP.NET Hosting Review

Read Time:6 Minute, 34 Second

Connection strings in web.config can be both powerful and challenging. The times were 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. The 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 useful 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 a 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 straightforward, 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 🙂

Replace config with Web.config transformations

You probably don’t use the same database for local development and running your production environment. Being able to change a connection string depending on which environment your code is currently running, can be done in a number of ways. Cloud providers like Azure already have built-in support for overriding a connection string through app service configuration. But for someone not running on Azure, replacing connection strings can be done using Web.config Transformations.

To use another connection string when your code is running in release configuration, create or open the file named Web.Release.Config and paste the following configuration:

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <connectionStrings>
    <add
       name="myConnection"
       connectionString="server=myprodserver;database=mydatabase;"
       xdt:Transform="SetAttributes"
       xdt:Locator="Match(name)"/>
  </connectionStrings>
</configuration>

When you deploy your website, the myConnection connection string will override the one already specified in the Web.config file. This is automatically done by Web.config Transformations and the xdt:Transform and xdt:Locator attributes.