By: Haroon Ashraf
Overview
This chapter shows how to use SSMS to connect to the main component of SQL Server which is the Database Engine.
Using SSMS to Connect to SQL Server Database Engine
According to Microsoft, the Database Engine is an essential component of SQL Server for storing, processing and securing data. This is where you will find databases, tables (where data is stored), views, stored procedures and other database objects.
Connecting to the Database Engine also means connecting to a specific instance of SQL Server. For each installation of SQL Server there is a unique instance of the Database Engine, usually referred to as the default instance. In addition, you can also install multiple instances of the Database Engine on the same desktop or server which is referred to as a named instance.
Please note that you must also have sufficient permissions to connect to the desired SQL instance. This is something that you might do when you do a new installation of SQL Server or if someone else did the installation of SQL Server they would provide you the necessary permissions to connect to the SQL Server instance.
To connect to the Database Engine, open SQL Server Management Studio and in the Object Explorer, click Connect followed by clicking the Database Engine… option as shown below. This will open a new window which we will discuss below.
Use SSMS to Connect to the Local Default Instance
As mentioned above, you connect to a specific instance of SQL Server. If you are working on the machine, such as your desktop or remote into a server, this is considered the local instance. In addition, when installing SQL Server if you don't give it an instance name it is considered the default instance and can be accessed just by the machine name and if you are on the local machine you can just use a dot (.) to specify the local default instance.
In the Connect to Server window do the following:
- For Server Type it is Database Engine.
- For the Server Name, we can simply use a dot (.) which will connect to the local default instance of SQL Server.
- For the Authentication you can select Windows or SQL Server. If you select Windows Authentication it will try to connect using your Windows credentials and if you select SQL Server Authentication you need to enter your SQL Server Login and SQL Server Password.
- Then click Connect.
Use SSMS to Connect to a Default SQL Instance
If you are not on the local machine, but need to connect to a default instance on some other SQL Server in your network, follow the steps above, but in the Server Name just enter the name of the server you want to connect to.
Use SSMS to Connect to a Named SQL Instance
As mentioned, a SQL Server installation can only have one default instance, but it can have several named instances. So to connect to a named instance, the process is the same as above, the only difference is how we specify the Server Name.
The server name is broken into two parts: Machine Name \ Instance Name. So in the below example, we are connecting to machine HAROON (could be a desktop or a server) and instance SQLTAB2016, so we enter HAROON\SQLTAB2016.
Once you are connected, you can see the SQL Server instance name when using the Object Explorer in SSMS as shown below.
Last Update: 2/5/2020
