Azure SQL Elastic Jobs connecting to a private endpoint doesnt seem to work due to SSL. Is there any way around this?

Question

I am trying to run queries against an Azure SQL Database daily using the Azure SQL Elastic jobs. The database is only accessible via the private endpoint.

When I try to target the databases public endpoint in the target group member options, it tells me it cant connect as the database public access option is set to Deny which I expect.

If I specify the private link though, the jobs give me this error:

Failed to connect to the target database: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Msg -2146893022, Level 20, State 0, Line 0)

There doesn't seem to be a way to trust the server certificate. Is there a way I am able to get around this error or fix this error?

Answer 1

Hi @Reece Collett Thank you for posting your question on Microsoft Q&A.

You must use the FQDN to connect to Azure SQL DB as documented at https://learn.microsoft.com/en-us/azure/sql-database/sql-database-private-endpoint-overview#check-con...

Please let us know if that works

Regards,
Oury

Answer 2

I have the same problem. My database is only accessible via the private endpoint.

The Ping command of servername.database.windows.net return ip adress 10.236.X.Y (that's good!)

I am using FQDN (servername.database.windows.net) and and it doesn't work.

I got error :

Failed to connect to the target database: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://learn.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://learn.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database). (Msg 47073, Level 14, State 1, Line 65536)

Answer 3

@Oury Ba-MSFT , Thank you for your reply.

Like @RobinTremblay-3022 mentioned above, using the servername.database.windows.net results in this error:

Failed to connect to the target database: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://learn.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://learn.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database). (Msg 47073, Level 14, State 1, Line 65536)

Answer 4

Thank you Felix for the rectification. indeed you had mentioned that there had to be tests before confirming that it is a bug or a bad configuration in my azure environment. So sorry.

Robin

Answer 5

@Reece Collett @RobinTremblay-3022

Could you please check name resolution and port
NSLOOKUP SERVERNAME.database.windows.net
Test-NetConnection -Port 1433 -ComputerName SERVERNAME.database.windows.net

Let us know the results.

Regards,
Oury