Sql Server database is divided in two, one physical part composed of database and log files and a logical part divided in Database Structures, tables, etc. Typically the physical database files can be found on a directory into the computer where Sql Server was installed.
On the other hand if you want to have access to the logical part of your database you need some tools called Database Management Systems (DBMS). One of those tools that belong to Microsoft is Sql Server Management Studio (SSMS).
Sql Server Management Studio gives you the possibility to access your database, it gives you the ability to administer and execute queries at your database using commands or if you prefer you can do that on a graphical way. SSMS gives you a great variety of tools to improve your data Management.
One Fascinating thing about SSMS is that you can create, delete or modify a database on a graphical interface, also you can modify, add or delete rows and configuring some parameter of your database without writing a single query. For example you can Import databases from Csv, txt or Excel files into Tables.
SSMS has a lot of functionalities that you will discover according to your needs. By using this tool, you can connect to different servers with different versions of SQL SERVER (2008, 2012, 2016, etc.) and execute queries on different Databases. I think that this tool is mandatory when developing and optimizing queries.
SSMS is installed by default when you execute Sql Server Installer (Unless you didn’t want to and uncheck the option). If you didn’t install SSMS or you want to access from other computer that is not the host, you can download Sql Server Management Studio from the following link.
1. Sql Server Management Studio Install
After you download SSMS from official Microsoft Website, install is easy all you have to do is to run installer and then click on “Install”.
On Location part try not to change it, default location is always the best option.
And then wait a few minutes to complete installation process.
2. Sql Server Database Engine login with SSMS
Sql Server local connection
If your Sql Server is installed in the same computer where your SSMS is located, login is easy. All you have to do is to choose the authentication mode that you configure during Sql Server installation.
You can logging multiple services using SSMS, if you want to logging into your database engine pick “Database Engine” on “Server type”.
If you installed Sql server on your local computer, your Server name is your IP Address (This can be obtained by typing ipconfig on Command Prompt console), or “localhost” to establish your local connection, followed by instance name (If you installed Sql server).
Your Sql Server Instance name would depend on your SQL installation.
- Default Instance. You connect to this instance by using only server name (ex. localhost or Sql server IP )
- Named Instance. You can connect to this instance by using [ServerName]\[Instance Name] (ex localhost\SQLEXPRESS)
If you use “Sql Server Authentication” you will have to introduce your logging Credentials (Superadmin user is “sa” and use the password established during installation).
In case you are using Windows Authentication, Sql Server validates the user and password using Windows credentials of the current Windows user using the computer to connect using SSMS. This means that Windows confirms the user and not Sql Server.
Remote Sql Server connection
If you are connecting from a remote computer to Sql Server, one of the first things you will have to do is to check if port 1433(Default Sql Server port) from Sql Server Host is reachable from your computer.
You have several options to check, the easiest one is trying to login with SSMS if it fails You can check if port is enabled for your computer using telnet on windows “command prompt”
First check if your telnet is working on cmd by typing “telnet” on cmd
If telnet is not enabled follow the next steps
- Go to Start > Control Panel.
- Go to Programs and Features.
- Click Turn Windows features on or off.
- In the Windows Features dialog box, check the Telnet Client check box.
You will have to wait a little until files are installed and configured on your computer. After finishing the installation process telnet will be ready to use.
After checking that telnet is available type on your Command Prompt
Telnet “SqlServerHostIP” 1433
In this example “192.168.11.129” is our host IP, 1433 is Sql default port (If you change it during installation change this number to the one you set).
If telnet connection success, you are going to see an empty cmd windows executing. if not you are going to receive the following message
If telnet is working for port 1433(Sql Server default connection port) that means that remote Sql Server is reachable, it is 100% sure that you will be able to connect. In case IP port 1433 is not reachable you will have to check your network connections and check if there is no firewall or other security configuration blocking your connection.
If you receive Connect Failed message you should check your net connection with sql server host, check your firewall or check if Sql server is enabling remote connections,
If you already installed SSMS and you are interested in enabling SSMS Dark Mode click on the following link: SSMS Dark Theme