I want to use a azure SQL Database and want connect to it via ip-adress.

My current setup:

1. Azure Database e.g. "example.database.windows.net"
2. Private Link in Azure Subnet connected with the database (10.231.1.5)
3. Azure Win VM (10.231.1.4)

When I open SSMS on my Windows VM, I can't connect to the private link database via IP-Address. It only works with the FQDN. The error message is "Cannot open Server "10.231.1.5" requested by login" Does somebody knows why?

In the future I want to use a P2S VPN to my local Subnet, there I dont have these Azure DNS entries. Is it possible to make a Azure Database work only with the private IP-Address? Otherwise I have to tell the IT-Support that they have to configure internal DNS to use a Azure DNS for Zone *.windows.net. Is there a best practice how DNS Zones can be linked to local?

I don't want the database to be reached via public IP, so all connections must use the private link.

CodePudding user response：

I tried to reproduce the same in my environment I got the result successfully like below.

Check the Note from MsDocs:

Use the Fully Qualified Domain Name (FQDN) of the server in connection strings for your clients .database.windows.net). *Any login attempts made directly to the IP address or using the private link FQDN .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.

To connect with private link database, try to add virtual network subnet

In your sql server -> Networking -> Disable Public access like below:

Try to create a new private endpoint with Microsoft SQL/server like below:

Try to login your vm into remote desktop install SQL server management studio

Now, when I try to connect with my private ip address it connected successfully like below:

In the future I want to use a P2S VPN to my local Subnet, there I don't have these Azure DNS entries. Is it possible to make a Azure Database work only with the private IP-Address?

You can make use of my previous Thread Using Azure Point-to-Site VPN, you can access the Private endpoint enabled Azure SQL Database

CodePudding user response：

I found the solution in this article: https://ourcloudnetwork.com/sqlerrornumber40532-unable-to-login-to-azure-sql-db/

It's mandatory to specify the Server Name. So if i want to use only IP Address I have to specify ServerName in user field e.g.

Server: 10.231.1.5 user: myDbUser@my-example-sql-db

when i specify the name in the user field with an @ then it works like expected