Overview
This chapter shows how to use SSMS to connect to the main component of SQL Server, which is the Database Engine.
Explanation
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 perform 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 example below, 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.
Haroon Ashraf’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.
He holds BSc (Gold Medal) and MSc Degrees in Computer Science and also received the OPF merit award.
His programming career began in 2006 working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Business Intelligence and Database Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration.
He has recently earned “Knowledge Management and Big Data in Business” certificate from The Polytechnic University of Hong Kong.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2020 | Author of the Year Contender – 2018-2020
When i use a “.” fir Server name, it just shows an error: TITLE: Connect to Server
——————————
Cannot connect to ..
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-2-database-engine-error
——————————
The system cannot find the specified file
——————————
BUTTONS:
OK
——————————
Hello. I’m from Russia and my English is very bad? but I want to hel you.
You need use name your local server. You can see/look/prove it at TAB Browse – Local.
At first you click Conect followed by clicking the Database Engine… option, as shown below.
This will open a new window, which we will discuss below. You can see two TAB in the window: History and Browse. You need click Browse and click Local/ You will see name your server. It was written as ……/….., you need to click it. That’s all. Please, sorry for my bad English.
AMAZING!
Thanks 😊
Thank you and very helpful
Very helpful
Hi Ofonmbuk,
what is the error you are getting?
Please, I have done all this, but can’t still connect to the server, it’s giving me error message.
Very Helpful
Thanks a lot!