Connecting with sqlcmd

Article
03/17/2023

The sqlcmd utility is available with the Microsoft ODBC Driver for SQL Server on Linux and macOS.

The following commands show how to use Windows Authentication (Kerberos) and SQL Server Authentication, respectively:

sqlcmd -E -Sxxx.xxx.xxx.xxx
sqlcmd -Sxxx.xxx.xxx.xxx -Uxxx -Pxxx

Available options

The following options are available in sqlcmd on Linux and macOS:

-?
Display sqlcmd usage.

-a
Request a packet size.

-b
Terminate batch job if there's an error.

-c batch_terminator
Specify the batch terminator.

-C
Trust server certificate.

-d database_name
Issue a USE database_name statement when you start sqlcmd.

-D
Causes the value passed to the sqlcmd -S option to be interpreted as a data source name (DSN). For more information, see "DSN Support in sqlcmd and bcp" at the end of this article.

-e
Write input scripts to the standard output device (stdout).

-E
Use trusted connection (integrated authentication.) For more information about making trusted connections that use integrated authentication from a Linux or macOS client, see Using Integrated Authentication.

-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
Specifies the input and output code pages. The codepage number is a numeric value that specifies an installed Linux code page. (available since 17.5.1.1)

-G
This switch is used by the client when connecting to Azure SQL Database, Azure SQL Managed Instance, or Azure Synapse Analytics to specify that the user be authenticated with Microsoft Entra ID (formerly Azure Active Directory). It can be combined with just the -P option to use access token authentication (v17.8+). This option sets the sqlcmd scripting variable SQLCMDUSEAAD = true. The -G switch requires at least sqlcmd version 17.6. To determine your version, execute sqlcmd -?.

Important

The -G option only applies to Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.

Microsoft Entra interactive authentication isn't currently supported on Linux or macOS. Microsoft Entra integrated authentication requires Microsoft ODBC Driver 17 for SQL Server version 17.6.1 or higher and a properly configured Kerberos environment.

-h number_of_rows
Specify the number of rows to print between the column headings.

-H
Specify a workstation name.

-i input_file[,input_file[,...]]
Identify the file that contains a batch of SQL statements or stored procedures.

-I
Set the SET QUOTED_IDENTIFIER connection option to ON.

-k
Remove or replace control characters.

-K application_intent
Declares the application workload type when connecting to a server. The only currently supported value is ReadOnly. If -K isn't specified, sqlcmd doesn't support connectivity to a secondary replica in an Always On availability group. For more information, see ODBC Driver on Linux and macOS - High Availability and Disaster Recovery.

Note

-K isn't supported in the CTP for SUSE Linux. You can, however, specify the ApplicationIntent=ReadOnly keyword in a DSN file passed to sqlcmd. For more information, see "DSN Support in sqlcmd and bcp" at the end of this article.

-l timeout
Specify the number of seconds before a sqlcmd login times out when you try to connect to a server.

-m error_level
Control which error messages are sent to stdout.

-M multisubnet_failover
Always specify -M when connecting to the availability group listener of a SQL Server 2012 (11.x) availability group or a SQL Server 2012 (11.x) Failover Cluster Instance. -M provides for faster detection of failovers and connection to the (currently) active server. If -M isn't specified, -M is off. For more information about Always On availability groups, see ODBC Driver on Linux and macOS - High Availability and Disaster Recovery.

Note

-M isn't supported in the CTP for SUSE Linux. You can, however, specify the MultiSubnetFailover=Yes keyword in a DSN file passed to sqlcmd. For more information, see "DSN Support in sqlcmd and bcp" at the end of this article.

-N[s|m|o]
Set the connection encryption mode to be Strict, Mandatory, or Optional respectively. Defaults to mandatory if not specified. ([s|m|o] added in sqlcmd 18.0)

-o output_file
Identify the file that receives output from sqlcmd.

-p
Print performance statistics for every result set.

-P
Specify a user password. When used with the -G option without -U, specifies a file that contains an access token (v17.8+). The token file should be in UTF-16LE (no BOM) format.

Access tokens can be obtained via various methods. It's important to ensure the access token is correct byte-for-byte, as it will be sent as-is. Below is an example command that obtains an access token. The command uses the Azure CLI and Linux commands and saves it to a file in the proper format. If your system or terminal's default encoding isn't ASCII or UTF-8, you may need to adjust the iconv options. Be sure to carefully secure the resulting file and delete it when it's no longer required.

az account get-access-token --resource https://database.windows.net --output tsv | cut -f 1 | tr -d '\n' | iconv -f ascii -t UTF-16LE > /tmp/tokenFile

-q commandline_query
Execute a query when sqlcmd starts, but doesn't exit when the query has finished running.

-Q commandline_query
Execute a query when sqlcmd starts. sqlcmd will exit when the query finishes.

-r
Redirects error messages to stderr.

-R
Causes the driver to use client regional settings to convert currency and date and time data to character data. Currently only uses en_US (US English) formatting.

-s column_separator_char
Specify the column-separator character.

-S [protocol:] server[,port]
Specify the instance of SQL Server to connect to, or if -D is used, a DSN. The ODBC driver on Linux and macOS requires -S. The only valid protocol value is tcp.

-t query_timeout
Specify the number of seconds before a command (or SQL statement) times out.

-u
Specify that output_file is stored in Unicode format, whatever the format of input_file.

-U
login_id Specify a user login ID.

-V error_severity_level
Control the severity level that is used to set the ERRORLEVEL variable.

-w column_width
Specify the screen width for output.

-W
Remove trailing spaces from a column.

-x
Disable variable substitution.

-X
Disable commands, startup script, and environment variables.

-y variable_length_type_display_width
Set the sqlcmd scripting variable SQLCMDMAXFIXEDTYPEWIDTH.

-Y fixed_length_type_display_width
Set the sqlcmd scripting variable SQLCMDMAXVARTYPEWIDTH.

-z password
Change password.

-Z password
Change password and exit.

Available commands

In the current release, the following commands are available:

[:]!!
:Connect
:Error
[:]EXIT
GO [count]
:Help
:List
:Listvar
:On Error
:Out
:Perftrace
[:]QUIT
:r
:RESET
:setvar

Unavailable options

In the current release, the following options aren't available:

-A
Log in to SQL Server with a Dedicated Administrator Connection (DAC). For information on how to make a dedicated administrator connection (DAC), see Programming Guidelines.

-L
List the locally configured server computers, and the names of the server computers that are broadcasting on the network.

-v
Create a sqlcmd scripting variable that can be used in a sqlcmd script.

You can use the following alternative method: Put the parameters inside one file, which you can then append to another file. This method will help you use a parameter file to replace the values. For example, create a file called a.sql (the parameter file) with the following content:

:setvar ColumnName object_id
:setvar TableName sys.objects

Then create a file called b.sql, with the parameters for replacement:

SELECT $(ColumnName) FROM $(TableName)

At the command line, combine a.sql and b.sql into c.sql using the following commands:

cat a.sql > c.sql

cat b.sql >> c.sql

Run sqlcmd and use c.sql as input file:

sqlcmd -S<...> -P<..> -U<..> -I c.sql

Unavailable commands

In the current release, the following commands aren't available:

:ED
:ServerList
:XML

DSN support in sqlcmd and bcp

You can specify a data source name (DSN) instead of a server name in the sqlcmd or bcp -S option (or sqlcmd :Connect command) if you specify -D. -D causes sqlcmd or bcp to connect to the server specified in the DSN by the -S option.

System DSNs are stored in the odbc.ini file in the ODBC SysConfigDir directory (/etc/odbc.ini on standard installations). User DSNs are stored in .odbc.ini in a user's home directory (~/.odbc.ini).

On Windows systems, System and User DSNs are stored in the registry and managed via odbcad32.exe. File DSNs aren't supported by bcp and sqlcmd.

See DSN and Connection String Keywords and Attributes for the list of entries that the driver supports.

In a DSN, only the DRIVER entry is required, but to connect to a remote server, sqlcmd or bcp needs a value in the SERVER element. If the SERVER element is empty or not present in the DSN, sqlcmd and bcp will attempt to connect to the default instance on the local system.

When using bcp on Windows systems, SQL Server 2017 (14.x) and earlier require the SQL Native Client 11 driver (sqlncli11.dll) while SQL Server 2019 (15.x) and higher require the Microsoft ODBC Driver 17 for SQL Server driver (msodbcsql17.dll).

If the same option is specified in both the DSN and the sqlcmd or bcp command line, the command line option overrides the value used in the DSN. For example, if the DSN has a DATABASE entry and the sqlcmd command line includes -d, the value passed to -d is used. If Trusted_Connection=yes is specified in the DSN, Kerberos authentication is used and user name (-U) and password (-P), if provided, are ignored.

Existing scripts that invoke isql can be modified to use sqlcmd by defining the following alias: alias isql="sqlcmd -D".