How to Connect to SQL Server Using SSMS, sqlcmd Utility, and dbForge Studio for SQL Server

December 29th, 2021

In the article, we are going to provide a step-by-step guide on how to connect to SQL Server and execute SQL queries using the sqlcmd utility, SQL Server Management Studio, and dbForge Studio for SQL Server.

Download a free 30-day trial version of dbForge Studio for SQL Server to evaluate its cutting-edge features and capabilities

Contents

Connecting to MS SQL Server using SSMS

SQL Server Management Studio (SSMS) is a powerful IDE with a set of tools for the management, configuration, administration, monitoring, and development of SQL Server and Azure SQL databases.

In the section, we’ll describe how to connect to SQL Server using Windows Authentication and SQL Server Authentication.

Prerequisites

To start working with SSMS, it should be installed on your computer. If not, download and install it.

Connect to SQL Server using Windows Authentication

The Windows Authentication mode allows you to connect with the credentials from your Windows user account.

To connect to the server, open the SSMS. In Object Explorer, click Connect and select Database Engine from the drop-down list. Alternatively, click Connect Object Explorer.

Open the connection manager in SSMS

In the Connect to Server dialog that opens, enter the following:

  • Server name: Specify the name of the server to which you want to connect. If you don’t use the default instance MSSQLSERVER, you should specify the server name and instance name separated with a backward slash.
  • Authentication: Select Windows Authentication from the drop-down list. Your Windows domain login and password will be pulled up automatically.

If you want to change some additional connection properties, such as a network protocol, column encryption, connection string parameters, connection or execution timeout, click Options.

Enter connection properties for the Windows Authentication type in SSMS

After the connection properties are entered, click Connect. A new connection will be displayed in Object Explorer.

Verify that the connection with the Windows Authentication type has been created

Connect to SQL Server using SQL Server Authentication

The SQL Server Authentication mode allows you to connect to the server by providing the SQL Server login and password.

To connect to the server, open the connection manager by selecting Object Explorer > Connect > Database Engine from the drop-down list or by clicking Connect Object Explorer.

Open the connection manager in SSMS

In the Connect to Server dialog that opens, enter the following information:

  • Server name: Specify the server name and instance name separated with a backward slash.
  • Authentication: Select SQL Server Authentication from the drop-down list.
  • Login: Enter the login from the server account required to log in to the SQL Server.
  • Password: Enter the password from the server account required to log in to the SQL Server. To save the password for later use, select the Remember password checkbox.

To modify connection properties, such as a network protocol, column encryption, connection string parameters, connection or execution timeout, click Options.

Enter connection properties for the SQL Server Authentication type in SSMS

Once done, click Connect. The new connection will be displayed and set as active in Object Explorer.

After the connection is created, let’s do the following:

  • View a list of databases available on the server
  • View a list of tables available on the selected database
  • Retrieve data from the table

View a list of databases available on the server

In SSMS, you can easily view a list of databases located on the server in Object Explorer. Select the server and expand the Databases node.

View a list of databases available on SQL Server in SSMS

View a list of tables available in the selected database

To see the tables of the specific database, in Object Explorer, expand the selected database and then expand the Tables node.

View a list of tables available on the selected database in SSMS

Retrieve data from the table

To move on, let’s run a SQL query to check that everything works properly. For example, we want to retrieve only the ‘Quality Assurance’ department from the HumanResources.Department and HumanResources.EmployeeDepartmentHistory tables. To do so, on the toolbar, click New Query. In the SQL document that opens, enter the following SQL statement and then click Execute.

SELECT
	*
FROM HumanResources.Department d
JOIN HumanResources.EmployeeDepartmentHistory edh
	ON d.DepartmentID = edh.DepartmentID
WHERE d.GroupName = 'Quality Assurance'; 

The output is as follows:

Execute the query in SSMS

Connecting to SQL Server using the sqlcmd utility

The sqlcmd utility allows you to execute SQL queries, T-SQL statements, system procedures, and script files using the command line. The utility uses the OLEDB provider to connect to the server.

In this section, we’ll describe how to connect to SQL Server using Windows Authentication and SQL Server Authentication, and to execute the query.

Note: Prior to working with the sqlcmd utility, make sure that it has been downloaded and installed on your Windows machine.

The default location of the sqlcmd utility is ‘C:\Program Files\Microsoft SQL Server\150\Tools\Binn’.

Connect to a SQL Server instance using Windows Authentication

Open the Command Prompt and switch to the location of the sqlcmd utility. Then, execute the following command by replacing the connection parameters with the server (server_name) and instance (instance_name) names to which you want to connect.

sqlcmd -S server_name\instance_name -E

-E indicates a trusted connection.

If you have successfully connected, you will see 1>. It verifies that you are connected to the SQL Server and can execute SQL statements.

Connect to the SQL Server using Windows Authentication

To close the current sqlcmd session, type exit and press Enter.

Connect to a SQL Server instance using SQL Server Authentication

Now, let’s see how to connect to SQL Server using SQL Server Authentication. Except for the server and instance names to which you want to connect, you also need to specify a user name:

sqlcmd -S server_name\instance_name -U user_name

After that, you will need to enter the password. If you have connected to the server, a new line starts with 1>. This means that the connection is successfully created and you can start working with queries.

Connect to the SQL Server using SQL Server Authentication

After we have connected to the SQL Server, we are going to verify that it works properly by executing some SQL queries that allow us to:

  • View a list of databases available on the server
  • View a list of tables available on the selected database
  • Retrieve data from the table

View a list of databases available on the server using the sqlcmd utility

To get the list of databases located on the server, execute the following query and press Enter:

SELECT name, database_id, create_date FROM sys.databases;

Then, type GO and press Enter. In the output result, you will see all databases connected to SQL Server, including the database name, its ID, the creation date, and the total number of databases.

Get the list of databases available on the SQL Server using the sqlcmd utillity

View a list of tables available in the selected database using the sqlcmd utility

To view a list of tables located in the database, execute the following query and press Enter:

SELECT name, crdate FROM sysobjects WHERE xtype = 'U';

where name is the name of the table, crdate – a creation date, xtype – an object type for the row, and U – user table. Then, type GO and press Enter.

View a list of tables located on the selected database

As you can see, the query returns the table, its creation date, and the total number of tables available in the BicycleStoreDev database.

Retrieve data from the table using the sqlcmd utility

As mentioned, we will retrieve data from the table. For this, we are going to use the AdventureWorks2019 database and execute a SELECT statement to get data from the Person.Person and HumanResources.Employee tables with the condition that BusinessEntityID is higher than 200. To cut the list for demo purposes, we output the first 20 rows in the result.

SELECT TOP 20
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,p.EmailPromotion
FROM Person.Person p
INNER JOIN HumanResources.Employee e
  ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID > 200;

In the Command Prompt, switch to the AdventureWorks2019 database to use it, type the query, and press Enter. Then, type GO and press Enter again. The output will display the required data.

Execute the SELECT TOP SQL statement to retrieve data from the table using the sqlcmd utility

Now, you can close the utility by typing exit and pressing Enter.

Connecting to SQL Server using dbForge Studio for SQL Server

dbForge Studio for SQL Server is a forefront IDE that comes with a bunch of built-in tools, features, and capabilities that allow you to enjoy the process of SQL Server database development, management, administration, and deployment and boost your productivity at the same time.

To get started, download and install dbForge Studio for SQL Server. Keep in mind that you can use a free fully-functional 30-day trial version of the tool to evaluate all its remarkable features and functionalities. After the trial version expires, you will be offered to purchase the tool.

To connect to SQL Server, launch the Studio and open the Connection Manager by using one of the following ways:

  • On the Database Explorer toolbar, click New Connection.
  • On the Database main menu, select New Connection.
Open the Connection Manager to connect to the SQL Server using dbForge Studio for SQL Server

In the Database Connection Properties dialog that opens, do the following:

  • Select the server to which you want to connect.
  • Choose the authentication mode from the drop-down list.
  • Depending on the authentication mode you chose, enter the login and password.
  • Select the database you want to connect to.
  • Optional step: Assign the environment category (development, production, sandbox, or test).

If you want to configure some additional connection details such as connection and execution timeout, connection encryption, switch to the Advanced tab.

After the connection details are added, click Connect.

Enter the database connection details using dbForge Studio for SQL Server

The Database Explorer will display the new connection with the green connection icon that indicates the active connection.

View the created SQL Server connection in Database Explorer of dbForge Studio for SQL Server

For more information about how to connect to SQL Server with dbForge Studio for SQL Server, see the documentation.

After the connection is created, let’s do the following:

  • View a list of databases available on the server
  • View a list of tables available on the selected database
  • Retrieve data from the table

View a list of databases available on the server using dbForge Studio for SQL Server

In dbForge Studio for SQL Server, you can easily view a list of databases located on the server without executing a query. To do so, go to the Database Explorer and expand the server connection.

View a list of databases located on the server

View a list of tables available in the selected database using dbForge Studio for SQL Server

If you want to see tables located on a specific database, you simply need to navigate to the Database Explorer and expand the selected database > the Tables node. The list of tables available on the server will be displayed. The Tables folder will also contain the total number of tables in brackets.

View a list of tables available on the selected database

Retrieve data from the table using dbForge Studio for SQL Server

Now, let’s execute a SQL statement to retrieve data from the Production.Product table based on the condition that the ProductID value is higher than 300. To do so, click New SQL on the toolbar. In the SQL document that opens, type the query and click Execute on the toolbar.

Execute the SQL query with dbForge Studio for SQL Server

As you can see, dbForge Studio for SQL Server is an easy and visual way to connect to SQL Server and quickly customize connection configuration up to your needs.

You can also watch this step-by-step tutorial to see how to connect to a SQL Server instance in dbForge Studio for SQL Server.

Conclusion

In the article, we described several ways to connect to SQL Server using the sqlcmd utility, SQL Server Management Studio, and dbForge Studio for SQL Server. With SSMS and dbForge Studio for SQL Server, you can easily set up connection details visually in the wizards. However, dbForge Studio for SQL Server is the best alternative to SSMS that leaves it behind due to a rich set of features and capabilities you can use in an intuitive and user-friendly GUI.

Download a free fully-functional 30-day trial version of dbForge Studio for SQL Server to evaluate its features and functionalities

Comments are closed.