Azure SQL Basics - Connecting to Azure SQL DB using SSMS

Note that this post is a part of the series. You can view all posts in this series here: Azure SQL and Data Factory Basics - Index

Connecting Azure SQL DB using SSMS

In this post, we will be showing how to connect SSMS (SQL Server Management Studio) to Azure SQL databases. Launch SSMS and the "Connect to Server" popup should open up automatically. Azure doesn’t support Windows Authentication. So you will need to log in using SQL server authentication with the username and password created in the Azure portal for the server or via AAD options.

Before login, you will need the connectivity details and to get that follow the below steps.

1. Get the server details to which you will connect. Make sure you get the server name instead of the database name. Sometimes people get confused. The server will have the naming convention as servername.Database.windows.net
2. Set up the server firewall in the Azure portal. Otherwise, you will not be able to connect with the server. It will throw an error.

To set up the Server Firewall: Go to all resources and select SQL Databases, it will show up all the databases you have created in your subscription.

Select the database to which you want the connectivity. Let’s take an example of the selected database below. Once you click on your database (test01 in the example below), it will open up the blade for that specific database. Go to the "Set server Firewall" option as selected.

Here you can add the IP ranges and/or specific IP addresses from where you want the access allowed to this Database. In the below example, we are adding the Client IP (i.e. the IP address of the client from where you are accessing the Azure portal.

By default, there will be no IP added under the Client IP address section. Once you click on step 1, then it will populate the IP address of your machine. In the large organization, you can set up the start and end IP.

Note that there is a radio button to allow Azure services and resources to access this server, this will ensure the azure services can monitor your database performance to do analytics for the diagnostic operations. Once you click Save, you should be able to connect the Azure SQL databases using SSMS.