SQL Server Connection String Examples with PowerShell

By:   |   Updated: 2022-01-04   |   Comments   |   Related: More > Application Development


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

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
'
Variables

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
SQL Authentication

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
Windows Authentication

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
Packet Size

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
Language

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
Error Message in Spanish

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
Application Name

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
Host Name

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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2022-01-04

Comments For This Article





download














get free sql tips
agree to terms