This is a part of a series “Stairway to being an Azure SQL DBA“, which is one stop learning for becoming an Azure SQL DBA.
Once you have deployed your SQL Database and you have got the notification of “Deployment succeeded” you must know how to connect to the database and SQL Server.
For making the connection to the Azure SQL Database we will look into two different scenarios:
1. Making the connection from a resource inside the Azure Network (like a VM) or for the VMs in your on-prem when you have VPN/ExressRoute in place.
2. Connecting from outside the Azure network, like your local workstation.
To connect to your SQL Server you need to know it’s “Server name”. You can find it by going into the SQL Server resource.
So for me I need to connect to sssqlserver101.database.windows.net when making the connection from SSMS or when using it in the connection string.
Connecting to Azure SQL Database from Azure VM
Before starting to make the connection to your SQL Server the important thing to know is that Azure SQL Database is not created inside a VNet (Virtual Network) that you have in your Resource Group (it’s a logical container of all your resources something like Filegroup is for files in SQL Server). But instead it’s one of those resources that reside on the Azure backbone network.
I have the below resources in my Resource Group.
I have a Virtual Machine named ssvmtest, SQL Server named as sssqlserver101 and the database database1. The rest of the resources are for the Virtual Machine and can be ignored for now.
I have also installed SSMS in the VM.
When I try to connect to my SQL Server from inside the VM it gives the below option.
What it is trying here is to let you Sign In to your Azure account and if you have the permission then will add your machine public IP into the SQL Server Firewall setting.
This method can also be done but we can leverage the Networking options available inside the Azure as this VM is inside the Azure environment.
Go to your SQL Server and then to the tab “Firewalls and virtual networks”. And then make “Allow Azure services and resources to access this server” to Yes.
Once you choose it Yes and save the changes you will be able to make the connection to your Azure SQL Server from your Azure VM.
I set the “Allow Azure services and resources to access this server” back to no.
The drawback of option 1 is that you have made it available to all the Azure services then all the resources CAN connect to your SQL Server. They will still need the access to your database and all but it’s not the security best practice.
Instead of allowing all the resources to be able to connect to your SQL Server you can limit the connection to a specific Virtual network. This VNet is going to be the one where your VM resides.
Currently I have not added any VNet but if you click on the “+ Add existing virtual network” you will get the option like below.
I have added the VNet which has the VM, as you can see above I am getting a prompt stating:
Selected subnet does not have service endpoint enabled for Microsoft.Sql. Enabling access may take up to 15 minutes to complete.
Service Endpoints provides a secure and direct connectivity to Azure services over an optimized route over the Azure backbone network.
Once you have selected your VNet click Ok. Once completed you will be able to see the connected network which was empty before.
Now my setting look like below
When trying connecting to SQL Server from inside the VM I am able to connect to it flawlessly.
Option 3 and Connecting from outside the Azure network, like your local workstation
The third option to connect our Azure VM is same as when you are connecting from your local workstation.
For doing this we need to add the Public IP address of the Azure VM or your local workstation in the same page.
Once you have added the IPs of the users who wish to connect to the SQL Server click Save.
I am able to connect from the Azure VM.
And similarly I am able to connect from my local workstation.
Till now we covered how to connect to your SQL Server using different options available at SQL Server level.
For connecting to a specific database you can also use Database level Firewall settings which is very much similar to Server level firewall settings but only scoped to the database and can be done by using T-SQL only.
EXECUTE sp_set_firewall_rule @name = N'DatabaseRule', @start_ip_address = '192.168.1.1', @end_ip_address = '192.168.1.1'
The priority of Firewall settings on SQL Server level and database level is as below.
That is all for today!