Power BI fails to connect to SQL Server in Direct mode while Power Query does connect and show data

[gvg]

Hi,

I have configured a table in Power Query that connects to SQL Server table in Direct mode. I see data extracted in Power Query window. However when I Close & Apply I end up with an error message that says "We couldn't connect to your DirectQuery data source or internal model. Double-check that your server and database names are correct, and that you have permission to access them.". Does connection in Direct mode require some special permission other than Import mode ?

[Anonymous]

Hi @gvg 

@lbendlin Thank you very much for your prompt reply and here allow me to share some of it.

The DirectQuery mode does have different requirements compared to the Import mode, where permissions are needed to allow live data access and querying.

Please try the following steps:

Ensure that the account used to connect to SQL Server not only has the permissions needed to read data, but also the permissions needed to perform queries.

Double-check the server and database names you entered, as indicated by the error message. Misspellings or incorrect instance names may be the cause of connection problems.

Make sure there are no network problems or firewalls blocking the connection from Power BI to SQL Server.

Try opening Power BI Desktop as an administrator.

Regards,

Nono Chen

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

[gvg]

@Anonymous  Thank for your tips. However I failed to find role allowing to perform queries only. Should Write permissions be granted in order to be able to use Direct queries? On the other hand is role db_datareader not sufficient to use Direct queries in Power BI?

[Anonymous]

Hi @gvg 

The "write" permission is not necessarily required. db_datareader role is sufficient for direct querying, as it allows read-only access to the data source.

However, if you need to modify the semantic model or administrative privileges, you need the administrator or member role in the workspace where the semantic model resides.

Regards,

Nono Chen

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

[gvg]

OK, I have db_datareader enabled. I can connect to my data source in import mode. However when I change it to Direct mode, it does not connect anymore.

[lbendlin · Accepted solution]

You cannot change from import mode to direct query. You have to start over.

[lbendlin]

That should work.  If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .