Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Report couldn't access data source in Direct Query mode

  I've created a simple report with PBI desktop. In desktop all works fine. When I open the report in the service i get the following error: Report couldn't access the data source because it doesn...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous I would assume that the credentials can access the SQL Server, otherwise you wouldn't be able to use the datasource. My thought is that while it can reach the SQL Server, it can't access all the tables/views that it needs to in order to produce the report results... what level of permission does it have on the SQL Server? Is it a different account, or your credentials?

    Alternatively, did you use a basic auth connection in the Desktop?

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Are you sure you use the same credential in gateway and in desktop file to connect to data source? Could you log in SQl Server in SSMS with this credential and view all wanted tables and columns?

 

Regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Yes i can login with a local user account (windows authentication) and the SA account. I can view the tables. 

    I tried both in the gateway. No luck there

    Can this be due to any restrictions with DirectQuery?

     

    • AllisB's avatar
      AllisB
      Regular Visitor
      3 years ago

      Hi, I got the same issue. I have resolved it by click on Edit Query of the table you got from direct query. Then chose Recent Sources from the tab >> choose your database again on the left >> click ok on the table >> Choose import in Connection settings (this will create a copy into power bi from your direct query). A new Query will be created as a copy of your direct query table. It's will be linked and updated when data in your database changed. You can create visualizations from that query. Hope this help!