Connect your on-premises SSMS to Azure SQL DB over a VPN

Now that’s a title! Here’s the problem I’ve ran into.

Setup

Customer has an Azure environment with an Azure SQL Server database that can be reached through a private endpoint. There’s a VPN in place to connect on-premises networks with the Azure VNet and the private endpoint is in one of the subnets, the VPN is in a peered network with it’s own subnet. Using a VM, we can connect to the Azure SQL Database via the private endpoint (the connection shows the internal IP address). But connections from the on-premises environment keep going over the public internet and show the external IP from the on-premises connection.

Problem

To make sure the on-premises connection uses the VPN and the private endpoint, we need to make sure the on-premises DNS (it’s always DNS) recognizes the traffic and redirects it to the VPN connection. But whatever we tried on the firewall, the traffic kept going the wrong way. It did have something to do with the on-premises DNS setup in the end.

When we tried to connect to the Azure SQL instance on IP-address, it threw an error because the instance wasn’t found. You can only connect to it with the FQDN (dbname.database.windows.net)

Solution

We ended up creating a new A-record in the on-premises DNS with a local name. This local name (database.company.local) redirected traffic to the private IP (10.1.1.1). But, I just said that doesn’t work. And it doesn’t.

Until you read a little further and notice that you should change your login name to dbuser@dbname.database.windows.net. When you try that, the connection succeeds and the connection gets established.

I hope this little blog helps you out when you run into this problem.

Thanks for reading!