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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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