sqlcmd Examples for SQL Server

sqlcmd is a command line utility for connecting to SQL Server and executing Transact-SQL without a GUI.

It is commonly used for automation, scripting, remote administration, and incident response, especially in environments where SSMS is unavailable, inappropriate, or too heavy.

This post covers sqlcmd usage from a DBA perspective, how to find or install it, common examples, and an important change in newer versions related to encryption and certificates.

---

What Problem This Solves

There are many situations where using SSMS is not ideal:

• Working on Server Core, minimal builds, or containers
• Running scripted checks across servers
• Executing queries from automation pipelines
• Troubleshooting when GUI access is unavailable
• Verifying connectivity and authentication quickly

sqlcmd gives you a fast, reliable way to interact with SQL Server directly from the command line.

---

When a DBA Would Care

DBAs typically reach for sqlcmd when:

• Running checks during incidents
• Automating maintenance or validation tasks
• Testing authentication and connectivity
• Working on jump servers or restricted hosts
• Integrating SQL checks into PowerShell, Bash, or CI pipelines

If you manage production SQL Server environments, sqlcmd should already be in your toolbox.

---

Prerequisites

Windows

If SQL Server Management Studio is installed, sqlcmd is usually already present.

It is installed as part of the SQL Server Client Tools.

If sqlcmd is missing, Microsoft provides a standalone download via the SQL Server command-line utilities documentation.

Linux, macOS, Containers

On non-Windows platforms, sqlcmd is provided via the Microsoft ODBC Driver for SQL Server packages.

You must install:

• sqlcmd
• the Microsoft ODBC Driver for SQL Server

Use the official Microsoft documentation for installation on Linux, macOS, and container images.

---

Version Considerations (Important)

Recent versions of sqlcmd and the Microsoft ODBC Driver introduced stricter encryption defaults.

Key points to be aware of:

• ODBC Driver 18 enables encrypted connections by default
• Certificate validation is enforced by default
• Self-signed or internally issued certificates may cause connection failures
• Older sqlcmd and ODBC Driver versions did not enforce this behaviour

This change commonly surfaces as a certificate trust error when connecting.

---

Finding sqlcmd on Windows

On Windows systems with client tools installed, sqlcmd is typically located under:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\<version>\Tools\Binn\

The version number corresponds to the SQL Server client version installed, for example:

• 130 for SQL Server 2016
• 140 for SQL Server 2017
• 150 for SQL Server 2019
• 180 for newer ODBC Driver 18 installations

If you double-click sqlcmd.exe directly, it will open and close immediately. This is expected behaviour. sqlcmd is a command-line utility and must be run from an existing Command Prompt or PowerShell session.

If the directory is on your system PATH, you can invoke sqlcmd from anywhere. Otherwise, open a terminal and run it from the Tools\Binn directory.

---

Connecting to SQL Server with sqlcmd

Windows Authentication (Local Server)

You can invoke sqlcmd without specifying a server name to connect to the local default instance. Explicitly adding -E makes the authentication method clear.

sqlcmd -E

Basic connection using SQL authentication

sqlcmd -S <server-name> -U <username> -P <password>

Named instance

sqlcmd -S <server-name>\<instance-name> -E

If the instance uses a non-default port, name resolution depends on SQL Server Browser or explicit port configuration.

If connectivity fails, verify:

• TCP is enabled
• The correct port is reachable
• Firewall rules allow traffic

Related posts:

• Enabling TCP Connections in SQL Server
• SQL Server Default Ports

---

Certificate Error on Newer Versions of sqlcmd

On systems using Microsoft ODBC Driver 18 or newer, you may see an error similar to:

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server :
SSL Provider: The certificate chain was issued by an authority that is not trusted.

This happens because newer drivers enforce encrypted connections and require the SQL Server certificate to be trusted by the client.

Nothing is wrong with SQL Server itself. The client behaviour changed.

This is the same underlying issue many users see when upgrading SSMS.

---

Fix: Trust the Server Certificate Explicitly

If you are connecting to an internal SQL Server using a self-signed or privately issued certificate, the quickest fix is to explicitly trust the server certificate.

Add the -C parameter alongside your connection options:

sqlcmd -S <server-name> -E -C

This tells sqlcmd to use encryption but skip certificate chain validation.

This is acceptable for:

• Internal servers
• Non-internet-facing environments
• Lab, test, and controlled production networks

For a deeper explanation of this behaviour and the security trade-offs, see:

SSMS Certificate Chain Not Trusted Error (Trust Server Certificate Fix)

Older versions of sqlcmd and earlier ODBC drivers did not require this parameter, which is why this error often appears after upgrades.

---

Running Queries with sqlcmd

Once connected, you can execute Transact-SQL interactively.

Identify the instance:

SELECT @@SERVICENAME;
GO

Check the connected login:

SELECT SUSER_NAME();
GO

List databases:

SELECT name FROM sys.databases;
GO

Change database context:

USE MyDatabase;
GO

List tables:

SELECT name FROM sys.tables;
GO

The GO keyword is required to terminate batches when using sqlcmd interactively.

---

Executing Queries Non-Interactively

sqlcmd is often used in scripts where no interactive session is needed.

Run a query and exit:

sqlcmd -C -Q "SELECT name FROM sys.databases"

Connecting to a non-local server for queries:

sqlcmd -S <server-name> -E -C -Q "SELECT name FROM sys.databases"

Output results to a file:

sqlcmd -S <server-name> -E -C -Q "SELECT name FROM sys.databases" -o databases.txt

This is commonly used in automation and reporting workflows.

---

Exiting sqlcmd

To exit an interactive sqlcmd session:

EXIT

---

Operational Notes

A few points worth remembering:

• sqlcmd validates connectivity, not application health
• Authentication failures often surface configuration issues quickly
• Named instances and dynamic ports can complicate connectivity
• Explicit ports are more predictable for automation
• Error messages are often clearer than SSMS

For troubleshooting connectivity, pair sqlcmd with:

• Testing Remote Server Port Connectivity with PowerShell
• How to Check Listening Ports on Windows

---

When Not to Use sqlcmd

sqlcmd is not ideal when:

• You need visual query plans
• You are analysing complex result sets
• You need rich debugging or UI tooling

It complements SSMS, it does not replace it.

---

Final Notes

sqlcmd is a small tool with outsized value.

For DBAs, it provides a fast, scriptable, and reliable way to interact with SQL Server in environments where GUIs are unavailable or inappropriate.

With newer drivers enforcing encryption by default, understanding certificate behaviour is now part of using sqlcmd effectively.