SQL Server Connection String Examples with PowerShell
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.?
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
These are some links to more information:
- PowerShell and ADO.NET Connection String Settings for SQL Server
- ADO.NET Connection Strings with SQL Server Database Mirroring
- Setup Environment Variables in SQL Server Integration Services
- SQL Server Integration Services Package Configuration
- Understanding SQL Server Connection Pooling in ADO.NET
- SQL Server Reporting Services SSRS 2017 Data Sources
About the author
View all my tips
Article Last Updated: 2022-01-04