The ultimate guide to connection strings in web.config

TOC

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 lot proceed specifying connection strings using the connectionStrings element in spider web.config? Stay tuned to learn everything there is to acquire about the subject.

The ultimate guide to connection strings in web.config

Permit me showtime this post by providing an example to make certain that anybody follows. Within 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 connexion strings for your application to employ on runtime. The configuration looks similar to this:

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

Fetching the connection cord is done using the ConfigurationManager class:

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

At starting time glance, this looks similar a more than complicated version of ConfigurationManager.AppSettings and when used this fashion, it actually is. Clever things happen when database vendors or ORMs congenital-in support for the connectionStrings element. Like Entity Framework where connection string names can exist referenced when creating the context:

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

SQL Server

Since well-nigh questions I get near connectedness strings are related to SQL Server, allow'southward start by looking at the possibilities at that place. The connection cord will be identical no matter if yous are connecting through Entity Framework, NHibernate, or using the raw SQL connexion available in .Internet.

The simplest SQL Server connection string looks like this:

              <connectionStrings>     <add     name="sqlServer"     providerName="Organization.Information.SqlClient"     connectionString="Data Source=localhost;Initial Itemize=MyDatabase;Integrated Security=Truthful;" /> </connectionStrings>

In that location are a couple of new things to notice. The providerName aspect tells users of the connectedness string which .NET Framework Information Provider to utilise when communicating with the database. The content of the connectionString aspect tells them which server to communicate with and the name of the database. The Integrated Security=True function volition use the Windows user executing the program to log onto SQL Server (Windows Authentication using Integrated Security). To make certain the current user is authenticated, open Management Studio, expand the Security node, and verify that the current user is added:

Current user beneath Security

Before we movement on, I desire to mention a couple of commonly used settings.

Connexion Timeout

Being able to alter the connexion timeout through web.config tin be very useful when deploying the aforementioned application on different environments. Luckily, this is doable through a uncomplicated modification:

              <connectionStrings>     <add     name="sqlServer"     providerName="Arrangement.Information.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). Find that this setting doesn't control command timeout, which is the time immune for querying one or more than tables. Command timeout is not accessible through web.config.

Username/password 

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

MySQL

Connecting to MySQL is as easy equally SQL Server. Afterwards installing either the MySQL .Cyberspace Connector or the MySQL NuGet packages, in that location'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.Information.MySqlClient"     connectionString="Server=localhost;Database=MyDatabase;IntegratedSecurity=yes;Uid=auth_windows;" /> </connectionStrings>

Connection puddle size 

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

Encryption 

              <connectionStrings>     <add together     proper noun="mySql"     providerName="MySql.Information.MySqlClient"     connectionString="...;SslMode=Required;" /> </connectionStrings>

Connection cord 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 at present has access to your database equally well. Encryption to the rescue.

Encrypting connexion strings is pretty straightforward, using the aspnet_regiis tool part of the .NET framework. In almost cases, you want encrypted settings on your staging and product environments merely, why this footstep could be role of your deployment pipeline.

To encrypt the entire connectionStrings element, run the post-obit 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 simply and the concluding parameter points out the folder containing the web.config file. Detect that the path should be to a binder and not the total path of the web.config file. Also, brand certain to run cmd as Administrator.

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

              aspnet_regiis -pe "connectionStrings" -app "/"

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

              <connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">   <EncryptedData Blazon="http://world wide web.w3.org/2001/04/xmlenc#Chemical 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://world wide web.w3.org/2000/09/xmldsig#">           <KeyName>Rsa Primal</KeyName>         </KeyInfo>         <CipherData>           <CipherValue>A long cipher value</CipherValue>         </CipherData>       </EncryptedKey>     </KeyInfo>     <CipherData>       <CipherValue>Some other cipher value</CipherValue>     </CipherData>   </EncryptedData> </connectionStrings>

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

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

              aspnet_regiis -pdf "connectionStrings" "c:\path\to\the\binder\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 surroundings 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 connectedness string through app service configuration. Merely for someone not running on Azure, replacing connection strings can be washed using Web.config Transformations.

To utilise some other connectedness 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        proper name="myConnection"        connectionString="server=myprodserver;database=mydatabase;"        xdt:Transform="SetAttributes"        xdt:Locator="Friction match(name)"/>   </connectionStrings> </configuration>

When you deploy your website, the myConnection connection string volition override the one already specified in the Web.config file. This is automatically done past Web.config Transformations and the xdt:Transform and xdt:Locator attributes. For more information and examples of Web.config Transformations, cheque out the following mail service: Web.config transformations - The definitive syntax guide. Also, make sure to validate your Spider web.Release.Config file with the Spider web.config Transformation Tester tool.

Validating and fault monitoring

Writing and testing connection strings by and large require you to launch your project and test the connection through a working site in the browser. Visual Studio has IntelliSense for the overall XML construction but not the content within the attributes. I always recommend people to use the Web.config Validator to validate that the XML part is correct. If you lot are replacing product-specific connection strings with Spider web.config transformations, make certain to validate your transformation with the Web.config Transformation Tester tool.

As for monitoring for errors, I recommend you lot implement a good error monitoring solution on your website running in product. As the founder of elmah.io, I desire to recommend you lot to use that. Only in that location are similar solutions out there that will get you almost every bit far. Just promise me not to log errors in log files that no one will always wait through. Getting error notifications through emails, Slack, Teams, or similar should exist essential for all .NET web developers.

elmah.io: Error logging and Uptime Monitoring for your spider web apps

This blog post is brought to yous by elmah.io. elmah.io is error logging, uptime monitoring, deployment tracking, and service heartbeats for your .NET and JavaScript applications. Stop relying on your users to notify you when something is wrong or dig through hundreds of megabytes of log files spread across servers. With elmah.io, we shop all of your log messages, notify you through popular channels like email, Slack, and Microsoft Teams, and help y'all fix errors fast.

elmah.io app banner

Meet how we can help y'all monitor your website for crashes Monitor your website