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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors