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

Reece Collett 1 Reputation point
2021-10-05T08:25:39.783+00:00

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?

Azure SQL Database
0 comments No comments
{count} votes

10 answers

Sort by: Most helpful
  1. Oury Ba-MSFT 17,866 Reputation points Microsoft Employee
    2021-10-05T19:07:10.887+00:00

    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...
    137893-image.png

    Please let us know if that works

    Regards,
    Oury

    0 comments No comments

  2. Robin Tremblay 1 Reputation point
    2021-10-06T00:05:11.637+00:00

    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)

    0 comments No comments

  3. Reece Collett 1 Reputation point
    2021-10-06T14:04:59.833+00:00

    @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)

    0 comments No comments

  4. Robin Tremblay 6 Reputation points
    2021-10-07T14:45:44.81+00:00

    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

    0 comments No comments

  5. Oury Ba-MSFT 17,866 Reputation points Microsoft Employee
    2021-10-07T16:51:51.763+00:00

    @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