Connect to Azure SQL Database from Azure or outside of Azure

In this post I want to talk about connectivity to Azure SQL Database, that’s one of the most common issues that normally users have. In this post I cover Public IP address and how we can access to Azure SQL Database from Azure VM or our of Azure environment.

Let’s start with the error message which you might receive:

Cannot open server 'mytestserver' requested by the login. Client is not allowed to access the server. (Microsoft SQL Server, Error: 40914)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-40914-database-engine-error 

You probably think I have access to the database what’s wrong? HereMicrosoft explains about firewall and connectivity but normally the first question from Azure SQL engineer would be where is your machine that you (or your application) try to connect?

(1) Connect from Azure VM:

Let’s assume you’re in Azure VM then you need jump to the portal check the “Server firewall” and see if you check “Allow Azure Service and resources to access this server”

That’s easiest way, if you use any application that would be great if you try to connect to the database from same machine with SSMS if that’s possible.

Instead of “Allow Azure Service and resources to access this server” Can I add the VM IP address?

Like screenshot1 you can go the SQL Server page in Azure portal and from “Set server firewall” then clin on “Add a virtual network rule”, you’ll have the windows like the below screenshot, and you can select the subnet which you already added for that VM.

Then go back to the VM and try to connect, you’ll see everything is fine!

(2) Connect outside the Azure VM:

Let’s assume you have “Public access” and the source is not in VM Azure, so you need to add the IP address into the Firewall rules list, You can do that from Azure SQL Portal like the following screenshot:

Or you can open SSMS from anywhere which you have access and run sp_set_firewall_rule command. I'll talk about this later but you can see the referral link

What happens when you ticked “Allow Azure Services and resources to access this server”?

You can run the following command in SSMS and see the rules which has been added in Firewall rule, as you see behind the sense Azure added 0.0.0.0 in the table.

 select * from sys.firewall_rules

I can delete any of the rules including that one with the following command (of course we can do that from Azure Portal)  See the referral link for the TSQL Command

EXEC sp_delete_firewall_rule N'Rule Name'

In this case if I back to the portal and see the “Allow Azure Service and resources to access this server” that’s untick now, that means we can’t connect to the Azure SQL Server from Azure VM.

Here is the command which I ran in my lab and I got access from my laptop to Azure SQL Database. You only need have access to database and add another IP range to the firewall rule list.

 
-- Enable Azure connections.  
exec sp_set_firewall_rule N'My Laptop', '192.172.1.0', '192.172.1.0';  

I hope I can simply explain how you can connect to Azure SQL Database and resolve Error: 40914. I'll try to talk about private access later.