Learn about SQL Server Connection String Options with PowerShell

Problem

What are SQL Server Connection Strings and what are some common parameters? What functionality is available for trusted connections with Windows authentication or SQL Server authentication? Can you connect via an IP Address, host name, localhost, ODBC, OLE-DB, or other options? Are there common default values for timeouts, SSL, SQL Server Express, localdb, etc.?

Solution

Database Connection Strings provide information on a data source and how to connect to it. The purpose of a SQL Server Connection String is to tell an application which Microsoft SQL Server and database to connect to, how to authenticate to it, and optionally configure some additional parameters. A connection string consists of a series of ‘keyword = value’ pairs separated by semicolons.

We are going to see some examples of connecting to a SQL Server using the PowerShell Invoke-SqlCmd cmdlet with the -ConnectionString, -Initial Catalog, -Integrated Security, -Packet Size, -Language, -Application Name, -Workstation ID, and -Query switches using it to execute a query that will show the connection value changes.

The Connection ‘String Keyword=Value’ pairs that are separated by semicolons in this format: Key1=Value1; Key2=Value2; Key3=Value3;… that are passed on to the provider.

Invoke-SqlCmd is a good choice for this tip simply because you can use a Connection String with it and it’s easy to show the changes in the same screenshot by querying some built-in system variables and system views.

Declare Script Variables

In order to keep the command line examples cleaner, we are first going to define some PowerShell variables to pass to the Invoke-SqlCmd switches. The variables contain a SQL Server instance name, database name, a SQL authentication login and password, and a query string.

$SqlServer    = 'WIN-MBEN3T4BDIM' # SQL Server instance (HostName\InstanceName for named instance)
$Database     = 'MyDatabase'      # SQL database to connect to 
$SqlAuthLogin = 'test'            # SQL Authentication login
$SqlAuthPw    = 'SuperSecret'     # SQL Authentication login password
# query to show changes
$Query = '
SELECT @@SERVERNAME AS [ServerName]
    , des.login_name
    , DB_NAME()   AS [DatabaseName]
    , dec.net_packet_size
    , @@LANGUAGE  AS [Language]
    , des.program_name
    , des.host_name
FROM sys.dm_exec_connections dec
JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
WHERE dec.session_id = @@SPID
'

Throughout the PowerShell session, the script assigned the variables without error and are valid.

Connect with SQL Authentication

The first example will use the minimum keyword values needed of ‘Data Source’, and a means to authenticate which is SQL authentication with a login and password, and a query to display the values.

# sql authentication without database name
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer$SqlAuthLogin$SqlAuthPw" -Query "$Query"  | Format-Table

The query returns the SQL Server name, database name, default network packet size, calling program name, and host name the script was called from. As you can see, we connected as ‘test’ and are in the default database of master.

Connect with Windows Authentication

In the second and following examples, the ‘User ID’ and ‘Password’ keys have been replaced with ‘Integrated Security=True’ that tells the application to authenticate with the same Windows account the user is logged in as using Windows Authentication. And the ‘Initial Catalog’ keyword was added to point the connection to a particular database. While it’s technically an optional parameter, without it you’ll point to the login’s default database which is likely master.

# more secure windows authentication with current account
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer$Database -Query "$Query" | Format-Table

The Windows account I’m logged into is now the login name. You can also see we are now pointing to MyDatabase.

Change Packet Size Used

The default network size used to communicate with the SQL Server is probably going to be best but in the event you need to change it, it’s easy to do in the Connection String by adding the ‘Packet Size’ keyword with the packet size you want.’ Our example will use 512.

# specify non default packet size
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer$Database -Query "$Query" | Format-Table

The value under net_packet_size is now 512 as opposed to the default of 8000 we saw before.

Change Language Used

SQL Server returns warning and error messages in multiple languages. To change the language, use the ‘Languages’ keyword.

# change language
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer$Database -Query "$Query" | Format-Table

Now the messages will be returned in Spanish, as shown under the Language field.

To illustrate, we’ll replace the $Query variable –Query with a purposely mistyped ‘sp_who’.

Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer</span>;Initial Catalog=$Database</span>; Integrated Security=True; Packet Size=512; Language=Spanish;" -Query "EXEC sp_whp" | Format-Table

Where we would expect to see “Could not find stored procedure sp_whp” the message is now “No se encontrÓ el procedimiento almacenado sp_whp”

Change Program Name

The application name changed in the connections field to identify the connection with the ‘Application Name’ keyword. This can be very help when troubleshooting a query problem on an active server with a lot of connections.

Here we’re calling it MyApp.

# change program name
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer$Database -Query "$Query" | Format-Table

And ‘MyApp’ appears under program_name.

Change Client Workstation Name

It is possible to change the name of the workstation running the program with the ‘Workstation ID’ keyword.

# change client workstation name
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer$Database -Query "$Query" | Format-Table

And the workstation name is now ‘MyComputer’.

Connection String Guidelines

Here are some addition guidelines for working with Connection Strings:

Does not support escape sequences
Uses the value associated with the last occurrence in the event a Keyword=Value pair occurs more than once in the connection string
Keyword=Value pairs are case insensitive i.e., Keyword1=Value1 is the same as keyword1=value1
Ignores blank characters that are not within a value or quotation marks
If a semicolon in part of a value, it must be delimited by full quotation marks
Single quotes are used if a begins with a double quote and vice versa a double quote is used if a value begins with a single quote
An additional equal sign must precede an equal sign in a keyword which indicates its part of the keyword
Preceding or trailing spaces in a Value must be enclosed in single or double quotes

Next Steps

These are some links to more information:

Resources

Community

Subscribe