Unable to Connect to Data Source in Report Only

[cusecuse315]

I am having an issue where a single report is throwing an error that its unable to connect to data source when opening the report in PBI Service only. It is a direct query SQL Server data source type, connecting through a On-Premise Data Gateway that is up to date (nov2020).

The report connects fine in Desktop, the data source connection in Data Gateway shows successful connection, when manually refreshing the data is shows that the refresh was successful, but when opening the report the data connection fails. It is a single table from a single data source, with no transformations, and other databases on the same server have no issues with their connections. Report uses service account credentials so it is not a credential issue.

PBI Desktop Version: Oct 2020

On-Prem Data Gateway Version: Nov 2020

Steps taken to unsuccessful resolution:

• Double checked credentials and validated correct
• changed report Credentials to another account with admin access
• changed data source in gateway credentials to a different admin account
• changed credential type (attempted all types in both report and data source in gateway)
• changed connection type (switched from direct query to import) and refresh still failed
• validated matching privacy levels between report and gateway
• created a new PBIX, connected to the data source, published to new workspace, still failing
• created brand new data gateway on a different network server, routed connection through that, and still failure

Screenshot of error with import:

Screenshot of error with DirectQuery

[v-easonf-msft]

Hi, @cusecuse315 

Have you tried to refresh data by using Import mode?
Please verify  whether you have used correct username and password. Also, verify that those credentials can successfully connect to the data source. Make sure the account that's being used matches the authentication method. The server and database names must match between Power BI Desktop and the data source within the on-premises data gateway.

Best Regards,
Community Support Team _ Eason

[GilbertQ]

hi @cusecuse315 

Can you log into the gateway server and make sure that you can connect with the required credentials from the gateway server to your SQL Server?

[cusecuse315]

@GilbertQ 

Yes, I have used my personal work account, which is admin on gateway server and SQL Server, as well as the main service account for the gateway server and SQL Server. I also spun up a gateway on a different server and experienced the same issues. 

Its very strange, I can refresh other reports using same connection type from other databases on the same server, but this database is failing, but only in service. Connection in Desktop is without issue. There are no security differences between various databases

[GilbertQ]

Hi @cusecuse315 

Can you run SQL Profiler on the source, then interact with the report to see if the query gets back down to the SQL Server Source?

That will help identify if the query gets to the SQL Server or not.

If it does not there might be an issue from the gateway server to the SQL Server Source.

Also are you passing through SSO for the logged in user?

[cusecuse315]

@GilbertQ 

thanks for the quick reply. 

Trace file shows audit logins when manually refreshing direct query dataset from workspace, but no activity when opening and refreshing from report.

I've tried multiple connections and accounts, both Windows and Basic for gateway, and Windows and Database credentials for the report. Tried all mix and match combos of those items. 

Microsoft Account and OAuth are not valid connection types for my company's security environment.