DB-유

Using sqlcmd from Linux

· mssql, linux, sqlcmd, ubuntu

I got tired of RDP-ing into a Windows box just to run a health check query. My monitoring scripts already live on an Ubuntu 16.04 machine, and now that SQL Server 2017 is out on Linux, Microsoft ships sqlcmd and bcp as a native package. They work like the Windows versions and connect to any SQL Server from 2008 up, so backups, checks and deployments can run from cron.

You need Ubuntu 16.04 (RHEL/CentOS 7 and SLES 12 work with the matching repo), curl and apt-transport-https, and a server reachable on TCP 1433 with a SQL login. Windows authentication needs Kerberos, which I'll get to.

Add the Microsoft repository

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update

On CentOS 7 the equivalent is:

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

Install mssql-tools and the ODBC driver

The tools depend on the Microsoft ODBC Driver 13 for SQL Server and on unixODBC. Both prompt for an EULA; set ACCEPT_EULA=Y to keep the install non-interactive.

sudo ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev

The binaries land in /opt/mssql-tools/bin, which is not on the PATH. Add it:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

For scripts running under cron, either use the full path /opt/mssql-tools/bin/sqlcmd or set PATH inside the crontab.

Connecting

Same switches as on Windows:

sqlcmd -S sqlprod01.example.com -U sa -P 'MyStr0ngPass!'
sqlcmd -S sqlprod01.example.com,1433 -U appuser -P 'secret' -d Sales
sqlcmd -S 10.0.0.12 -U sa -Q "SELECT @@VERSION"

Note the comma for a non-default port; named instances (server\instance) need SQL Browser reachable over UDP 1434, and the backslash has to be quoted in bash: -S 'sqlprod01\DEV2016'. I always connect by port from Linux and skip SQL Browser entirely.

Inside the interactive prompt everything works as expected: type T-SQL, GO to execute, :quit or exit to leave. Command history and line editing are available because the Linux build links against readline.

Scripts and output

Run a script file and write the output to a file:

sqlcmd -S sqlprod01 -U deploy -P "$SQLPASS" -d Sales -i /home/dba/deploy/001_create_tables.sql -o /var/log/deploy/001.log -b

The -b flag makes sqlcmd return a non-zero exit code when a T-SQL error occurs, which is what you want in shell scripts:

#!/bin/bash
set -e
export PATH="$PATH:/opt/mssql-tools/bin"
sqlcmd -S sqlprod01 -U backupop -P "$SQLPASS" -b -Q "BACKUP DATABASE Sales TO DISK = N'/var/opt/mssql/backup/Sales.bak' WITH INIT, COMPRESSION, CHECKSUM"
echo "backup finished $(date)"

For machine-readable output, -s ',' sets the column separator and -W trims trailing spaces; -h -1 removes the header row:

sqlcmd -S sqlprod01 -U reader -P "$SQLPASS" -h -1 -W -s ',' -Q "SET NOCOUNT ON; SELECT name, state_desc FROM sys.databases"

Windows authentication uses -E and requires a Kerberos ticket (kinit user@DOMAIN.LOCAL) plus a working krb5.conf. It works, but it's more setup than I want for a script host, so I use a dedicated SQL login with minimal rights for each job.

Quick sanity check

sqlcmd -S sqlprod01 -U sa -P "$SQLPASS" -Q "SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY('ProductVersion'), host_platform FROM sys.dm_os_host_info"
echo "exit code: $?"

host_platform returns Windows or Linux depending on where the server runs, and the exit code should be 0.

Errors I've hit

  • "Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired." followed by "TCP Provider: Error code 0x2749" or 0x2AF9. The host is unreachable, the port is closed or the name does not resolve. Test with nc -zv sqlprod01 1433 and check the Windows firewall on the server.
  • "Login failed for user 'sa'." with "Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only." in the server's ERRORLOG. Switch the instance to Mixed Mode in SSMS under Server Properties → Security.
  • "sqlcmd: command not found" from cron even though it works in your shell. Cron does not read .bashrc; use the full path or define PATH in the crontab.
  • Passwords with $ or ! get mangled by bash. Wrap them in single quotes on the command line, or export them from a file with chmod 600.
  • "Unable to load shared library 'libodbc.so.2'" or "[unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server'". The ODBC driver package did not install, or /etc/odbcinst.ini is missing the driver entry. Reinstall msodbcsql and check odbcinst -q -d.

bcp lands in the same directory, by the way, and I've started using it for bulk exports too.