Learn about SQL Server Connection String Options with PowerShell

By: Joe Gavin | Updated: 2022-01-04 | Comments (1) | Related: > Application Development

Problem

What are SQL Server Connection Strings and what are some common parameters used? 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 default values that are commonly used 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 that are 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 was chosen 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
'

The variables were assigned without error and are valid throughout the PowerShell session.

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; User Id=$SqlAuthLogin; Password =$SqlAuthPw" -Query "$Query"  | Format-Table

The query returns the SQL Server name, database name, default network packet size, the calling program name, and the 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 has been 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;Initial Catalog=$Database; Integrated Security=True;" -Query "$Query" | Format-Table

The login name is now the Windows account I'm logged in as and 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;Initial Catalog=$Database; Integrated Security=True; Packet Size=512" -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

Warning and error messages can be returned from the SQL Server in multiple languages. To change the language, the 'Languages' keyword is used.

# change language
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer;Initial Catalog=$Database; Integrated Security=True; Packet Size=512; Language=Spanish;" -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;Initial Catalog=$Database; 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;Initial Catalog=$Database; Integrated Security=True; Packet Size=512; Language=Spanish; Application Name=My App" -Query "$Query" | Format-Table

And 'MyApp' is shown under program_name.

Change Client Workstation Name

The name of the workstation running the program can be changed with the 'Workstation ID' keyword.

# change client workstation name
Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer;Initial Catalog=$Database; Integrated Security=True; Packet Size=512; Language=Spanish; Application Name=My App; Workstation ID=MyComputer" -Query "$Query" | Format-Table

And the workstation name is now 'MyComputer'.

Connection String Guidelines

Here are some addition guidelines for working with Connection Strings:

Escape sequences are not supported
In the event a Keyword=Value pair occurs more than once in the connection string the value associated with the last occurrence is used
Keyword=Value pairs are case insensitive i.e., Keyword1=Value1 is the same as keyword1=value1
Blank characters that are not within a value or quotation marks are ignored
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:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-01-04

SQL Server Connection String Examples with PowerShell