How connect to Azure SQL Server from ADF integration runtime using private endpoints?

Question

Hello,

I am trying to set up an Integrated Runtime in ADF, I'd like it to create the SSIS catalog (SSISDB) in Azure SQL Server. I have public access blocked on the SQL Server and private endpoints in place. Also, I am using the express vnet injection method where I have a subnet delegated to Microsoft.Batch as per the documentation.

I can get it to connect to SQL Server using the full privatelink endpoint (e.g. my-sql-server.privatelink.database.windows.net), however, I get the following error:

Error code: AzureSqlConnectionFailure. Error message: Failed to connect to Azure SQL DB server due to sql error '-2146893022', message: 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.).

I presume this is happening because the privatelink hostname doesn't match the certificate SQL Server presents. This is described in the second bullet point here.

When I use the shortened URI without the .privatelink, the firewall on the SQL Server blocks it because it's using a public IP.

So it seems to me that the delegated subnet is resolving DNS differently that the other subnets in the VNet that uses my Private DNS Resolver. Because the subnet is delegated, I can't just spin up a VM in there to test.

Any help and/or suggestions would be appreciated.

Thanks

Answer 1

You can start by verifying that your Private DNS Zone is correctly configured and associated with your virtual network (VNet). The correct DNS resolution is crucial for ADF to connect to the Azure SQL Database over the private endpoint.

The DNS zone should resolve the SQL Server's private endpoint correctly ( my-sql-server.privatelink.database.windows.net).

From what I saw online, this error typically occurs because the SQL Server certificate is not matching the hostname in the connection. A common reason for this in a private endpoint setup is that the hostname in the connection string is not matching the one expected by the SSL certificate presented by Azure SQL.

The private endpoint DNS resolution should automatically route traffic to the private IP associated with the private link, and the SQL Server should expect a connection with the correct hostname (my-sql-server.privatelink.database.windows.net).

You also mentioned using Express VNet injection, which means the integration runtime will be placed into a subnet that is part of your VNet so you may need to check if this subnet has the necessary network configurations to route traffic to the private endpoint, and make sure the DNS settings are correctly inherited by the integration runtime.

Answer 2

Hi @Bruce Smith
Thanks for the question and using MS Q&A platform.

Private DNS Zone Resolution - You're correct that the successful connection using the long URI (my-sql-server.privatelink.database.windows.net) strongly suggests that your private DNS zone is generally configured correctly. It's resolving the fully qualified private endpoint hostname to the correct private IP address.

The issue lies in the shortened URI (likely just the server name without the .privatelink domain). The SQL Server's SSL certificate is likely expecting the fully qualified private endpoint hostname, leading to the "The target principal name is incorrect" error.

Unfortunately, there isn't explicit documentation on directly checking how DNS settings are inherited by the integration runtime within the delegated subnet. Microsoft’s documentation on VNet integration and private endpoints should provide useful insights.

Azure Private Endpoint private DNS zone values

Use private endpoints to create an Azure Data Factory pipeline

Provision the Azure-SSIS integration runtime in Azure Data Factory

Hope this helps. Do let us know if you have any further queries.