Forum Discussion
Power BI fails to connect to SQL Server in Direct mode while Power Query does connect and show data
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 ?
You cannot change from import mode to direct query. You have to start over.
6 Replies
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 .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.
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?
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.
