Using the ADO.NET Connection String

Most of my Windows PowerShell scripts use SQL Server Management Objects (SMO) to access SQL Server properties to find out their current state or other information pertinent to the role of an operational DBA. With that said, I frequently need to access data. The SQLPS module provides the Invoke-Sqlcmd cmdlet, which makes accessing data fairly easy.

There are many times, however, when I use ADO.NET to access data. If I'm building a script that will run as a regular scheduled job, it's easier to just do the work using ADO.NET, especially if I'm giving that script to users who don't know they have to load the SQLPS module before they can use the Invoke-SQLCMD cmdlet.

When I want to connect to a database using ADO.NET in a PowerShell script, I create a SqlConnection object. The property that's most critical in that object is the ConnectionString property. The connection string consists of a set of key-value pairs separated by semicolons. It contains all the necessary information for a script to successfully connect to a SQL Server database.

Creating a Connection String

To create a connection string, you first need to create a key-value pair that provides the necessary server information. The keyword can be Server, Data Source, Address, Addr, or Network Address. The value can be a server name, instance name, or address. If you're connecting through a non-standard port number, you also need to specify the port number after a comma. Here are some examples:

Data Source=WS12SQL;
Server=SQLTBWSINST01;
Server=10.1.1.118,10291;
Server=tcp:dijewiq84u.database.windows.net;

The last example connects to a Windows Azure SQL database. Prefacing the value with tcp: ensures that TCP/IP will be used for that connection.

If you want to connect to the server on which you're running the script, you can use (local) as the value. Similarly, if you want to connect to the local server using TCP/IP, you can use localhost as the value.

At this point, you need to create a key-value pair that specifies the database to which you want to connect. The keyword can be either Database or Initial Catalog, with the name of the database as the value.

The next, and most critical, key-value pair specifies the security context you want to use when connecting to the server. The keyword can be either Integrated Security or Trusted_Connection. The possible values are SSPI, True, False, Yes, and No. I tend to use SSPI because it means that Windows Authentication with Kerberos (under the current security context) will be used for the connection. It appears that specifying Yes or True produces the same result as specifying SSPI, although the documentation doesn't explicitly mention this. If you specify False or No, you need to add credentials to gain access to your server.

When you don't use Windows Authentication, you need to supply your credentials using the User ID and Password (or Pwd) keywords, with the proper value for each one. This is where things can get tricky, as hard-coding user IDs and passwords in scripts isn't a good idea from a security perspective. There are measures you can take to mitigate the risk, such as passing in SecureString values by means of a variable, but they aren't as secure as Windows Authentication.

Another good idea is to include the key-value pair of Encrypt=True; in the connection string. For example, to connect to an Azure SQL database, the connection string might look like:

Server=tcp:dijewiq84u.database.windows.net;Database=Northwind;
User ID=DBAdmin@$azsvnm;Password=P@ssw0rd1;
Trusted_Connection=False;Encrypt=True;

When you include this key-value pair, your connection string will be encrypted as it's being sent to the server if the server has a certificate installed. Using this approach is highly recommended because it avoids the possibility of some network sniffing process picking up your access information.

Another key-value pair I find very useful has the Application Name keyword. The value is the name of the application or script making the connection. Including this key-value pair makes it much easier to troubleshoot any connection problems that might come up when several applications share a server or multiple scripts are run on a server. In addition, anyone looking at SQL Server sessions will know what the script is doing.

Using the Connection String

After you build the connection string, you can put it in a string variable and pass it in as a parameter when you create the SqlConnection object, like this:

$cstr = "Server=WS12SQL;Database=Northwind;Integrated Security=SSPI;ApplicationName=SetupNorthwind";$cn = new-object system.data.SqlClient.SqlConnection($cstr)$nwddl = Get-Content .NorthwindDDL.sql$cmd = New-Object System.Data.SqlClient.SqlCommand($nwddl,$cn)$cn.Open()$cmd.ExecuteNonQuery()$cn.Close()

In this example, I'm connecting to the existing Northwind database on server WS12SQL using Windows Authentication. Once the connection is established, I load the NorthwindDDL.sql script (which is designed to add tables to a database) into the current directory and run the script against the Northwind database. In this code, note the "late open" and "early close" of the connection. This method helps ensure that a script will work even after a failover of the server in a failover cluster or an AlwaysOn availability group.

Define Your Access

Connection strings define your access to SQL Server. You can find more details about them on the MSDN SqlConnection.ConnectionString Property web page. In addition, the Connection Strings Reference website is a great resource for sample connection strings.