SQL Managed Instance as data source for Power BI dataset - Connection Fails

Question

SQL Managed Instance as data source for Power BI dataset - Connection Fails

Aayush Suresh Jain 146

I have a SQL database created in Azure SQL Managed Instance. I created a Power BI dataset and report from Power BI desktop and published the same to the Power BI portal. I am able to connect to the SQL database from my laptop.

However, on the power bi portal, when I try to connect to the source, I get the following error :
User's image

The Azure SQL managed instance is under a virtual network and for that VNet, I have added a rule to the NSG that should allow PowerBI to connect to it.
User's image

On the networking for the Azure SQL Managed Instance, I have Public endpoint(data) option DISABLED.

I still receive the same error. What should I do to have the PowerBI data source working?

Aayush Suresh Jain 146 Reputation points

2023-08-28T14:16:43.11+00:00

@SSingh-MSFT This is the first time I am trying to connect and the I cannot enable the public endpoint option as mentioned in the Reference documents links. Can there be any other way to do it?
SSingh-MSFT 16,371 Reputation points Moderator

2023-08-29T03:56:43.35+00:00
Can you please confirm if below conditions meet:

Private endpoints to SQL Managed Instance can only be used to connect to port 1433, the standard TDS port for SQL traffic. More complex connectivity scenarios requiring communication on other ports must be established via the instance's VNet-local endpoint.

Private endpoints to Azure SQL Managed Instance require a special setup to configure the required DNS resolution, as described in Set up domain name resolution for private endpoint.

Private endpoints always operate with the proxy connection type.

Thanks
SSingh-MSFT 16,371 Reputation points Moderator

2023-08-30T03:47:23.67+00:00

Hi Aayush Suresh Jain •,

Just checking if you got a chance to look at the above comment, thanks
Aayush Suresh Jain 146 Reputation points

2023-09-08T10:04:45.2833333+00:00

Hello @SSingh-MSFT
We do not have a private endpoint created and the port 1433 is allowed access in the VNet rules. All the steps I see is asking me to disable networking which I cannot do it.
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-08T11:07:45.09+00:00

Hi Aayush Suresh Jain •,

This is strange and not expected. I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks
Aayush Suresh Jain 146 Reputation points

2023-09-08T12:00:16.6166667+00:00

Hello @SSingh-MSFT ,

I have an Azure SQL Server Database which has similar networking rules applied and that I am able to connect without any issues. I am having issues only with the Azure SQL Managed Instance. I have added a PowerBI service tag also in the inbound rules for the network.
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-12T03:55:31.8533333+00:00

Hi Aayush Suresh Jain •,

Thanks for the details.

This looks strange and I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks

1 answer

Your answer

Aayush Suresh Jain 146 Reputation points

2023-08-28T14:16:43.11+00:00

@SSingh-MSFT This is the first time I am trying to connect and the I cannot enable the public endpoint option as mentioned in the Reference documents links. Can there be any other way to do it?
SSingh-MSFT 16,371 Reputation points Moderator

2023-08-29T03:56:43.35+00:00

Can you please confirm if below conditions meet:

Private endpoints to SQL Managed Instance can only be used to connect to port 1433, the standard TDS port for SQL traffic. More complex connectivity scenarios requiring communication on other ports must be established via the instance's VNet-local endpoint.

Private endpoints to Azure SQL Managed Instance require a special setup to configure the required DNS resolution, as described in Set up domain name resolution for private endpoint.

Private endpoints always operate with the proxy connection type.

Thanks
SSingh-MSFT 16,371 Reputation points Moderator

2023-08-30T03:47:23.67+00:00

Hi Aayush Suresh Jain •,

Just checking if you got a chance to look at the above comment, thanks
Aayush Suresh Jain 146 Reputation points

2023-09-08T10:04:45.2833333+00:00

Hello @SSingh-MSFT
We do not have a private endpoint created and the port 1433 is allowed access in the VNet rules. All the steps I see is asking me to disable networking which I cannot do it.
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-08T11:07:45.09+00:00

Hi Aayush Suresh Jain •,

This is strange and not expected. I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks
Aayush Suresh Jain 146 Reputation points

2023-09-08T12:00:16.6166667+00:00

Hello @SSingh-MSFT ,

I have an Azure SQL Server Database which has similar networking rules applied and that I am able to connect without any issues. I am having issues only with the Azure SQL Managed Instance. I have added a PowerBI service tag also in the inbound rules for the network.
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-12T03:55:31.8533333+00:00

Hi Aayush Suresh Jain •,

Thanks for the details.

This looks strange and I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks

Answer 1

Hi Aayush Suresh Jain •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are unable to connect to Azure SQL Managed Instance from Power BI.

Could you please let us know if this is first time you are trying to connect, or this connection was earlier working and now stopped working?

Please refer to below points meet for your Azure SQL MI:

Private endpoints to SQL Managed Instance can only be used to connect to port 1433, the standard TDS port for SQL traffic. More complex connectivity scenarios requiring communication on other ports must be established via the instance's VNet-local endpoint.
Private endpoints to Azure SQL Managed Instance require a special setup to configure the required DNS resolution, as described in Set up domain name resolution for private endpoint.
Private endpoints always operate with the proxy connection type.

Note:

Azure SQL Managed Instance requires the connection string from the SQL client to bear the name of the instance as the domain name's first segment (for example: <instance-name>.<dns-zone>.database.windows.net). PaaS and SaaS services that attempt to connect to Azure SQL Managed Instance's private endpoint via its IP address will not be able to connect.

Reference documents: https://techcommunity.microsoft.com/t5/azure-sql-blog/integrate-microsoft-cloud-services-with-your-azure-sql-database/ba-p/1083543

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/

Thanks

Share via

SQL Managed Instance as data source for Power BI dataset - Connection Fails

1 answer

Your answer