Cross platform go-sqlcmd utility for SQL Server

Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

The go-sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt and uses the go-mssqldb driver for go language. go-sqlcmd aims to be a complete port of sqlcmd to the go language and compiles to executable binaries for Windows, macOS, and Linux on both x64 and arm64 architectures. Download and install the go-sqlcmd binaries to get started without additional dependencies. Using go-sqlcmd in place of sqlcmd removes the ODBC driver dependency, increases options for Azure Active Directory (Azure AD) authentication types, and adds additional enhancements.

go-sqlcmd is open source under the MIT license and available on GitHub. As a CLI, go-sqlcmd is ideal for pipelines and edge applications as it has no additional dependencies and supports various environment configurations. The capabilities of go-sqlcmd expand beyond the ODBC-based sqlcmd to incorporate a vertical output format and extensive Azure Active Directory authentication options.

Download and install go-sqlcmd

Syntax

For more in-depth information on sqlcmd syntax and use, see:

• sqlcmd syntax
• Start the sqlcmd Utility
• Use the sqlcmd Utility

Breaking changes from sqlcmd

Several switches and behaviors are altered from sqlcmd in go-sqlcmd. For the most up-to-date list of missing flags for backwards compatibility, visit the Prioritize implementation of back-compat flags GitHub discussion.

• -P switch is removed. Passwords for SQL Server Authentication can only be provided through these mechanisms:

  • The SQLCMDPASSWORD environment variable
  • The :CONNECT command
  • When prompted, the user can type the password to complete a connection

• -r requires a 0 or 1 argument

• -R switch is removed.

• -I switch is removed. To disable quoted identifier behavior, add SET QUOTED IDENTIFIER OFF in your scripts.

• -N now takes a string value that can be one of true, false, or disable to specify the encryption choice. (default is the same as omitting the parameter)

  • If -N and -C aren’t provided, go-sqlcmd negotiates authentication with the server without validating the server certificate.
  • If -N is provided but -C isn’t, go-sqlcmd requires validation of the server certificate. A false value for encryption could still lead to the encryption of the login packet.
  • If both -N and -C are provided, go-sqlcmd uses their values for encryption negotiation.
  • More information about client/server encryption negotiation can be found at MS-TDS PRELOGIN.

• -u The generated Unicode output file has the UTF-16 Little-Endian Byte-order mark (BOM) written to it.

• Some behaviors that were kept to maintain compatibility with OSQL may be changed, such as alignment of column headers for some data types.

• All commands must fit on one line, even EXIT. Interactive mode doesn’t check for open parentheses or quotes for commands, and doesn’t prompt for successive lines. The ODBC sqlcmd allows the query run by EXIT(query) to span multiple lines.

Connections from go-sqlcmd are limited to TCP connections. Named pipes aren’t supported at this time in the go-mssqldb driver.

Enhancements

• :Connect now has an optional -G parameter to select one of the authentication methods for Azure SQL Database – SqlAuthentication, ActiveDirectoryDefault, ActiveDirectoryIntegrated, ActiveDirectoryServicePrincipal, ActiveDirectoryManagedIdentity, ActiveDirectoryPassword. More information on Azure Active Directory authentication support follows. If -G isn’t provided, Integrated security or SQL Server Authentication is used, depending on the presence of a -U user name parameter.

• The new --driver-logging-level command line parameter allows you to see traces from the go-mssqldb driver. Use 64 to see all traces.

• sqlcmd can now print results using a vertical format. Use the new -F vertical command line switch to set it. The SQLCMDFORMAT scripting variable also controls it.

Azure Active Directory authentication

This version of sqlcmd supports a broader range of Azure AD authentication models based on the azidentity package. The implementation relies on an Azure AD connector in the driver.

Command line arguments

To use Azure AD authentication, you can use one of two command line switches.

-G is (mostly) compatible with its usage in the prior version of sqlcmd. If a username and password are provided, it authenticates using Azure AD Password authentication. If a user name is provided, it uses Azure AD Interactive authentication, which may display a web browser. If no username or password is provided, it uses a DefaultAzureCredential, which attempts to authenticate through various mechanisms.

--authentication-method= can be used to specify one of the following authentication types.

ActiveDirectoryDefault

• For an overview of the types of authentication this mode uses, see Default Azure Credential.
• Choose this method if your database automation scripts are intended to run in both local development environments and in a production deployment in Azure. In your development environment, you can use a client secret or an Azure CLI login. Without changing the script from the development environment, you can use a managed identity or client secret on your production deployment.
• Setting environment variables AZURE_TENANT_ID and AZURE_CLIENT_ID are necessary for DefaultAzureCredential to begin checking the environment configuration and look for one of the following additional environment variables in order to authenticate:
  • Setting environment variable AZURE_CLIENT_SECRET configures the DefaultAzureCredential to choose ClientSecretCredential.
  • Setting environment variable AZURE_CLIENT_CERTIFICATE_PATH configures the DefaultAzureCredential to choose ClientCertificateCredential if AZURE_CLIENT_SECRET isn’t set.
  • Setting environment variable AZURE_USERNAME configures the DefaultAzureCredential to choose UsernamePasswordCredential if AZURE_CLIENT_SECRET and AZURE_CLIENT_CERTIFICATE_PATH aren’t set.

ActiveDirectoryIntegrated

This method is currently not implemented, and falls back to ActiveDirectoryDefault.

ActiveDirectoryPassword

• This method authenticates using a username and password. It doesn’t work if MFA is required.

• You provide the user name and password using the usual command line switches or SQLCMD environment variables.

• Set AZURE_TENANT_ID environment variable to the tenant ID of the server if not using the default tenant of the user.

ActiveDirectoryInteractive

This method launches a web browser to authenticate the user.

ActiveDirectoryManagedIdentity

Use this method when running sqlcmd on an Azure VM that has either a system-assigned or user-assigned managed identity. If using a user-assigned managed identity, set the user name to the ID of the managed identity. If using a system-assigned identity, leave user name empty.

ActiveDirectoryServicePrincipal

This method authenticates the provided user name as a service principal ID and the password as the client secret for the service principal. Provide a user name in the form @. Set SQLCMDPASSWORD variable to the client secret. If using a certificate instead of a client secret, set AZURE_CLIENT_CERTIFICATE_PATH environment variable to the path of the certificate file.

Some Azure AD authentication settings don’t have command line inputs, and some environment variables are consumed directly by the azidentity package used by sqlcmd.

These environment variables can be set to configure some aspects of Azure AD authentication and to bypass default behaviors. In addition to the variables listed previously, the following are sqlcmd-specific and apply to multiple methods.

SQLCMDCLIENTID

Set this environment variable to the identifier of an application registered in your Azure AD, which is authorized to authenticate to Azure SQL Database. Applies to ActiveDirectoryInteractive and ActiveDirectoryPassword methods.

Next steps

• Learn more about go-sqlcmd on GitHub
• Run on Docker