Azure VM won't connect to Azure SQL DB with vnet rule and service endpoint

1
votes

We have a VM with a vnet containing a subnet (call the subnet data-subnet) with the Microsoft.Sql service endpoint in eastus2 region.

In the same subscription we have an Azure SQL Database in the same region in a different resource group with a vnet rule using the data-subnet mentioned above from the VM's virtual network.

When we try to connect to the SQL Server from the VM we get this error message:

Invoke-Sqlcmd : Cannot open server 'yada' requested by the login. Client with IP address 
'137.yada.yada.yada' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or 
run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.

But we already added the vnet subnet rule to the sql server for a subnet that the server already has. It almost looks like it is trying to connect to the SQL Server using a public IP address rather than the subnet. Indeed the subnet AddressPrefix starts with 172... not 137.

We have also made sure the vm, vnet, and sql server are in the same region.

We have successfully set this up in Azure with other VMs using a completely different subscription, but this VM has more than one subnet (under one vnet), which we haven't tested previously.

Any ideas as to what we need to do more?

1
sql-serverazureazure-sql-database
Double check the steps outlined here(Quickstart: Configure an Azure VM to connect to Azure SQL Managed Instance) to see if there is something you've missed. If you have it successfully setup else you could generate an ARM template from the existing configuration, then deploy that. Once you have your reference configuration deployed, make one change at a time to work towards your desired configuration, that should make it easier to isolate where the issue is. – Matt Stannett
This is Azure SQL Database not Azure SQL Managed Instance. We have followed all of the steps for that afaik. We could make the arm template from our working config, so we can take a shot at that, thanks! – Daltin
Also you could see if a static IP on your VM and a firewall rule on the Azure SQL Server instance works - would be quicker to isolate the problem than the ARM template approach. There is a guide for this here. – Matt Stannett

1 Answers

0
votes

I've create a same test and there is no problem. I can connect to the Azure SQL with SSMS in my vm.

My Azure SQL server configuration is as follow, is there any difference? enter image description here