Connecting power bi service to SQL DB via on prem data gateway

[amit_official]

Hi Community,

I have on-prem data gateway and SQL managed instance onAzure.
I am trying to create new connection in Powerbi service (powerbi.com) using data gateway to reach SQL DB. 
I have create private endpoint from data gateway vnet to SQL DB. And added a DNS record too.

While testing connection in power service I get following error.

Details: <my-data-gateway-name>: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) <my-data-gateway-name>: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) Please have this information handy if you choose to create a support ticket. Session Id: <some session id> RequestId: <some request id> Cluster URI: https://api.powerbi.com Status code: 400 Time: <date time of error record>

Can anyone help to identify what am I missing with setup?

Thanks

Amit

[Poojara_D12 · Accepted solution]

Hi @amit_official 

The error you're seeing typically indicates an issue with the SSL/TLS handshake between the Power BI Data Gateway and the SQL Managed Instance over the private endpoint.

Here are a few things to check:

1. Firewall and Network Configuration: Ensure that the firewall on the SQL Managed Instance allows traffic from the Data Gateway’s IP address range. Verify that the private endpoint and DNS settings are correctly configured to route traffic through the gateway.

2. SSL/TLS Configuration: Make sure that the SQL Managed Instance is correctly configured to allow SSL/TLS connections. You might need to update the Data Gateway to the latest version if it’s out-of-date.

3. Certificate Validation: Ensure that the certificate on the SQL Managed Instance is trusted by the Data Gateway. If using a self-signed certificate, you might need to install it on the machine hosting the Data Gateway.

4. Network Security Groups (NSGs): If you're using an NSG, ensure that it allows the required inbound and outbound traffic to the SQL Managed Instance.

If everything checks out, try restarting the Data Gateway service, and test the connection again. If the issue persists, contact support with the session ID and request ID for further troubleshooting.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS  (360 remaining for 500 SUBS) Please support!!