The ultimate guide to connection strings in web.config
Tags: csharp, dotnet, webconfig, aspnet
Published: 2020-08-12 06:51:14

---
title: The ultimate guide to connection strings in web.config
published: true
description: Learn everything there is to know about connection strings in web.config. From setting up SQL Server using Windows Authentication to password encryption.
tags: csharp, dotnet, webconfig, aspnet
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/d2o86t6xmvl90aa5a4en.png
canonical_url: https://blog.elmah.io/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
<?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:

```csharp
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:

```csharp
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:

```xml
<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:

![](https://dev-to-uploads.s3.amazonaws.com/i/qnvp0hsm1g9c8dzyyai6.png)

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:

```xml
<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**

```xml
<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:

```xml
<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**

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

**Connection pool size**

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

**Encryption**

```xml
<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:

```powershell
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:

```powershell
aspnet_regiis -pe "connectionStrings" -app "/"
```

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

```xml
<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:

```powershell
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 **p**rovider **d**ecrypt **f**ile 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](https://elmah.io/?utm_source=devto&utm_medium=social&utm_campaign=devtoposts) ⬅️

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