How to access azure sql database from local machine

In this video, we will discuss how to connect and access azure SQL database from your local machine using SQL Server Management Studio or Visual Studio. 

SSMS cannot connect to azure sql database

By default you cannot connect to azure SQL database from your local machine. It doesn't matter whether you are using 

1. SQL Server Management Studio (SSMS)
2. Visual Studio or
3. Some other client tool

By default, the connection to azure SQL database will be blocked and you will see the following error message.

Your client IP address does not have access to the server azure. Sign in to an Azure account and create a new firewall rule to enable access.

This is for obvious security reasons. You don't want to allow everyone to be able to connect to your sql database in azure. If for some reason, you want to allow access to someone, you have to explicitly whitelist their IP address. You do this, by adding a new firewall rule.

Azure SQL database add firewall rule

Log into azure portal and navigate to your SQL Server instance.

Click on Firewalls and virtual networks under Security

The IP address of the machine from which you are trying to access azure sql database is displayed as client IP address

Click Add client IP button and then Save

Connect to azure sql database from SSMS

You should now be able to connect to azure sql database from your local machine using SQL Server Management Studio (SSMS). Make sure you provide the complete name of your SQL Server. You can find this in the azure portal on your SQL Server instance Overview blade

Connect to azure sql database from visual studio

To connect to azure sql database from visual studio, use SQL Server Object Explorer. To get to it, click on View -> SQL Server Object Explorer

Click on Add SQL Server icon.

Expand Azure node. You will see all databases in Azure. 

Click on the Azure database you want to connect.

Fill in the detaila (Server name, Authentication, Username and Password)

Click Connect