sqlcmd & go-sqlcmd

Status: Reviewed
Stack: PowerShell → SQL / stored procs → Blazor
Depends on: none (foundation)
Goal: Training expansion of the original lean deep-dive — choose and use sqlcmd vs go-sqlcmd vs Invoke-Sqlcmd correctly, then run the labs and recipes until they are muscle memory for Rick’s AD/Windows/PowerShell → stored procs → Blazor Server loop.

Cross-links (do not duplicate): SQL-CLI-03 Invoke-Sqlcmd / SqlServer module · SQL-CLI-06 auth cheat sheet · SQL-CLI-07 PS→SQL→Blazor patterns

This page owns the CLI client: install, which binary is on PATH, flags, Entra switches, -b / $LASTEXITCODE, :setvar / :r, and paste-ready recipes. Object-shaping lives in CLI-03. Kerberos/double-hop and the full auth matrix live in CLI-06. End-to-end Collect→Stage→Load→EXEC→Blazor orchestration lives in CLI-07.

Learning outcomes

After this page you can:

• Pick sqlcmd (ODBC), go-sqlcmd, or Invoke-Sqlcmd for a given job without guessing.
• Install both Windows clients, prove which binary PATH will run, and pin a full path in Agent jobs.
• Connect with Windows Integrated, SQL auth, and Entra ID (interactive / default / SP / MI) using documented flags only.
• Decide when -C / -N / -F are required (lab self-signed vs prod certs; SQL Server 2025 encrypt default).
• Drive deploy and ops scripts with -i / -Q / -o, -v / :setvar, GO batches, and :r includes.
• Treat $LASTEXITCODE as a contract — -b always, check immediately, throw on non-zero.
• Avoid the usual silent-poison cases: encoding, GO inside -Q, password on argv, sqlcmd vars vs PowerShell $().
• Paste any of the recipes below on a jump box and get a real result.

1. Decision map (three tools)

Microsoft ships two sqlcmd variants that share a name:

Invoke-Sqlcmd is not either of those. It is a cmdlet in the SqlServer Gallery module. It does not shell out to sqlcmd.exe. Details: SQL-CLI-03.

Rick’s stack default

On-prem ops / deploy / Agent / loop servers   → sqlcmd or go-sqlcmd  +  -b  +  $LASTEXITCODE
Need DataRows → JSON / Blazor shaping         → Invoke-Sqlcmd                  (CLI-03)
Azure / Entra-first CLI                       → go-sqlcmd  -G  or  --authentication-method
Legacy Windows-only Agent PATH                → classic ODBC sqlcmd; do not swap PATH blind
Kerberos / double-hop / Encrypt matrix        → CLI-06 (this page only shows sqlcmd flags)
Collect → Stage → Load → EXEC → Blazor        → CLI-07 (this page only shows the sqlcmd hop)

Rule of thumb: if the next consumer is a file, a log, or a SQL Agent jobstep, use the exe. If the next consumer is a PowerShell object that becomes JSON for Blazor, use Invoke-Sqlcmd.

Identify which binary PATH will run

Get-Command sqlcmd | Format-List Source, Path, Version
where.exe sqlcmd
sqlcmd "-?"          # Learn-recommended; works in PS (quote the ?)
sqlcmd --version     # go-sqlcmd only — ODBC will error
sqlcmd --help        # go-sqlcmd modern subcommands

Learn note: installing go-sqlcmd via a package manager puts Go ahead of ODBC on PATH. ODBC is not uninstalled — call it by full path. Close and reopen the shell after install.

2. Install lab (Windows)

Do this on the jump box (or a throwaway VM) before you trust any script.

2.1 Classic sqlcmd (ODBC)

Typical locations (version folders vary):

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE
C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\180\Tools\Binn\SQLCMD.EXE

Verify:

where.exe sqlcmd
sqlcmd "-?"
# Expect: Microsoft (R) SQL Server Command Line Tool  /  Version 15+ (Learn: have at least 15.0.4298.1)
# Entra (-G) and Always Encrypted (-g) need ≥ 13.1; interactive Entra needs ODBC sqlcmd ≥ 15.0.1000.34 + ODBC Driver ≥ 17.2

OS floor from Learn: Windows 10 / Windows Server 2016 or later.

2.2 go-sqlcmd (official as of 2026)

Microsoft documents two variants. Go install paths on Windows:

Default Go install directory: C:\Program Files\sqlcmd.

Not official: dotnet tool install for sqlcmd. That path is SqlPackage, not go-sqlcmd.

Caveat from Learn: package managers may lag; versions newer than 1.6 might not be in every feed. GitHub latest at research time is v1.10.0 (2026-03-03). If you need that build (or a specific older one), download the MSI/zip.

winget install sqlcmd
# new session, then:
sqlcmd --version          # expect Version: 1.x.x  (want ≥ 1.0.0; prefer current 1.10.x)
sqlcmd --help             # modern commands: query, config, create, open, …
sqlcmd "-?"               # ODBC-compatible flag list

Linux / macOS exist (apt/yum/brew install sqlcmd, tarballs) but this lab is Windows-first. Cloud Shell ships a sqlcmd already.

2.3 PATH discipline (do this once)

# After winget/choco, reopen the host. Then:
where.exe sqlcmd
# If you still need ODBC in Agent:
Get-ChildItem 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\*\Tools\Binn\SQLCMD.EXE'

2.4 Invoke-Sqlcmd (pointer only)

Install-Module -Name SqlServer -Scope CurrentUser
Import-Module SqlServer          # not the legacy SQLPS module
Get-Command Invoke-Sqlcmd | Format-List Source, Version

SSMS 17+ no longer ships the module. CurrentUser vs AllUsers, #NOSQLPS in Agent, Encrypt flags: SQL-CLI-03.

3. Auth lab

Flags here. The decision matrix (when Integrated vs SQL vs Entra, plus Kerberos/double-hop, Encrypt across every tool) is SQL-CLI-06. Do not copy that sheet into scripts — link it.

3.1 Windows Integrated

sqlcmd -S sql01.contoso.local -d AppDb -E -Q "SELECT SUSER_SNAME(), HOST_NAME();"

-E = trusted connection. Default on Windows when -U / -P are omitted — you can leave -E off, but scripts should keep it so the intent is obvious.

Do not mix -E with -U / -P. Do not mix -E with -G. Either combination errors.

3.2 SQL auth

# Prefer env var over -P on the command line (visible in process lists / PSHistory / Agent logs)
$env:SQLCMDPASSWORD = '***'      # scrub after; never commit
sqlcmd -S sql01 -d AppDb -U deploy_user -Q "SELECT 1"
Remove-Item Env:SQLCMDPASSWORD

If -U is set and neither -P nor SQLCMDPASSWORD is set, sqlcmd prompts. Fine interactively; fatal in Agent.

SQLCMDPASSWORD wins over the legacy OSQLPASSWORD. Same pattern for SQLCMDUSER / SQLCMDSERVER.

3.3 Entra ID / Azure AD

Azure AD still appears in flags and error text. Microsoft Entra ID is the current name. They are the same thing here.

go-sqlcmd — two switches

-G is mostly ODBC-compatible:

--authentication-method is explicit (Learn, sql-server-ver17):

:Connect on go-sqlcmd accepts the same method names as an optional -G parameter (SqlAuthentication, ActiveDirectoryDefault, …).

SQLCMDCLIENTID (go-sqlcmd): app registration id for ActiveDirectoryInteractive / ActiveDirectoryPassword.

ODBC sqlcmd — -G only

ODBC -G requires sqlcmd ≥ 13.1. Interactive needs sqlcmd ≥ 15.0.1000.34 and ODBC Driver ≥ 17.2. Interactive is not supported on Linux/macOS for ODBC. -G applies to Azure SQL Database and Azure Synapse. -A (DAC) is not supported with -G.

# go-sqlcmd — DefaultAzureCredential (az login / MI / env)
sqlcmd -S myserver.database.windows.net -d AppDb -G -l 30 -Q "SELECT SUSER_SNAME()"

# Explicit method — service principal (secrets from env, not argv, on shared hosts)
sqlcmd -S myserver.database.windows.net -d AppDb -l 30 `
  --authentication-method ActiveDirectoryServicePrincipal `
  -U $env:AZURE_CLIENT_ID -P $env:AZURE_CLIENT_SECRET -Q "SELECT 1"

# ODBC interactive — -G BEFORE -U
sqlcmd -S myserver.database.windows.net -d AppDb -G -U 'rick@contoso.com' -l 30

Azure login timeout: Learn recommends -l 30 or higher with -G.

The Entra principal must exist in the database (CREATE USER [name] FROM EXTERNAL PROVIDER). That is a DBA step, not a flag.

3.4 TrustServerCertificate / encrypt

Breaking change (SQL Server 2025 / current tools): if you omit -N, the default is -Nm (mandatory). SQL Server 2022 and earlier defaulted to optional (-No). Lab boxes with self-signed certs usually need -C (and often an explicit -N). Prefer real certs in prod; -C is a deliberate ops choice.

go-sqlcmd encrypt negotiation (Learn):

• No -N and no -C → negotiate without validating the server cert.
• -N without -C → require cert validation.
• Both → use the values you passed.

SQL Server 2025 also introduces TDS 8.0 support in sqlcmd (-N s / strict).

-J (server certificate file) is ODBC Linux/macOS in Learn’s option table; go-sqlcmd has been adding cert-pinning — verify with sqlcmd "-?" on your build before you script it.

Full Encrypt / TSC / Strict matrix across sqlcmd, bcp, Invoke-Sqlcmd, sqlpackage: CLI-06.

3.5 Kerberos / double-hop (pointer)

Windows Integrated from workstation → jump host → SQL fails the second hop unless constrained delegation / CredSSP / gMSA is in place.

For Blazor Server app pools: run the site as a domain identity that has direct SQL rights, or use SQL auth / Entra. Do not hop the end-user’s Windows token IIS → SQL without constrained delegation.

Full table, klist checks, gMSA vs CredSSP, app-pool identity: SQL-CLI-06 §2 / §8. Stop here.

4. Flag reference (practical, grouped)

Only flags you will actually type. “Why in scripts” is the point. Full syntax: sqlcmd utility (Learn, ver17).

Connection

Query, input, output

Errors and timeouts

Format / crude export

Hardening and variables

go-sqlcmd extras (not on ODBC)

go-sqlcmd compatibility (do not be surprised)

5. Scripting workshop (PS orchestration)

The full Collect→Stage→Load→EXEC→Blazor pipeline is SQL-CLI-07. This section is the sqlcmd hop only: how PowerShell should call the exe so Blazor is not fed a silent failure.

5.1 -Q vs -i vs -o

sqlcmd -S $Server -d $Db -E -b -V 11 -i .\Deploy\Procs.sql -o .\logs\deploy.log
if ($LASTEXITCODE -ne 0) {
    throw "sqlcmd failed with exit $LASTEXITCODE — see deploy.log"
}

Without -b, many SQL errors still exit 0. That silently poisons Blazor data loads.

5.2 Variable substitution (-v / :setvar)

Precedence, low → high (Learn): system env → user env → shell SET / $env: → sqlcmd -v → :setvar.

sqlcmd -S $Server -d $Db -E -b `
  -v Environment="Prod" AppSchema="dbo" `
  -i .\Scripts\Seed.sql

-- Seed.sql
PRINT 'Env=$(Environment)';
INSERT INTO $(AppSchema).Config(Env) VALUES (N'$(Environment)');
GO

Rules that bite:

• Names: no whitespace, no quotes, not shaped like $(var).
• Case-insensitive.
• :setvar VarName with no value clears the variable.
• Quote values that contain spaces: -v OutDir="C:\temp\run1".
• -v is Windows-only on ODBC. On Linux, prepend a :setvar file (or use go-sqlcmd and verify -v on that build).
• PowerShell will expand $(Environment) inside double-quoted -Q strings. Use a single-quoted here-string when the $( ) is for sqlcmd, not for PS.

5.3 GO batches and :r includes

GO is a client batch separator, not T-SQL. The engine never sees it.

-- MasterDeploy.sql
:r .\Procs\usp_GetOrders.sql
:r .\Procs\usp_UpsertCustomer.sql
GO

Set-Location $PSScriptRoot          # :r paths are relative to process CWD, not the master file
sqlcmd -S $Server -d $Db -E -b -i .\MasterDeploy.sql

• Cannot put GO inside IF / a stored-proc body / a -Q string.
• Variables declared in T-SQL reset across GO — re-declare.
• :r is expanded by the client, then the combined text is batched on GO.

5.4 Capturing output for Blazor / files

# Crude CSV (good enough for a drop file that bcp or a proc will reload)
sqlcmd -S $Server -d $Db -E -b -W -s"," -h-1 `
  -Q "SET NOCOUNT ON; SELECT OrderId, CustomerId, Total FROM dbo.Orders WHERE ModifiedUtc >= '2026-01-01';" `
  -o .\out\orders.csv

SET NOCOUNT ON first — (N rows affected) breaks parsers.

For typed objects / real CSV / JSON that a Blazor service will deserialize, stop using sqlcmd and switch to Invoke-Sqlcmd → Export-Csv -Encoding utf8 / ConvertTo-Json.

Capturing stdout in PS without -o:

$raw = & sqlcmd -S $Server -d $Db -E -b -Q "SET NOCOUNT ON; SELECT @@SERVERNAME;"
if ($LASTEXITCODE -ne 0) { throw "sqlcmd exit $LASTEXITCODE" }
# $raw is text. Do not pretend it is a DataRow.

Check $LASTEXITCODE immediately. Any later native command overwrites it.

5.5 Quoting pitfalls (PowerShell)

$query = @'
SET NOCOUNT ON;
EXEC dbo.usp_GetOrders @Since = '2026-01-01';
'@
sqlcmd -S $Server -d $Db -E -b -Q $query
if ($LASTEXITCODE -ne 0) { throw "sqlcmd exit $LASTEXITCODE" }

5.6 When Invoke-Sqlcmd wins

$rows = Invoke-Sqlcmd -ServerInstance $Server -Database $Db -TrustServerCertificate `
  -Query "EXEC dbo.usp_GetOrders @Since = '2026-01-01'" -QueryTimeout 60
$payload = $rows | Select-Object OrderId, CustomerId, Total
$payload | ConvertTo-Json -Compress | Set-Content .\out\orders.json -Encoding utf8

Use it when you need objects, not log text. Parameter styles differ by module version — CLI-03. Do not read $LASTEXITCODE after Invoke-Sqlcmd; it is not the SQL result.

6. Exit codes and error handling

This is the discipline that keeps Blazor from rendering yesterday’s data as if the load succeeded. CLI-07 repeats it for the whole pipeline; here it is sqlcmd-specific.

What -b actually does (Learn)

• sqlcmd exits and sets ERRORLEVEL / $LASTEXITCODE when an error occurs.
• Value is 1 when the SQL error severity is greater than 10; otherwise 0.
• Severity 10 (informational) is not an error.
• -V raises the bar: sqlcmd does not treat a message as an error if its severity is below -V.
• Script problems (bad comment, syntax, missing scripting variable) also return 1.
• ERRORLEVEL is not @@ERROR. Do not compare it to a SQL error number.

Recommended pair in this stack: -b -V 11. Use -V 16 when you only want “correctable general errors” to fail the job (Learn best practice).

PowerShell contract

sqlcmd -S $Server -d $Db -E -b -V 11 -Q "SET NOCOUNT ON; EXEC dbo.usp_Inventory_LoadFromStaging;"
if ($LASTEXITCODE -ne 0) { throw "sqlcmd failed exit=$LASTEXITCODE" }

Wrapper you can drop into a profile / common.ps1 — see Recipe 7.

Agent CmdExec: the jobstep already surfaces ERRORLEVEL. Still pass -b. Still log -o.

7. Gotchas

8. Recipes (paste and run)

Placeholders: sql01 / AppDb / myserver.database.windows.net. Swap in yours. Every exe recipe uses -b.

1) Quick query (integrated)

sqlcmd -S sql01 -d AppDb -E -b -Q "SET NOCOUNT ON; SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME();"
if ($LASTEXITCODE -ne 0) { throw "sqlcmd exit $LASTEXITCODE" }

2) Run a .sql file with abort-on-error

sqlcmd -S sql01 -d AppDb -E -b -V 11 -i .\Deploy\usp_GetOrders.sql -o .\logs\usp_GetOrders.log
if ($LASTEXITCODE) { throw "Deploy failed: $LASTEXITCODE" }

3) Export crude CSV (sqlcmd)

sqlcmd -S sql01 -d AppDb -E -b -W -s"," -h-1 -o .\out\customers.csv `
  -Q "SET NOCOUNT ON; SELECT CustomerId, Name, Email FROM dbo.Customer;"
if ($LASTEXITCODE) { throw "Export failed: $LASTEXITCODE" }

Need real CSV / UTF-8 / typed columns? CLI-03 Invoke-Sqlcmd | Export-Csv.

4) Loop servers from a list

Get-Content .\servers.txt | ForEach-Object {
    $s = $_.Trim()
    if (-not $s -or $s.StartsWith('#')) { return }
    Write-Host "=== $s ===" -ForegroundColor Cyan
    sqlcmd -S $s -d master -E -b -Q "SET NOCOUNT ON; SELECT @@SERVERNAME AS Srv, @@VERSION AS Ver;"
    if ($LASTEXITCODE) { Write-Warning "$s failed ($LASTEXITCODE)" }
}

5) Entra auth (go-sqlcmd)

# Interactive / MFA
sqlcmd -S myserver.database.windows.net -d AppDb -G -U 'rick@contoso.com' -l 30 `
  -Q "SET NOCOUNT ON; SELECT SUSER_SNAME();"

# Service principal (secrets from env)
sqlcmd -S myserver.database.windows.net -d AppDb -l 30 `
  --authentication-method ActiveDirectoryServicePrincipal `
  -U $env:AZURE_CLIENT_ID -P $env:AZURE_CLIENT_SECRET `
  -Q "SET NOCOUNT ON; SELECT SUSER_SNAME();"

6) Managed identity (go-sqlcmd, Azure host)

# System-assigned MI
sqlcmd -S myserver.database.windows.net -d AppDb -l 30 `
  --authentication-method ActiveDirectoryManagedIdentity `
  -Q "SET NOCOUNT ON; SELECT SUSER_SNAME();"

# User-assigned MI — -U is the client id
sqlcmd -S myserver.database.windows.net -d AppDb -l 30 `
  --authentication-method ActiveDirectoryManagedIdentity `
  -U $env:AZURE_CLIENT_ID `
  -Q "SET NOCOUNT ON; SELECT SUSER_SNAME();"

7) Exit-code check wrapper

function Invoke-SqlcmdExe {
    param(
        [Parameter(Mandatory)][string]$Server,
        [Parameter(Mandatory)][string]$Database,
        [string]$InputFile,
        [string]$Query,
        [string]$OutputFile,
        [hashtable]$Variables
    )
    $cmdArgs = @('-S', $Server, '-d', $Database, '-E', '-b', '-V', '11')
    if ($InputFile)  { $cmdArgs += @('-i', $InputFile) }
    if ($Query)      { $cmdArgs += @('-Q', $Query) }
    if ($OutputFile) { $cmdArgs += @('-o', $OutputFile) }
    foreach ($k in $Variables.Keys) { $cmdArgs += @('-v', "$k=$($Variables[$k])") }

    & sqlcmd @cmdArgs
    if ($LASTEXITCODE -ne 0) {
        throw "sqlcmd exit $LASTEXITCODE (Server=$Server Db=$Database)"
    }
}

# Usage
Invoke-SqlcmdExe -Server sql01 -Database AppDb -InputFile .\Deploy\Procs.sql `
  -Variables @{ Environment = 'Prod' }

8) Trust cert + TCP pin (lab / broken PKI)

sqlcmd -S "tcp:sql01.contoso.local,1433" -d AppDb -E -C -b `
  -Q "SET NOCOUNT ON; SELECT 1 AS Ok;"

9) :setvar deploy (same script, many environments)

sqlcmd -S sql01 -d AppDb -E -b -V 11 `
  -v Environment="Prod" AppSchema="dbo" TargetTable="Config" `
  -i .\Scripts\Seed.sql -o .\logs\seed-prod.log
if ($LASTEXITCODE) { throw "Seed failed: $LASTEXITCODE" }

-- Scripts\Seed.sql
PRINT 'Deploy $(Environment) → $(AppSchema).$(TargetTable)';
INSERT INTO $(AppSchema).$(TargetTable)(Env) VALUES (N'$(Environment)');
GO

10) Multi-file run (:r master)

Set-Location $PSScriptRoot\Deploy
sqlcmd -S sql01 -d AppDb -E -b -V 11 -i .\MasterDeploy.sql -o .\..\logs\master.log
if ($LASTEXITCODE) { throw "MasterDeploy failed: $LASTEXITCODE" }

-- Deploy\MasterDeploy.sql
:r .\Procs\usp_GetOrders.sql
:r .\Procs\usp_UpsertCustomer.sql
:r .\Procs\usp_Inventory_LoadFromStaging.sql
GO

11) Invoke-Sqlcmd when it wins (objects → Blazor JSON)

Import-Module SqlServer
$rows = Invoke-Sqlcmd -ServerInstance 'sql01' -Database 'AppDb' -TrustServerCertificate `
  -Query 'EXEC dbo.usp_GetOrders @Days = 7' -QueryTimeout 60
$rows | ConvertTo-Json -Depth 4 | Set-Content .\out\orders.json -Encoding utf8

Full cmdlet surface: SQL-CLI-03.

12) DAC / emergency console (on-prem, Windows)

# Dedicated admin connection — engine must allow DAC. Not valid with -G.
sqlcmd -S sql01 -A -E
# then T-SQL interactively; quit with :EXIT

What’s next

Need objects in PowerShell / Blazor JSON?  → Invoke-Sqlcmd          (CLI-03)
Need Azure Entra (MI / SP / MFA) from CLI? → go-sqlcmd
Need Agent / legacy PATH fidelity?         → classic ODBC sqlcmd
Need deploy scripts + exit codes + :r?     → sqlcmd / go-sqlcmd -b -i
Need the hop/Encrypt/identity matrix?      → CLI-06
Need the pipeline, not the client?         → CLI-07

Sources (verify periodically):
Download/install sqlcmd · Check installed version · sqlcmd utility / Go differences · Entra auth in sqlcmd · Scripting variables · Invoke-Sqlcmd · go-sqlcmd GitHub (latest v1.10.0 at research)