By: Rajendra Gupta | 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.
If winget is not installed, you get the following 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.
Type Y and press Enter to agree to the source agreement terms. It starts downloading MSI from GitHub, then Windows Installer, as shown below.
It quickly finishes the go-sqlcmd installation on Windows.
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.
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.
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'"
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
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:
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.
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.
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.
To use the Named Pipe protocol, use the following syntax:
sqlcmd -S . \\localhost\pipe\sql\query
Similarly, use the following connection string to use the TCP protocol:
sqlcmd -S tcp:localhost,1433
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.
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
About the author
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
