Connecting to SQL Server using SQL Server Management Studio

Back to: SQL Server Tutorial

Connecting to SQL Server using SQL Server Management Studio

In this article, I am going to discuss how to connect to SQL Server Database using SQL Server Management Studio (SSMS). The most important thing that you need to keep in mind is that both SQL Server and SSMS are two different things. Here SQL Server is the database and SQL Server Management Studio (SSMS) is the tool that can be used by a user to write and execute SQL queries against the SQL Server Database. As part of this article, we are going to discuss the following pointers in detail.

  1. How to connect to the SQL Server using SQL Server Management Studio?
  2. Understanding the Server Type available in SQL Server.
  3. Understanding the Authentication while connecting with the database.
  4. Windows Authentication.
  5. SQL Server Authentication.
How to connect to the SQL Server using SQL Server Management Studio?

You need to follow the below to connect to the SQL Server using SQL Server Management Studio

  1. First click on the start
  2. Then select all Programs
  3. Select the version of Microsoft SQL Server that is installed in your machine, in my case it is 2014
  4. Finally, click on the SQL Server Management Studio which will open the below window.

Connecting to SQL Server SQL Server Management Studio (SSMS)

Let us discuss what we need to select and provide from the above image

Step1. Select Server Type

As we are going to connect with the SQL Server database, so here we need to select the Server Type as Database Engine. Along with Database Engine, the other Server Types options are available as SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS) and SQL Server Integration Services (SSIS). These there are called MSBI (Microsoft Business Intelligence) which we will discuss in detail in our MSBI Tutorials.

Database Engine:
  1. The database engine is the core service of SQL Server which will use for storing a large amount of data, accessing the data, manipulating the data and providing security to the information.
  2. In the database engine, the data will be stored in the form of a 2-D format (Tables).
Analysis Services (SSAS):
  1. The SSAS (SQL Server Analysis Service) is a tool that is used under the data warehousing/ data mining environment for storing the information in the form of a 3-D format.
Reporting Services (SSRS):
  1. SQL Server Reporting Service (SSRS) is a tool that is used to generate various reports such as MS-Word file format, MS-Excel format, .pdf format, XML format, .tiff file format et.
  2. The report is a document that is used to store some business-related information.
Integration Services (SSIS):
  1. The SQL Server Integration Service (SSIS) is a tool that is used to convert one database tables into another database understandable format. For example, SQL Server database Tables are converting into Oracle understandable table format.

The SSAS, SSRS, SSIS tools are coming under the MSBI (Microsoft Business Intelligence) tool.

Step2. Specify Server Name:

The server name is nothing but the name of the SQL Server or IP address of SQL Server. One more thing that you need to remember is if the SQL Server is installed on your machine then you can specify the server name as a dot (.) or 127.0.0.1 or local.

Server name = (local)

Step3.  Select Authentication

In SQL Server there are two types of authentication i.e. Windows Authentication and SQL Server Authentication. But it will depend on how you installed the SQL Server. That means, at the time of installation, if you select the mixed-mode authentication then you will get both Windows and SQL Server authentication to connect with the SQL Server database or else you will only have windows authentication to connect with the database.

Windows Authentication:
  1. It is the default authentication mode of SQL Server.
  2. In Windows authentication, we will work on user admin.
  3. With Windows authentication mode there is no need to enter the user credentials i.e. user Id and password because User Id and Password are generated by the Operating System by default,
Windows Authentication Mode in SQL Server
SQL Server Authentication:
  1. In SQL Server authentication we will work on the current user.
  2. When we will work with SQL Server authentication we should enter user Id and Password (This user Id and password are created by the user at the time of SQL Server software installation).

SQL Server Authentication Mode

How you connect to the SQL Server, it is up to you.

Once you provide the necessary details and click on the Connect button, then we will be connected to the SQL Server Database. Once you connected, now, click on the New Query option. You can find the New Query option on the top left corner of SQL Server Management Studio as shown in the below image.

SQL Server Management Studio New Query Window

Once you click on the New Query option, it should open the new query editor window, where we can type SQL queries and execute which we will discuss in our next article.

The key point that you need to remember from this article is that the SSMS (SQL Server Management Studio) is not the Server, it is the client tool that can be used by a user to connect with Server. The database server (SQL Server) is generally on a dedicated machine, and the users are connecting to the server using SQL Server Management Studio from their machines as shown in the below image.

Using SQL Server Management Studio to connect SQL Server

As shown in the above image, the developer machines are connecting to the database server using SQL Server Management Studio.

In the next article, I am going to discuss show how to create, alter and delete a database in SQL Server. Here, in this article, I try to explain how to use SQL Server Management Studio (SSMS) to connect with SQL Server Database step by step. I hope this article will help you with your needs.