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/
Top comments (0)