cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fabbimk
Frequent Visitor

DirectQuery reports not loading in PowerBI service

Hello all,

 

Recently we installed an on-premises gateway with the aim of sharing reports that are based on data stored in SQL server. However, when using DirectQuery we get the following error message:

Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, etc.

image.png

 

In the data source settings of the gateway, the credentials for the basic authentication method are provided. It indicates that the connection is successful, and the gateway status in the dataset settings also looks fine. 

This problem does not occur when the data connectivity mode is set to 'import', it only happens with DirectQuery. What is going wrong here? 

 

Please let me know if you have any ideas!

10 REPLIES 10
fabbimk
Frequent Visitor

Thanks for the replies!

 

Did some more research but our SQL server is on Amazon RDS. Is DirectQuery even a possibility then?

Hi there

I am not 100% sure, it would appear that it has to be a SQL Server source.

Can you configure it as a data source in the Gateway on the Power BI Service?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

It is currently configured with SQL server as data source type, which works when the data in the report is loaded in Import Mode. There is no separate option for Amazon RDS, only for AWS Redshift. 

 

I've found an open idea here that would probably cover what we want to achieve, seems to be a topic since 2014. 

Hi there

It must be because it uses some other way to connect when using DirectQuery

I would think it should work if it is seen as a SQL Server source?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

That's what we thought, but that resulted in the error message from my first post.

 

When ticking the SSO box as you mentioned earlier we get a different error by the way: "This report couldn't access the data source. Contact [my name], the author, to have it fixed"

Hi there

What happens if you use a SQL Server Account for authentication?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

It does not seem to make a difference whether Windows authentication or basic SQL server account is used. For both it says in the data source settings that the connection is successful, but when opening the report I get the same error message. 

Do you have the GatewayErrors log from the Gateway?  That will give you the definitive answer as to why your connections are failing.

 

The generic error message obscures a lot of other errors.  

 

For instance, if you have upgrade your AWS RDS certificate, but it's not installed on the client (gateway) machine, you will get this error, despite the data source settings test working.

 

In the log, you'll see: [1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: SqlException:<pi>System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) ---> System.ComponentModel.Win32Exception (0x80004005): The certificate chain was issued by an authority that is not trusted

v-eachen-msft
Community Support
Community Support

Hi @fabbimk ,

 

You could use IP address as the server name and check if it works.

If it works, you may check DNS settings. Here is the document for your reference:

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2003/cc755882(v=ws....

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
GilbertQ
Super User
Super User

Hi there

When you configured the data source did you enabled SSO (Single Sign On) which would then want to pass through the individual users to the database.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors