New Command Line Tool go-sqlcmd for SQL Server

By:   |   Updated: 2023-07-06   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Tools


Problem

SQLCMD is a Microsoft SQL Server command-line tool for running SQL queries and performing automation tasks on Windows and Linux systems. It is also used for running dynamic SQL as well. Microsoft recently announced the go-sqlcmd tool as a new version of the SQLCMD tool. Have you heard about it? If not, let's explore the tool in this tip.

Solution

The go-sqlcmd utility is built on the go-mssqldb driver for connecting to SQL Server, executing T-SQL statements, and scripts similar to the existing SQLCMD. It removes the SQLCMD dependency on the ODBC driver. It works on Windows, macOS, and Linux systems on X64 and ARM64 architecture. Its installation is quite simple, and you can install it without dependencies.

The few valuable features of the go-sqlcmd tool include the following:

  • It is an open-source tool licensed under MIT, the source code is on GitHub.
  • It supports various Azure active authentication methods - ActiveDirectoryDefault, ActiveDirectoryIntegrated, ActiveDirectoryPassword, ActiveDirectoryInteractive, ActiveDirectoryManagedIdentity, ActiveDirectoryServicePrincipal.
  • It supports all SQLCMD features and enhancements, such as results in a vertical format.

Note: The go-sqlcmd is in the preview phase at the time of writing this tip.

go-sqlcmd installation on Windows

To install the latest go-sqlcmd on Windows, launch a command prompt and run these commands.

Step 1 - see if winget is installed

Run winget to see if the Windows Package Manager Client is installed. It returns the winget version and command options if already installed.

Winget

If winget is not installed, you get the following error message.

Winget error message

You can download and install it from here install Winget.

Step 2 - install go-sqlcmd

To install, run the command winget install sqlcmd to install the go-sqlcmd tool.

Winget install

Type Y and press Enter to agree to the source agreement terms. It starts downloading MSI from GitHub, then Windows Installer, as shown below.

Windows Installer

It quickly finishes the go-sqlcmd installation on Windows.

installed

To verify the installation, re-launch the command prompt and run the command sqlcmd --help. The command output shows the link https://github.com/microsoft/go-sqlcmd/issues/new, its usage, and examples. As the link suggests, it is the new SQLCMD version, i.e., go-sqlcmd.

Verify installation

Alternatively, connect to SQL Server using sqlcmd. The below connects to the local host server using . with a trusted connection.

sqlcmd -S . -E

Run the following query:

--mssqltips.com
select session_id, client_interface_name, program_name from sys.dm_exec_sessions where session_id=@@spid

The output shows the client interface name is go-mssqldb and the program name is sqlcmd.

sqlcmd syntax

Explore the go-sqlcmd Tool

If we use the existing SQLCMD and run a query that returns multiple columns, the query output is difficult to read as it spans multiple rows and columns as shown below:

--mssqltips.com
sqlcmd -S . -E -Q "select * from sys.databases where name='master'"
query output hard to read

The go-sqlcmd provides a new formatting option with the -F parameter. We need to specify the keyword vertical with the -F parameter, which changes the column's output in a user-friendly way.

--mssqltips.com
sqlcmd -S . -E -Q "select * from sys.databases where name='master'" -F vertical
go-sqlcmd new formatting option

SQL Authentication with go-sqlcmd

Previously, we connected to SQL Server using the integrated (Windows) authentication. SQL authentication requires a username and password for connection to the SQL Server instance.

Usually, with the existing SQLCMD, the syntax to connect with SQL authentication is:

--mssqltips.com
Sqlcmd -S “ServerName” -U “UserName” -P “Password”

The go-sqlcmd deprecates the -P “password” use for specifying the password in SQL authentication. It gives the following error message:

sqlcmd: error: sqlcmd: '-P' is obsolete. The initial passwords must be set using the SQLCMDPASSWORD environment variable or entered at the password prompt.

It provides options for specifying passwords in SQL authentication:

  • The SQLCMDPASSWORD environment variable
  • : CONNECT command
  • Users can enter their password during the password prompt

For example, in the screenshot below, we did not use the parameter -P, which prompts the user to enter the password. It follows the Unix style and does not show any characters for user passwords. Type the password and press Enter for a connection using SQL authentication.

did not use the parameter -P

Protocols for go-sqlcmd

SQLCMD supports Shared Memory, Named Pipes, and TCP protocols for database connections. I have all three protocols enabled in SQL Server Configuration Manager for my demo environment, as shown below.

3 protocols enabled in SQL Server Configuration Manager

Now, let's connect to SQL Server locally without specifying any protocol and check the connection protocol with the following SQL code:

--mssqltips.com
select net_transport from sys.dm_exec_connections where session_id=@@spid
go

As shown in the image below, the protocol to connect locally is Shared Memory.

Shared memory

To use the Named Pipe protocol, use the following syntax:

sqlcmd -S . \\localhost\pipe\sql\query
Named pipe protocol

Similarly, use the following connection string to use the TCP protocol:

sqlcmd -S tcp:localhost,1433
TCP protocol

Note: If the connection string does not specify any protocol, SQLCMD will attempt to connect in the following order. If connecting to a remote host, lpc will be skipped.

  • Shared Memory (lpc) > Named Pipes (np) > TCP

The go-sqlcmd Microsoft docs states that go-sqlcmd connections are limited to TCP connections. However, in my demo, I could use all three network protocols.

Microsoft docs

Console Colors

go-sqlcmd supports syntax coloring of the output of T-SQL queries in the terminal. After connecting to go-sqlcmd, execute the following command to see all color options:

--mssqltips.com
:list color

Azure Active Directory Authentication

go-sqlcmd supports multiple ways to connect to SQL Server using Azure Active Directory Authentication. You can also use the -G parameter compatible with the older version of SQLCMD:

  • Azure Active Directory Password Authentication: Specify the username and password with the -G parameter.
  • Azure Active Directory Interactive Authentication using Web Browser: Specify only username.
  • DefaultAzureCredential: Without specifying the username or password that authenticates through various mechanisms.

You can use the --authentication-method parameter to specify the authentication types from any of the below:

ActiveDirectoryDefault

It attempts to authenticate using either of the following for the DefaultAzureCredential:

  • Environment Variables: It uses the information from environment variables.
  • Workload Identity: It uses the environment variables set of the workload identity webhook for the Kubernetes app.
  • Managed Identity: It is for the Azure host with the managed identity enabled.
  • Azure CLI: It is for authentication through the Azure CLI az login command.

You can refer to Azure Identity Client Module for Go for more information.

This method is suitable if the automation scripts are supposed to run on dev and production deployment in Azure.

  • ActiveDirectoryIntegrated: This method is not in implementation yet. If AzureDirectoryIntegrated is specified in the script, it fails back to ActiveDirectoryDefault.
  • ActiveDirectoryPassword: It uses the username and password to authenticate. You can specify the details using the command line switch or SQLCMD environment variables.
  • ActiveDirectoryInteractive: It opens a web browser window to enter the credentials for authentication.
  • ActiveDirectoryManagedIdentity: You can use this method if the Azure VM has either a system-assigned or user-assigned managed identity.
  • ActiveDirectoryServicePrincipal: In this method, authentication occurs using the user name as Service Principal ID and the password as a secret for the Service Principal.
Next Steps
  • Explore Microsoft docs for more info on go-sqlcmd documentation.
  • Explore existing tips on SQLCMD.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-07-06

Comments For This Article




Thursday, July 6, 2023 - 10:30:46 AM - Greg Robidoux Back To Top (91360)
Thanks this has been fixed.

Thursday, July 6, 2023 - 8:22:53 AM - Dany Van Den Steen Back To Top (91359)
slqcmd -S . -E

has to be

sqlcmd -S . -E