Manage Connection Strings

Before we can retrieve, modify or even see any data stored in a database, regardless of the engine used, we should set up a connection string. With it, we can instantiate a Connection object, and use it to open the database connection.

So connection strings are such a fundamental part of communicating with databases, but what are they? Basically, they are string literals. But you should treat and see them as name-value collections. ADO.NET has classes which help you build connection strings in this manner. They have the suffix ConnectionStringBuilder, following the prefix of the database engine (for example, SQL). But that’s a further topic which I’ll talk about a bit later in this post.

Let’s see a valid connection string:

Data Source=.\SQLEXPRESS; Integrated Security=SSPI; Initial Catalog=Northwind;

The previous string uses the SQLEXPRESS SQL server on the local machine (the . always means the local machine), and uses Windows Authentication, which should be used in most cases. The most important properties of the connection strings:

Application Name The name of the application
Async Enables asynchronous support when set to true/yes.
AttachDbFilename/Initial File Name When pointing to a file with .mdf extension that file will be attached and used.
Connect Timeout The timeout of the connection, in seconds. An exception is thrown when it elapses.
Context Connection Set to true when using In-process connections.
Data Source/Server/Address/Add/Network Address The address of the database server.
Encrypt Turns on SSL encryption for all communications.
Failover Partner The failover database server to use.
Initial Catalog/Database The database to use.
Integrated Security/Trusted Connection When set to true/yes/SSPI, it uses Windows Authentication. When omitted or set to no/false, you must use SQL Authentication.
MultipleActive ResultSets If MARS is used, set it to true.
Password/pwd;User Name/Uid User name and password for SQL Authentication.
User Instance If user instances are enabled, one of them will be used instead of the default server instance.

 

You have two main ways to store your connection strings:

  • Hard-code them
  • Store them in .config files

Hard-coding connection strings isn’t the best idea ever, because later modifications need a recompilation of the application. However, this method is supported, so you can use it. The easier approach is to instantiate a plain string for the connection string, and pass it to a Connection object. The somewhat more elegant method is to use the ConnectionStringBuilder classes. They allow you to write and manage connection strings in a dictionary manner. Furthermore, they throw exceptions when an invalid connection string is provided for them, and they don’t even try to use it.

The better way of storing connection strings is to place them in configuration files. The .NET Framework provides these files to store application preferences, settings, behavior, etc. The section you should use is the connectionStrings. To retrieve a connection string from these files, use the following code:

ConfigurationManager.ConnectionStrings[“myString”].ConnectionString;

Or use the WebConfigurationManager class when you are dealing with ASP.NET web applications.
Configuration files tend to be a good location for your connection strings, but sadly, they are just plain, human-readable XML files, so they aren’t so secure when you need to use SQL Authentication, and must include a user name and password. In this case, you should encrypt it.

Two encryption methods are available (and this can be extended unlimitedly). The first one is RsaProtectedConfigurationProvider. This is one comes handy in web farm environments, since it provides a shareable key to encrypt/decrypt the information. The second is DPAPI, Windows’ data protection API. This one is makes sense when you just need to secure information on one machine only.

You can encrypt/decrypt programmatically, and there’s a separate tool for that, too, called aspnet_regiis. Search for it on this blog, I’ve written about it before.

For further security, you can attach database files on the fly. This can be done by specifying the AttachDbFileName property in the connection string. However, you need to be an administrator to do so. There’s a workaround for this catch, namely the User Instance switch in the connection string.

Enough talking, let’s see a full-grown ADO.NET connection (but only a basic one):

using System.Data.SqlClient;
SqlConnection theConnection = new SqlConnection();
theConnection.ConnectionString = ConfigurationManager.ConnectionStrings[“myString”].ConnectionString;
theConnection.Open();
if(theConnection.State == ConnectionState.Open)
Console.WriteLine(“Succesful connection”);

Note that this example doesn’t use exception handling (a topic of a yet-to-come post), and the connection string itself is stored in the configuration file.

A last question: which authentication mode should we prefer? The answer is easy. If you can use Windows Authentication (Integrated Security=SSPI), then use it. It provides a much better and secure authentication mode, since there’s no need to store user names and passwords, nor to send them to the server. In those cases when you can’t use it (you don’t have a Windows account on the database server, and you can’t set up one), you should use SQL Authentication. But make sure that you use SSL (Encrypt = true) in these cases.

Advertisements

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: