Express Essentials: Connection Strings

In "The Code Connection Between Visual Studio 2005 and SQL Server Express," I showed you how to code a connection between Visual Basic Express and SQL Server Express. A coded connection is handy because you can easily cut and paste it into new applications or even use it to create custom code snippets. You absolutely must use the right .NET namespace and the right ADO.NET SQLConnection object methods, but once you have those requirements down, the next most important thing is the connection string.

The connection string tells the application how to connect to a database server. The connection string consists of semicolon-delimited keyword pairs that take the form of keyword=value. Although I don't have room here to discuss all the keywords, the three most common keywords and their meanings are

- Server (or Data Source, Address, Addr, or Network Address)

The Server keyword (or any of its alternatives) specifies the name or network address of the SQL Server instance you want to connect to. Although you can use any of the alternative keyword names, Server is the most widely used.

- Integrated Security (or Trusted_Connection)

The Integrated Security (or Trusted_Connection) keyword specifies that integrated security be used as the authentication method--in other words, that your Windows login be passed to SQL Server Express. With this keyword, you use a value of True or SSPI to indicate that Windows authentication is to be used to connect to the database. A value of False causes SQL Server authentication to be used.

- Database (or Initial Catalog)

This Database (or Initial Catalog) keyword provides the name of the SQL Server target database that will be used after the application connects to the server.

A connection string must specify at least the SQL Server instance and the type of authentication to be used. A very simple connection string is

"SERVER=.SQLEXPRESS;INTEGRATED SECURITY=TRUE"

As this example shows, the value used with the Server keyword for a local SQL Server Express instance is typically ".SQLEXPRESS". The INTEGRATED SECURITY keyword value indicates that your Windows login will be used.

Typically, you'd also want your coded connection to tell the application which database you want to use, so you'd also include the DATABASE keyword. The connection string

"SERVER=.SQLEXPRESS;INTEGRATED SECURITY=TRUE;DATABASE=Adventureworks"

connects to the Adventureworks sample database on the local system.

You can also connect to a database on a networked SQL Server or SQL Server Express system. To connect to a networked SQL Server Express system, you specify the computer name followed by the SQL Server Express instance name. The connection string

"SERVER=NetworkedPCSQLEXPRESS;INTEGRATED SECURITY=TRUE; DATABASE=SharedDB"

connects to a SQL Server Express instance on the system named NetworkedPC. Remember that, in order for the remote SQL Server Express system to allow incoming connections, remote connections must be enabled on the remote system.