Unable to connect to Azure SQL when Deny public network access is enabled

Question

I am working on trying to figure out how to deny public access to Azure SQL and only be allowed from specific virtual networks.

I create a test Azure SQL server and then created a test VM. Checked the box to Deny Public network access. Added the VNET of the test VM under virtual networks in the firewalls and virtual networks tab. I then peered the VNET of SQL and the VNET of the VM

If I use SSMS and try to connect to the server name xxxxxx.database.windows.net I get an error:
Error occurred while establishing a connection to SQL server connection was denied since Deny Public Network Access is set to yes.

If I use SSMS and try to connect to the private link. xxxxx..privatelink.database.windows.net. I get an error:
A connection was successfully establish with the server, but then an error occured during the login process. The target principal name is incorrect.

Those are the only two ways I would know how to try and connect.

If I go into the private endpoint DNS configuration and telnet from that VM to the internal IP address, that does work. So I would assume there is a connection between the two networks.

Any help would be great..
Thanks

Answer 1

I resolved it by following below steps
go to your Sql Server --> Security --> Networking --> Public Network Access --> Selected Networks

and Add Your Client Ipv4 address & even Add a Virtual network rule if necessary

Answer 2

Hi JeffFazio-1428,

I think the issue is the with the private link the dns name of the server is different and tls connection is not trusted.

Below is a tutorial on how to setup private link for azure sql server:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-private-link-private-endpoint-connectivity/ba-p/1235573

Hope this helps!

Answer 3

When I try to use the private link, I am getting an error
A connection was successfully established with the server but then an error during login process Azure SQL
The target principle name is incorrect.
I'm just using the FQDN that's under the DNS configuration. Is it supposed to be something else?

Answer 4

@Jeff Fazio I see that you are using the private link FQDN from SSMS and the connection is failing. Please check the Note below:

Use the Fully Qualified Domain Name (FQDN) of the server in connection strings for your clients (<server>.database.windows.net). Any login attempts made directly to the IP address or using the private link FQDN (<server>.privatelink.database.windows.net) shall fail. This behavior is by design, since private endpoint routes traffic to the SQL Gateway in the region and the correct FQDN needs to be specified for logins to succeed.

Please let us know if you have any further questions and we will be glad to assist you further. Thank you!

Remember:

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.