DirectQuery reports not loading in PowerBI service

[fabbimk]

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.

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!

[fabbimk]

Thanks for the replies!

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

[GilbertQ]

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?

[fabbimk]

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. 

[GilbertQ]

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?

[fabbimk]

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"

[GilbertQ]

Hi there

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

[fabbimk]

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. 

[TheCatt]

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]

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....

[GilbertQ]

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.