Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
GrantB-WF
Regular Visitor

I am unable to create a direct query connection, but it works fine in import mode?

Hello, I am trying to create a dashboard with a Sql Query to a Microsoft SQL Server. When I  put the database and server in and the query and select direct query it is able to pull up the preview with the correct data. After I click load on the preview, It pops up with the following error. 

GrantBWF_0-1701204939350.png

Once I hit close it goes away and shows the wavy mess in the fields area until I click switch to import mode in the bottom righ hand corner. 

GrantBWF_1-1701206574740.png

 

3 REPLIES 3
v-nuoc-msft
Community Support
Community Support

Hi @GrantB-WF

I’m sorry to hear that you can’t create a direct query connection. If you can connect to a data source normally in import mode, but you encounter an error in direct query mode, there are several possible reasons:

 

Your data source version does not support direct query. Direct queries require SQL Server 2016 or later, you can check your SQL Server version by running the following query:

vnuocmsft_0-1701238756485.png

 

If your version is lower than 2016, you might need to upgrade your SQL Server or use a different data source that supports direct query.

KB4093226 - FIX: “DirectQuery may not be used with this data source” error when you browse a Direct ...

 

Another reason could be that there is a problem with your network connection or firewall settings that prevents Power BI from accessing your SQL Server. You can try to test your connection by using the Performance Analyzer tool in Power BI Desktop. You may need to adjust your firewall settings or network configuration to allow Power BI to connect to your SQL Server.

Troubleshoot DirectQuery models in Power BI Desktop - Power BI | Microsoft Learn

 

Or maybe there's an issue with your query syntax or formatting that causes Power BI to generate an invalid or unsupported query. You can use the Query Editor in Power BI Desktop to modify or refine your query.

 

Hopefully, these suggestions can help you solve the problem.

 

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thanks for your response,

 

The SQL server version is 2022. So any version errors do not seem to be the issue.

 

I don’t believe it is a firewall issue as I am able to setup direct query connections with other servers within the same network.

 

I know that the error for unsupported queries in direct query and this is a different error.
Also note, recently the database administrator updated these servers and then it began to fail. I have also found this error on some of the direct query dashboards that don’t work after the update.

GrantBWF_0-1701306054030.png
Could the issue be that something was left out of the update/install that needs to be there for Direct Query to work?
Could it be an issue that there is no SSAS installed and a power bi direct query connection uses it in the background?
Also just to be clear the connection is to a SQL server not a SSAS cube

I have kept doing reaserch could the issue be that I am using september 2021 report server and desktop but trying to connect direct query to Microsoft Sql Server 2022?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.