From working on few support cases with our customers. I noticed some users report error message listed below. Unfortunately, I didn`t come across public document that explain what this error means and how users can investigate and resolve this error.
Therefore, I thought it will be useful to share it, so that you can resolve it by following the steps below if you encounter this issue.
Scenario: Customers get the following error message while trying to connect from Azure VM to Azure SQL Server.
ERROR MESSAGE
Cannot open server 'azuresqlserver1' requested by the login. The login failed. (.Net SqlClient Data Provider)
Server Name: azuresqlserver1.database.windows.net
Error Number: 40532
Severity: 14
State: 1
Line Number: 65536
This normally happens because you cannot logon to the Azure SQL Server requested by the login, likely due to missing virtual network rule for the source subnet.
INVESTIGATION
To investigate the issue, you need to check Azure SQL Server met 2 conditions.
1) Public network access enabled for select networks
2) The source virtual network assigned to the VM not listed
if both conditions met then this error message is expected.
On Azure VM check the virtual network and subnet settings as shown below and notice if the same virtual network and subnet already added to Azure SQL Server in select networks.
In our scenario the virtual machine configured with virtual network named “virtualmachine_group-vnet” and subnet “default”.
MITIGATION
Add the virtual network rule for the originating subnet in the ‘Networking’ section to Azure SQL Server “Networking” section in public access.
After adding the virtual network to Azure SQL Server, you should now be able to log on to azuresqlserver1.
TAKE AWAY
When users trying to connect to Azure SQL Server from Azure VM, and that VM had VNET\Subnet enabled. It will take the internal backbone route that the Azure SQL Server recognize this traffic are originating from the source virtual network and subnet assigned to the Azure VM.
On Azure SQL Server, if the firewall does not have the VNET\Subnet enabled on the Azure SQL Server, the connection then blocked.
Thank you for reading! If you have any comments or questions, please leave them below and I will do my best to reply to everyone.