Connecting to SQL Server Using SQLCMD Utility

Problem

A DBA asked how to connect to a SQL Server database using the SQLCMD utility.  This tip will show how to do that with Windows Authentication and SQL Server Authentication and run a simple query.

Solution

Using the SQLCMD utility, you can execute Transact-SQL statements, stored procedures, or any other SQL commands. This can be much quicker versus launching SSMS or some other GUI. Also, there may be times when you don’t have access to a GUI tool. This is a very effective utility available to SQL Server Database Administrators or Developers to run simple or ad-hoc queries.

SQLCMD is a light-weight utility that can be used to connect to any SQL Server instance quickly.

When using the SQLCMD utility from a command line, SQLCMD uses the OLEDB provider to connect to SQL Server. However, SQL Server Management Studio (SSMS) uses the Microsoft .Net SqlClient Data Provider. You can query the DMV sys.dm_exec_sessions to see the provider used by looking at the client_interface_name column.

The SQLCMD utility is found in one of these folders depending on SQL Server version:

• C:\Program Files\Microsoft SQL Server\100\Tools\Binn\
• C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\

This tip will look at connecting to a database engine using the SQLCMD utility with Windows Authentication and SQL Server Authentication.

List of Command for SQLCMD

To get a list of the different command options, you can run SQLCMD -? at a command prompt as shown in the below snippet.

SQLCMD Connect to SQL Server Using Windows Authentication

Open a Command Prompt window and browse to the location where the SQLCMD utility is available on your machine (see above for folder location).

The below examples show how to connect to the default instance of the Database Engine on port 1433, a named instance and a named instance listening on port 1919.  (Substitute your server name and instance name to connect.)

--Default Instance
SQLCMD -S SERVERNAME -E
 
--Named Instance
SQLCMD -S SERVERNAME\INSTANCENAME -E
-- Named Instance and port
SQLCMD -S SERVERNAME\INSTANCENAME,1919 -E

Once you are connected to the database engine using SQLCMD utility, copy and paste the sample blocking code query below in the SQLCMD window and press “Enter“. Then type “GO” and press “Enter” to execute the query.

-- query to find blocking sessions
SELECT  session_id, blocking_session_id FROM sys.dm_exec_requests WHERE session_id > 50

Here you can see what the output looks like:

To close a SQLCMD session, type “EXIT” and press “Enter,” as shown in the above snippet.

SQLCMD Connect to SQL Server Using SQL Server Authentication

Open a Command Prompt window and browse to the location where the SQLCMD utility is available on your machine (see above for folder location).

The below examples show how to connect to the default instance of the Database Engine on port 1433, a named instance and a named instance listening on port 1919.  (Substitute your server and instance names to connect). 

For this connection, we need to also specify the -User and -Password parameters, as well.

--Default Instance
SQLCMD -S SERVERNAME -U sa -P St0rangPa55w0rd
 
--Named Instance
SQLCMD -S SERVERNAME\INSTANCENAME -U sa -P St0rangPa55w0rd
 
--Named Instance and port
SQLCMD -S SERVERNAME\INSTANCENAME,1919 -U sa -P St0rangPa55w0rd

Once you are connected to the database engine using SQLCMD utility, you can run the blocking query or any other query to test.

Next Steps

• Read the following tip, Enabling Dedicated Administrator Connection Feature in SQL Server. This feature will be helpful for a Database Administrator to connect to a SQL Server Instance when the database engine is not responding to regular connections.
• Read these related tips:
  • Using SQLCMD to Execute Multiple SQL Server Scripts
  • Using the SSMS Query Editor in SQLCMD Mode
  • SQL Server Management Studio SQLCMD mode option
  • Finding SQL Servers On Your Network with the OSQL and SQLCMD utilities

Ashish Kumar Mehta

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

• MSSQLTips Awards: Achiever (75+ tips) – 2013