SQL Server Connection Strings

By Bill Graziano on 14 November 2007 | 13 Comments | Tags: Security, .NET


Some common and not so common connection strings for the .NET SqlConnection object.  The article includes .NET sample code and some tricks to increase the supportability of your application.

Trusted Authentication

Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI;

Trusted authentication uses the security credentials of the current user to make the connection to SQL Server.  SQL Server uses Windows (or Active Directory) to validate the current user.  ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2.  ServerName can also be expressed as an IP address.  SSPI stands for Security Support Provider Interface (in you were curious). 

SQL Server Security Authentication

Data Source=ServerName; Initial Catalog=DatabaseName; User Id=UserName; Password=UserPassword;

In SQL Server authentication SQL Server stores the username and password.  ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2.  ServerName can also be expressed as an IP address.

Setting the Application Name

Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; Application Name=MyAppName;

I often set the Application Name when I construct connections strings.  Whatever text you assign to Application Name will appear in a couple of different places:

  • It will be displayed in Profiler under the Application Name column.
  • It will be shown in the output of sp_who2 in the Program Name column.
  • It will be shown in the Activity Monitor in the Application column.  You can get to the Activity Monitor in SQL Server Management Studio by Management -> Activity Monitor.
  • It will appear in the program_name column if you select from master.dbo.sysprocesses (for SQL Server 2000)
  • It will appear int he program_name column if you select from sys.dm_exec_sessions (for SQL Server 2005 and later).

Setting the application name makes it very easy to find out what applications are issuing particular SQL statements against my database.  Setting the application name can also lead to an increase in the number of connections to your SQL Server.  Each client that uses connection pooling will create one pool inside each application per unique connection string.  If you use multiple application names you have the possibility to increase the number of pools and thus the number of connections to SQL Server.  I've always found it more beneficial to have the application name than to have a few less connections to my database servers.

Using MARS (Multiple Active Result Sets)

Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; MultipleActiveResultSets=True;

If you want to use MARS you'll need to enable it in the connection string.

Sample .NET code

There are two common ways to create a connection string in .NET.  The first is to use an explicit connection string.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=L40; Initial Catalog=master; Integrated Security=SSPI;";

The second is to use the Connection String Builder object in .NET to construct a connection string.

SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = "L40";
csb.InitialCatalog = "master";
csb.IntegratedSecurity = true;

SqlConnection conn = new SqlConnection();
conn.ConnectionString = csb.ToString();

Discuss this article: 13 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Alerts for when Login Failures Strike (14 July 2008)

Understanding the Difference between Owners and Schemas in SQL Server (5 October 2007)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server (4 May 2007)

Using SQL Server 2005 fulltext search from ASP.NET 2.0 (5 February 2007)

Improving Data Security by Using SQL Server 2005 (28 October 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

How to Asynchronously Execute a DTS package from ASP or ASP.NET (27 March 2005)

Other Recent Forum Posts

How to add a dropdown to a texbox inside a report. (5 Replies)

Randomly Assign to Group (4 Replies)

Year only from datetime (0 Replies)

Partitioning existing Databas (4 Replies)

need help on Covering Indexes (1 Reply)

C# code to put a dropdown list to excel (0 Replies)

Update Date based on Employee (4 Replies)

Repeat records depending field value (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -