Connecting SQL database though SQL Server Management Studio (SSMS)
For Connecting SQL Database through SSMS in Azure:
The following prerequisites are required for connect SQL Database through SSMS are
👉 SQL Database: Azure SQL Database is a cloud based Relational Database service which has all the features like Microsoft SQL server with added scalability and flexibility.
👉 SQL Server Management studio: SSMS is an windows application and graphical user interface to manage, configure and administer the SQL server.
👉 Firewall rules: It creates a firewall the server level for single and pooled databases.
Key Benefits of SQL Server Management Database:
👉 SSMS used to manage SQL Server database on local or remote instance of SQL easier. Server.
👉 SSMS not only create databases but also write SQL queries of any complexity faster and easier.
👉 SSMS can generate t-sql scripts for almost all the functionalities.
👉 SSMS has a more robust feature set than MYSQL Workbench.
Connecting SQL Database through SSMS:
Step-1: Login into the Azure portal and search for SQL databases in the search bar, then Click on the SQL databases.
Step-2: In the SQL Databases, you can view all the databases that are already created. If you want to create a new database you can click on create.
Step-3: In order to connect to the SQL Databases, you need to download SSMS SQL Server Management Studio.
Search for ssms download in your browser and download it.
Then click on the Download SSMS application for windows. Install the software and open SSMS application.
Step-4: Now SSMS login page will open. You will get 3-options to login, by default it will select Database engine in server type.
1. Server type: it contains 5 types of services
Database Engine: It is used to store, manage data from the database.
Analysis service: It is used to show the data in multiple dimensions.
Reporting service: It is used for generating reports in various formats.
Compact Edition: It is used to develop mobile applications.
Integration services: It is used to convert one data format into another, SQL to oracle.
2. Server name: It is a machine name/computer name.
3. Authentication: provide the username and password to login for security access.
Step-5: Now enter the server’s name from the SQL database that you created from the azure portal, and enter login and password credentials that you provided while creating the SQL database in Azure.
You can see the status of database is paused over above diagram, because we did not connect to the SQL database through SSMS yet.
Step-6: After entering the Server name and login credentials, go to connection properties beside the login tab, in that it will ask connect to database name. Database name can be obtained from SQL database that you created in the azure portal.
Step-7: In order to get database name, go to azure portal select SQL database, click on the SQL database. Now you will see the databases and servers in that select and click on SQL server.
Step-8: In the SQL server testrecords databases, you will see testdb in Database, select the testdb name and copy.
Step-9: Now got to SSMS connection properties and enter the database name that you copied from azure portal. Now click on connect button.
Now you can see the error establishing connection, this is because of firewall rules which were not added.
Step-10: Go to azure portal and select SQL database. You will see SQL server, click on it. In that go to Networking tab and add the client ipv4 address and add firewall rules. Now add the default client address and firewall rules and save that rule.
Step-11: Now go to SSMS SQL server login page, then click on connect button.
Now the SQL databases are successfully connected through SSMS. Now you can view that right side which is shown below.
We can test the SQL databases in Azure portal showing status available and online. That means We have successfully configured the connecting SQL Database through SQL Server Management Studio (SSMS).
Author : Chandra Shekar