Unable to connect to Azure SQL Server via Direct Query

[alohaes]

Hey everyone. 

I am facing this error in powerbi service whenever I try to open this report which is connected through Direct Query to a SQL Server in Azure. Because of this none of the visuals are loading. Far as I can remember, the same report a few days back was not throwing this error but then suddenly it starts doing so. Not sure why. 

Below is the snapshot of the error:

In dataset also, this error is showing up:

I would really appreciate any help on this. Do let me know if any more information is required.

[v-achippa]

Hi @alohaes,

Thank you for reaching out to Microsoft Fabric Community.

The error message shows that the issue is related to DirectQuery failing to connect to Azure SQL server, this could be due to network settings, authentication issues or dataset misconfiguration.

• In power bi service under Data Source Credentials, verify if authentication has expired, re-enter credentials and ensure the authentication type matches Azure SQL settings.
• Open the power bi (.pbix) file in power bi desktop, click refresh to check if the issue occurs locally. If it works in desktop but fails in power bi service, the issue is likely with cloud connectivity.
• Check firewall settings in Azure and ensure power bi service is allowed.
• Check if there are any schema changes in SQL server like missing columns, renamed table, invalid DAX measures or relationships.

After following these steps, please test the report again. If the issue persists, kindly provide additional details like any other reports using DirectQuery also affected, or any changes are made to the Azure SQL database schema recently.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[alohaes · Accepted solution]

Hi there, thanks for your help! I found out that the actual issue was related to a setting that we have for datasource connections called Use SSO via Azure AD for DirectQuery queries. This was unintentionally turned on and after switching it off, the data is now loading in my report.

For anyone else who might stumble into this issue, here are the steps for the above soln:

• Go to powerbi service settings(gear icon on top right)
• Go to manage connections and gateways inside the sidepanel
• Find your datasource for which you are getting this issue, click on 3 dot icon beside it -> settings -> uncheck the SSO checkbox

[freginier]

Hey there!

Azure SQL has a firewall that might be blocking Power BI Service’s IP addresses. If the Power BI service IP range changed, the connection might fail.

To fix this, try: 

- Go to Azure Portal → SQL Server.
- Navigate to Networking → Firewall and Virtual Networks.
- Ensure "Allow Azure services and resources to access this server" is enabled.
- Add Power BI IP addresses from this list: Microsoft Power BI IP Ranges

The problem also may be specific to Power BI Service.

Fix:

- Open Power BI Desktop.
- Try reconnecting to the same Azure SQL Server using DirectQuery.
- If it works in Desktop but not in Service, the issue is with Power BI Gateway or Service Credentials.

Hope this helps!

😁😁