PowerShell: Querying Microsoft SQL Server with Invoke SqlCmd

5.4K

To administer Microsoft SQL Server and run T-SQL queries, most developers and administrators use the SQL Server Management Studio (SSMS) GUI or VSCode with extensions.

Why Use PowerShell to Manage Microsoft SQL Server

But in some cases, you need to get or write data to the MSSQL database from the command line or PowerShell script. To run queries against SQL Server databases, you can use the SQLCMD tool or the Invoke SqlCmd cmdlet.

Here’s how to use PowerShell to connect to on-premises Microsoft SQL Server, Azure SQL databases, and run T-SQL queries using different authentication methods.

Prerequisites

Before using Invoke-Sqlcmd, you must ensure that:

• PowerShell 5.1/PowerShell 7 is installed
• The SqlServer PowerShell module is installed
• TCP/IP is enabled on the SQL Server instance
• Firewall rules allow SQL traffic (TCP 1433/custom port)
  Check this with command:
  

  Test-NetConnection ny-sql01 -Port 1433

• The account has sufficient SQL permissions
• SQL Server Browser service is running for named instances (optional)

Installing the SqlServer PowerShell Module

The Invoke-SqlCmd cmdlet is part of the SqlServer module. You can install it from the online PowerShell Gallery using the command:

Install-Module -Name SqlServer –AllowClobber

Checking module installation and available commands

To verify that you have installed the SQL PowerShell module, run the following command:

Get-Module sqlserver

List the commands available in the module:

Import-Module sqlserver

Get-Command -Module SqlServer

Hint. You can install the SqlServer module and use the Invoke-Sqlcmd on MacOS and Linux.

Connecting PowerShell to SQL Server

Now let’s look at how to connect to a local or remote SQL Server instance.

Specify your MSSQL instance name:

$SQLinst ="ny-sql01\SQLEXPRESS"

SQL Server Default Instance vs Named Instance

SQL Server can be installed either as a default instance or as a named instance. A default instance uses only the server name:

ny-sql01

A named instance uses the format:

ServerName\InstanceName

Here is an example:

ny-sql01\SQLEXPRESS

In the example mentioned above ny-sql01 is an SQL Server hostname, and SQLEXPRESS is an SQL Server instance name.

SQL Server Express editions are commonly installed as named instances such as:

• SQLEXPRESS
• SQL2019
• MSSQLSERVERDEV

In case SQL Server is installed as the default instance, you don’t need to specify an instance name in Invoke-Sqlcmd:

Invoke-Sqlcmd -ServerInstance "ny-sql01"

In case SQL Server uses a named instance, you must specify the instance name:

Invoke-Sqlcmd -ServerInstance "ny-sql01\SQLEXPRESS"

You can list all SQL Server instances installed on a computer using:

Set-Location SQLSERVER:\SQL\localhost

Get-ChildItem

Note that one of the most common connection issues in Invoke-Sqlcmd occurs when you specify only the server name while the SQL Server is installed as a named instance.

SQL Server Ports and SQL Browser Service

Keep in mind that by default, the SQL Server default instance uses TCP port 1433. However, named SQL Server instances often use dynamic TCP ports instead of the default 1433 port.

Because of this you may face the following:

• clients may fail to connect in case only port 1433 is opened
• SQL Server Browser service may be required
• firewall rules may need additional config

You can check whether the SQL Server Browser service is running using the command:

Get-Service SQLBrowser

Keep in mind that the SQL Server Browser service listens on UDP port 1434 and helps clients discover named SQL Server instances and their dynamic TCP ports. In case UDP 1434 is blocked by a firewall, connections to named instances may fail even when the SQL Server service itself is running properly.

You can test SQL connectivity to a specific port using the following command:

Test-NetConnection ny-sql01 -Port 1433

In order to identify the TCP port used by a SQL Server instance, you should use SQL Server Configuration Manager/check the SQL Server error log. You can also query the SQL Server registry config from PowerShell, but note that the registry path contains a version- and instance-specific Instance ID. For example:

Get-ItemProperty `
'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

The MSSQL16.MSSQLSERVER portion is specific to SQL Server 2022 and the default instance. You should not use this exact path for other SQL Server versions/named instances.

Also you can do that by checking:

• SQL Server Configuration Manager
• SQL Server ERRORLOG
• SQL Server network config settings

Note. Named SQL instances commonly use dynamic ports unless static TCP ports are configured manually.

Common SQL Connection String Examples

Keep in mind that PowerShell scripts and .NET SQL client libraries commonly use SQL connection strings to define how a connection to SQL Server should be established. Connection strings can specify:

• SQL Server hostname/instance
• TCP port
• authentication method
• encryption settings
• database name
• timeout settings

Scenario	Example Connection String
Windows Authentication	Server=ny-sql01;Database=sales;Integrated Security=True
SQL Authentication	Server=ny-sql01;Database=sales;User ID=sqluser;Password=P@ssw0rd
Named SQL Instance	Server=ny-sql01\SQLEXPRESS;Database=sales;Integrated Security=True
Custom TCP Port	Server=ny-sql01,51433;Database=sales;Integrated Security=True
Azure SQL Database	Server=tcp:myserver.database.windows.net,1433;Initial Catalog=sales;Encrypt=True;TrustServerCertificate=False

Note that SQL Server connection strings support both default instances and named instances. In case SQL Server listens on a custom TCP port, you can specify the port using the format:

ServerName,Port

Important. You should avoid hardcoding usernames and passwords directly inside PowerShell scripts whenever possible. We recommend you to use Windows Authentication, Microsoft Entra ID authentication, managed identities, or secure secret vault solutions.

Important Security Recommendations

Note that you should avoid storing SQL passwords directly inside PowerShell scripts in plain text.

Instead you should:

• Use Get-Credential
• Store credentials securely in SecretManagement/Vault solutions
• Prefer Windows Authentication whenever possible
• Limit SQL login permissions to the minimum required level

Connecting to a remote SQL Server with credentials

Request credentials to connect to SQL Server. You can authenticate using either Windows Authentication/SQL Server Authentication, depending on how the SQL instance is configured:

$cred = Get-Credential

Using Windows Authentication vs SQL Authentication

Microsoft SQL Server supports multiple authentication methods when connecting from PowerShell.

The 2 most common authentication types are:

Authentication Type	Description
Windows Authentication	Uses the current Windows user or domain credentials
SQL Authentication	Uses SQL Server logins stored inside SQL Server

Windows Authentication is generally recommended in enterprise environments because it supports:

• AD integration
• Kerberos authentication
• Centralized account management
• Password policies
• MFA integrations through Microsoft Entra ID hybrid scenarios

Here is an example using Windows Authentication:

Invoke-Sqlcmd -ServerInstance $SQLinst `
-Database sales `
-Query "SELECT @@SERVERNAME"

And here is an example using SQL Authentication:

$cred = Get-Credential

Invoke-Sqlcmd -ServerInstance $SQLinst `
-Credential $cred `
-Database sales `
-Query "SELECT @@VERSION"

Note that SQL Authentication is commonly used for:

• Legacy apps
• Cross-platform scripts
• External systems
• Environments without AD trust

Using PowerShell with Azure SQL and Microsoft Entra ID

Keep in mind that modern environments increasingly use Azure SQL Database and Microsoft Entra ID authentication instead of traditional SQL logins. Advantages of Microsoft Entra ID authentication include:

• Multi-factor authentication (MFA)
• Passwordless authentication
• Conditional Access policies
• Centralized identity management
• Reduced password exposure

When using Azure SQL, you should authenticate using:

• Interactive Microsoft Entra ID login
• Managed identities
• Service principals
• Access tokens

Here is an example of using an Azure access token:

$token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token

Invoke-Sqlcmd `
-ServerInstance "myserver.database.windows.net" `
-Database "sales" `
-AccessToken $token `
-Query "SELECT TOP 10 * FROM Customers"

Notes.

The Az.Accounts PowerShell module is required to obtain Azure access tokens.

Keep in mind that MFA-enabled interactive logins may not work correctly in fully unattended PowerShell scripts. In automation cases, we recommend you to prefer managed identities, service principals, or secure token-based authentication methods.

Important. In modern enterprise environments, we recommend you to use Microsoft Entra ID authentication instead of SQL logins whenever possible.

In order to connect to a Microsoft SQL instance and to list the database, run the following command:

Get-SqlDatabase -ServerInstance $SQLinst -Credential $cred

Now, using Invoke-SqlCmd, you can connect to any database and run a simple SQL query:

Invoke-Sqlcmd -ServerInstance $SQLinst -Credential $cred -Database sales -Query "SELECT * FROM sales.customers"

Note that in case you omit the -Credential parameter, Invoke-Sqlcmd uses Windows Authentication with the current user context.

Important. You should avoid using SELECT * in production automation scripts whenever possible. Instead, you need to explicitly specify only required columns:

• SELECT CustomerID, CustomerName, City
• FROM sales.customers

This will help you to improve:

• query performance
• network usage
• script readability
• compatibility with schema changes

Notes

Supported commands are T-SQL statements and the XQuery subset.
You can use the -Password option to connect to SQL Server using a clear text password.

Invoke-SqlCmd Troubleshooting

An error may occur when connecting to the database:

Invoke-Sqlcmd: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

In this case, you will need to add the -TrustServerCertificate parameter to the Invoke-Sqlcmd command.

Note that modern SQL Server builds increasingly require encrypted connections by default. In some environments, you may need to explicitly config encryption settings:

Invoke-Sqlcmd `
-ServerInstance $SQLinst `
-Encrypt Optional `
-TrustServerCertificate

Important. Keep in mind that using -TrustServerCertificate bypasses certificate validation and you should use it only in trusted/internal environments.

For production environments, we recommend you to use properly trusted TLS certificates.

Configuring Query Timeout in Invoke-Sqlcmd

Keep in mind that long-running SQL queries may fail because of default timeout settings. You can increase the query timeout value using the -QueryTimeout parameter:

Invoke-Sqlcmd `
-ServerInstance $SQLinst `
-Database sales `
-Query "EXEC GenerateMonthlyReport" `
-QueryTimeout 300

In the example above, PowerShell waits up to 300 seconds for the query to complete. This is commonly required for:

• reporting queries
• maintenance tasks
• index rebuild operations
• data import/export jobs
• large enterprise databases

Note. In addition to -QueryTimeout, you can control the connection timeout using -ConnectionTimeout (default is 15 seconds). This can be very useful when connecting to remote SQL servers over high-latency links.

Exporting Query Results and Formatting Output

Export SQL results to CSV file

You can export SQL table query results to a CSV file. To do that, add the following pipe to the command ((| Export-Csv -Path C:\VSCode\SqlResult.csv -Delimiter “;”), or view the results in a graphic GridView table (|Out-GridView).

The SQL connection parameters and the database name can be stored in a hash table so that they do not have to be specified in the command each time:

$params = @{Credential=$cred; Database='sales'; ServerInstance=$SQLinst}

In order to execute an SQL query, you now need to run:

$query = "SELECT * FROM sales"

Invoke-Sqlcmd @params -Query $query | ft

Running Multi-Line SQL Queries in PowerShell

For longer or more complex SQL queries, we recommend you to use more convenient PowerShell here-strings. For example:

$query = @"
SELECT
CustomerID,
CustomerName,
City
FROM sales.customers
WHERE City = 'New York'
ORDER BY CustomerName
"@

Invoke-Sqlcmd @params -Query $query

Using here-strings improves readability, query maintenance, formatting and script troubleshooting.

Note that this approach is commonly used in enterprise automation scripts and reporting solutions.

By default, Invoke-Sqlcmd returns a series of System.Data.DataRow type rows. You can view this format as a PowerShell object as follows:

$table= Invoke-Sqlcmd @params -Query $query

write-host ($table | Format-Table | Out-String)

In case your SQL query is stored in a text file, you can run it using the -InputFile parameter:

Invoke-Sqlcmd @params -InputFile "C:\VSCode\MySqlScript.sql"

Keep in mind that you can run not only SELECT queries, but also other types of queries.

Running SQL Queries Against Multiple SQL Servers

Note that you can automate SQL admin tasks across multiple SQL Server instances. For example:

$Servers = @(
"sql01",
"sql02",
"sql03"
)

foreach ($Server in $Servers) {

Invoke-Sqlcmd `
-ServerInstance $Server `
-Database master `
-Query "SELECT @@SERVERNAME"
}

The approach mentioned above is commonly used for:

• inventory collection
• health checks
• compliance reporting
• DBA automation
• enterprise SQL maintenance tasks

Avoid SQL Injection in PowerShell Queries

When building SQL queries dynamically in PowerShell, you should avoid concatenating user input directly into query strings.

The approach below is unsafe example, it may allow SQL injection attacks in case variables contain malicious SQL code:

$query = "INSERT INTO resellers (Name, City, email)
VALUES('$name', '$city', '$email')"

Keep in mind that SQL injection vulnerabilities may allow attackers to:

• Read sensitive database data
• Modify/delete records
• Execute unauthorized SQL commands
• Escalate privileges in the database

Note that this is especially dangerous when variables originate from:

• User input
• CSV files
• Web forms
• API requests
• External scripts

Important to mention that safer approaches include input validation, stored procedures, or parameterized queries through .NET SQL client libraries.

Here is an example of using SQL parameters with System.Data.SqlClient:

$connectionString = "Server=ny-sql01\SQLEXPRESS;Database=sales;Integrated Security=True"

$sql = @"
INSERT INTO resellers (Name, City, Email)
VALUES (@Name, @City, @Email)
"@

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = $sql

$command.Parameters.Add("@Name",[System.Data.SqlDbType]::NVarChar,40).Value = $name
$command.Parameters.Add("@City",[System.Data.SqlDbType]::NVarChar,30).Value = $city
$command.Parameters.Add("@Email",[System.Data.SqlDbType]::NVarChar,50).Value = $email

$command.ExecuteNonQuery()

$connection.Close()

The method above prevents SQL injection because user input is passed separately from the SQL command text.

Important. The Invoke-SqlCmd cmdlet itself does not support true parameterized queries in the same way as .NET SQL client libraries. In production automation scripts, we recommend you to use parameterized SqlCommand objects whenever SQL queries contain dynamic input values.