Many people start learning SQL Server, because they want to use a database in application development. You can develop web or client applications with a retail version of Visual Studio or the free edition, Visual Studio 2010 Express (there is also a new product called Web Matrix that we hope to talk about in future posts). A connection string specifies how to connect to your SQL Server database. Development tools can hide or create this connection string for you, but it helps to know how to create one and what it means. In this post, we’ll cover the basics, provide some examples, and point to tools that automatically generate connection strings.

Code Example:

Here is some basic ADO.NET code that uses a connection string:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
    class Program
        static void Main(string[] args)
            SqlConnection sqlConn =
                new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorksLT;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("select count(*) from SalesLT.Address", sqlConn);
            Console.WriteLine("There are {0} rows in the SalesLT.Address table.", Convert.ToInt32(cmd.ExecuteScalar()));

In this example, the SqlConnection object specifies the connection string in the constructor. The connection string contains a set of properties separated by semicolons. This string specifies that my local SQLEXPRESS instance is the database server, AdventureWorksLT is the database, and my current login should be used to authenticate.

Note that some development tools will present you with a dialog box and create the connection string based on your choices. I’ll talk more about this at the end of this post.

Common Connection Strings

There are a lot of properties you can set on your connection string. However, there are a few common connection strings that I’d like to describe. To get a full list of properties that you can use, see the documentation for the SqlConnection.ConnectionString Property.

1. The following connection string connects to a database (Initial Catalog) on the database server (Data Source) and uses Windows Authentication (Integrated Security=True), which means the user that runs your application must have access to the target database server and database.

Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorksLT;Integrated Security=True

2. The following connection string is the same as the first one, except that it does not use Windows Authentication (note that the Integrated Security property is not specified). Instead, it uses SQL Server Authentication, which requires a user name and password. Obviously this opens up a concern about people seeing this information in the code or in a configuration file (see Connection Strings and Configuration Files).

Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorksLT;User ID=myuser;Password=mypassword

3. The following connection string uses a feature called User Instances. A user instance opens up a target database file directly in a new instance of SQL Server Express that is running under the context of the current user. This is useful for users that are not administrators on their machines. Note that the database can not also be attached to a running SQL Server instance for this type of connection to succeed, which means you won’t see this database in a tool like SQL Server Management Studio unless you attach to it.

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Temp\MyDatabase.mdf;Integrated Security=True;User Instance=True

Connection String Tools

There are a few tips for generating connection strings. First, there is a great website that provides examples for many more connection strings than I have given here:

Second, you can use Visual Studio. If you are working in Microsoft Visual Web Developer 2010, then you should open the Database Explorer window. If you’re working in other editions of Visual Studio, open the Server Explorer window. From those panes, you can right-click on the Data Connections node and select “Add Connection“. The wizard guides you through the basic choices. Once you have a connection, you can select it and the Properties window shows you the connection string. Here is a screen shot that shows this:

There are also many other features that Visual Studio can use when you setup connections in this way. Other wizards or properties that require a database connection can use this connection. But here I’m showing that you can use this technique to quickly get the connection string from the properties window. You can then use that string directly in whatever code you wish.