Forum Discussion
Unable to connect to Azure SQL Server via Direct Query
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
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
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