arisng/mssql-cli

SQL Server CLI Querying

Toolchain Note

mssql-cli (Python-based) is deprecated but widely installed. sqlcmd (Go-based, go-sqlcmd) is the maintained successor with near-identical flags. Both are covered here — use whichever is available:

Windows PowerShell:

Get-Command mssql-cli -ErrorAction SilentlyContinue
Get-Command sqlcmd -ErrorAction SilentlyContinue

WSL/Linux:

command -v mssql-cli || command -v sqlcmd

If neither command exists, install sqlcmd first. Only install mssql-cli when a workflow specifically depends on its interactive meta-commands such as \dt or \d.

---

Installation & Verification

1. Install sqlcmd on Windows

Preferred options for the maintained Go-based sqlcmd:

# Windows Package Manager
winget install sqlcmd

# Chocolatey
choco install sqlcmd

If package managers are unavailable, download the latest Windows zip or MSI from the microsoft/go-sqlcmd releases page and place sqlcmd.exe on PATH.

Verify installation:

sqlcmd --version
sqlcmd -?

2. Install sqlcmd in WSL/Linux

For WSL2 or Linux environments that already use Homebrew, the maintained Go-based sqlcmd is the simplest install path:

brew install sqlcmd

If you want the Microsoft ODBC-based sqlcmd, follow the current Microsoft Learn instructions for your distro and install mssql-tools18. The final install and PATH steps are:

sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18 unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc

For Red Hat, SUSE, Ubuntu repo bootstrap, or offline installs, use the current Microsoft Learn mssql-tools18 instructions for your distro before running the final install step above.

Verify installation:

sqlcmd --version
sqlcmd -?

3. Optional: Install legacy mssql-cli

Only do this if you need mssql-cli-specific interactive commands. Prefer sqlcmd for normal query execution and automation.

Windows:

py -m pip install --user mssql-cli

WSL/Linux:

python3 -m pip install --user mssql-cli

Verify installation:

mssql-cli --version

---

Connection String → CLI Flags

mssql-cli and sqlcmd do not accept a full connection string directly. Parse it first using the bundled helper:

python3 scripts/parse_connection_string.py "Server=myserver;Database=mydb;User Id=sa;Password=Pass123;"
# Outputs: -S myserver -d mydb -U sa -P 'Pass123'

Then compose the full command:

CONN=$(python3 /path/to/skill/scripts/parse_connection_string.py "$CONNECTION_STRING")
mssql-cli $CONN -Q "SELECT @@VERSION"

Common connection string formats

| Format | Example | |--------|---------| | ADO.NET | Server=host;Database=db;User Id=u;Password=p; | | ADO.NET (Encrypt) | Server=host;Database=db;User Id=u;Password=p;Encrypt=yes;TrustServerCertificate=yes; | | Azure SQL | Server=tcp:srv.database.windows.net,1433;Initial Catalog=db;User ID=u;Password=p;Encrypt=True; | | ODBC | Driver={ODBC Driver 18 for SQL Server};Server=host;Database=db;UID=u;PWD=p; | | Windows Auth | Server=host;Database=db;Integrated Security=true; |

---

Key Connection Flags

-S   Server host[,port]           e.g. localhost  or  srv.database.windows.net,1433
-d   Database name
-U   Username (SQL Auth)
-P   Password (or set MSSQL_CLI_PASSWORD env var)
-E   Windows Integrated Authentication (skip -U/-P)
-N   Force SSL/TLS encryption
-C   Trust server certificate (required for self-signed certs)
-l   Connect timeout in seconds   e.g. -l 30

---

Executing Queries

Single inline query

mssql-cli -S localhost -d mydb -U sa -P 'Pass123' -Q "SELECT TOP 10 * FROM Orders"

From a SQL file

mssql-cli -S localhost -d mydb -U sa -P 'Pass123' -i migration.sql

Capture output to file

mssql-cli -S localhost -d mydb -U sa -P 'Pass123' -Q "SELECT * FROM sys.tables" -o tables.txt

Suppress welcome/goodbye banner

mssql-cli ... --less-chatty -Q "SELECT 1"

Check exit code (for scripting)

mssql-cli ... -Q "SELECT 1" && echo "OK" || echo "FAILED"

---

Schema Exploration

Via system views (scriptable — preferred)

# List all tables
mssql-cli $CONN -Q "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME"

# Describe a table's columns
mssql-cli $CONN -Q "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Orders' ORDER BY ORDINAL_POSITION"

# List stored procedures
mssql-cli $CONN -Q "SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'"

# Row counts for all tables
mssql-cli $CONN -Q "SELECT t.name, p.rows FROM sys.tables t JOIN sys.partitions p ON t.object_id=p.object_id WHERE p.index_id IN (0,1) ORDER BY p.rows DESC"

Interactive meta-commands (mssql-cli only)

\dt [pattern]    List tables matching pattern
\dv [pattern]    List views
\di [pattern]    List indexes
\df [pattern]    List functions
\dn              List schemas
\d TableName     Describe table (calls sp_help)

---

Azure SQL Specifics

Azure SQL requires encryption and often certificate trust:

mssql-cli -S "tcp:myserver.database.windows.net,1433" -d mydb -U "user@myserver" -P 'pass' -N -C -Q "SELECT @@VERSION"

Or parsed from an Azure connection string:

CONN=$(python3 /path/to/skill/scripts/parse_connection_string.py "$AZURE_CONN_STR")
mssql-cli $CONN -Q "SELECT @@VERSION"

---

Troubleshooting

| Symptom | Fix | |---------|-----| | SSL / certificate error | Add -C (trust server cert) | | Connection refused | Verify port: -S "host,1433" | | Timeout | Add -l 60 | | Password with special chars | Wrap in single quotes or use MSSQL_CLI_PASSWORD env var | | Azure "Login failed" | Use FQDN (server.database.windows.net), add -N -C | | mssql-cli not found | Use sqlcmd; if neither binary exists, install sqlcmd using the platform steps above |

---

sqlcmd (Go) — Drop-in Alternative

The Go-based sqlcmd uses the same core flags (-S, -d, -U, -P, -Q, -i, -o). Use it when mssql-cli is unavailable or for production pipelines:

sqlcmd -S localhost -d mydb -U sa -P 'Pass123' -Q "SELECT @@VERSION"

Additional sqlcmd-specific flags:

-b   Abort batch on error (like sqlcmd classic)
-r   Redirect error messages to stderr

---

Bundled Script

See scripts/parse_connection_string.py — call with a connection string, outputs ready-to-use mssql-cli flags. Handles ADO.NET, ODBC, and Azure SQL string formats.