Azure SQL is a PaaS regional service and by default it is accessible over the internet from anywhere. So, why do we want to connect to Azure SQL over private connection and completely close the internet route? The sort answer is- minimize the risk of exposure. Let’s summarize our requirements-
- Azure SQL Databases can’t be accessible over internet connection, period. Deny public network access setting is set to Yes under the firewall section. This is done to reduce the radius of attack surface.
- Use Azure Active Directory user to connect to the databases. No SQL user please. We need to be able to govern the users centrally.
- Use TDE with customer managed key to encrypt databases backups, and logs at rest without any changes to your application. We do this to comply with data is encrypted at rest.
- Logging and Monitoring: Turn on auditing to capture database or server access logs. Yet another important audit requirement.
- Databases can be accessed from Azure vNET’s and optionally from on-premise over VPN (P2S, S2S, ExpressRoute) connection.
We need a drawing to depict the architecture that would support our requirements-
Azure PrivateLink is at our rescue. Azure PrivateLink is still in preview as of this writing and you should pay caution if you are planning for production loads. Microsoft is quickly catching up to similar capability available in AWS and, hopefully, it would not be too long before PrivateLink become GA. You can do two things with PrivateLink- create private endpoint to existing Azure regional service (this is the case for our requirements) and create privatelink service to expose your own service.
Without further delay, let’s create an Azure SQL database and configure the settings based on what we agreed in the requirements. Azure Advance Security is another important security feature but it’s not in our requirements at this time.
As promised, we will update the firewall setting to close the connection from internet.
Update the Transparent Data Encryption setting to use Customer Managed Key from Azure Key Vault.
When you create Azure SQL server, you are required to create a local account. This is our break-glass account but we would not use this account for normal operations. Our requirement is to use Azure AD users to connect to SQL database. To do so, we need to set an admin account by selecting a user from Azure AD. It’s important to note, this is the ONLY account that can create other AAD based users in Azure SQL.
For private connectivity, we would need to create a private endpoint to Azure SQL from our vNET/subnet using PrivateLink.
Private endpoint needs to resolve to a DNS and, for that matter, we would need to have Private DNS prior to creating private endpoint. I already have the private dns zone and we just used it. Please note, we
Almost there! At this time, you can connect to sql database from Azure vNET but we need some extra work before we can connect from on-premise. nslookup to the database on my local machine returns public ip, dns, private endpoint, etc.
nslookup aspnet4you1.database.windows.net
Server: UnKnown
Address: 192.168.1.1
Non-authoritative answer:
Name: cr2.eastus1-a.control.database.windows.net
Address: 40.121.158.30
Aliases: aspnet4you1.database.windows.net
aspnet4you1.privatelink.database.windows.net
dataslice2.eastus.database.windows.net
Regardless of the location you are accessing the database, you would use public dns (aspnet4you1.database.windows.net) always. I ran into issue connecting from on-premise over Point to Site VPN (same would be true for ExpressRoute or S2S VPN). It’s because you can’t resolve to private endpoint IP address, 172.240.17.4 in my case. Found the solution at thewindowsupdate.com. I used a shortcut, updated my local host file to add an entry to resolve the dns for Azure SQL.
172.240.17.4 aspnet4you1.database.windows.net
As you can see, I am not using private endpoint dns (aspnet4you1.privatelink.database.windows.net). You will meet with the following error if you try to use private dns. It’s by design, we turned off the public route in the firewall!
TITLE: Connect to Server Cannot connect to aspnet4you1.privatelink.database.windows.net.. ADDITIONAL INFORMATION: Reason: An instance-specific error occurred while establishing a connection to SQL Server. The public network interface on this server is not accessible. To connect to this server, use the Private Endpoint from inside your virtual network. (Microsoft SQL Server, Error: 47073) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=47073&LinkId=20476 BUTTONS: OK
Now, we need to create a login to access the database from on-premise. We have to connect with the AAD account we set as admin.
Pick the AAD user account and run the command like the one below
CREATE USER [bob@aspnet4you.com] FROM EXTERNAL PROVIDER;
Finally, we are ready to use ready to login as Bob the Azure AD user! You need the latest version of SQL Server Management Studio to see Azure Active Directory – Password option.
Yahoo, we can access Azure SQL from on-premise using Azure AD account! Enterprise customers can’t use host file based DNS resolution and you have to use your on-premise private dns instead. Be sure to open up the firewall to allow sql port (tcp:1433).
Hope this post was helpful for you. For questions or comments, feel free to connect me over LinkedIn.