Send data to disable public access Azure SQL Database

Question

Hi

What i want to accomplish:

Send data through azure with a refresh rate of 2 seconds too a power bi report.

Java script sending on prem data via event hubs using stream analytics collected in an azure sql database in an azure sql server, all with private access through a virtual network. The Vnet is in a different resource group. I have two subnets for the vnet, one for eventhubs and one for the sql server.

Problem:

I manage to send data through the eventhub private access by the vnet using the FQDN-name in the script. But, when i want too disable public access in the azure sql server and using private endpoint connected too the vnet it does not work.

I have used both the privatelink-address for the server and the regular server name. Both manual entry and automatic. When using the private link the login is ok, but the error is then SSL-related.

Error message with privatelink-server name:

Querying SQL Database returned an error: 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.).

Any ideas?

BR

Petter

Answer 1

Hello Petter Cavallin,

Welcome to the Microsoft Q&A forum.

Based on the error message you provided, it seems that the issue is related to SSL certificate validation. When you use a private endpoint to connect to an Azure SQL Database, the SSL certificate presented by the server may not match the hostname used to connect to the server. This can cause SSL certificate validation to fail.

If you are using a private link FQDN, you may need to configure your client to trust the server certificate. This can be done by setting the TrustServerCertificate property to True in your connection string of the java script

Ex: if you want to use *.privatelink.sql.azuresynapse.net to connect to server instead of *.sql.azuresynapse.net, you need to trust server certificate

You can test this part using SSMS

The issue was resolved after checking the trust server certificate

I hope this helps. Please let me know if you have any further questions.

Answer 2

Hi! Thanks for your answer1 I didnt get a notification when you answered

I'll look into your suggestion but im quite sure we have that box checked

Will get back to you shortly!

BR

Petter

Answer 3

Or actually its more how do i control the "Trust certificate" when using eventhub with SQL source? do i access this from SSMS and in that case how?

Answer 4

Okey so you mean that updating TSC in the javascript the connection to the SQL will work?

Are you aware of the flow?

Javascript sending data from local server -> azure eventhub -> eventhub input stream analytics -> sql database output stream analytics -> azure sql database -> Power BI