We have an odd issue; a report with 2 data sources (import mode): Server 1 database A and server 2 database B.
Server 1 is UAT and Server 2 is production (but have the same databases A and B), so when the report is published to the Report server, they use different accounts with different privileges.
We have some queries created under the datasource for server 1, but which have joins to the database that is specified in the server B datasource; however, as it exists on Server 1, you would expect the query to run only against the databases on Server 1.
However, our scheduled refresh is failing saying that the server 1 account cannot access database B - which MUST mean database B on Server 2 (production), as the account would be able to access database B on server 1.
I get the same error in PBi Desktop if I use the same credentials as on the server.
Is there some oddity within the Power Query model that might do this? i cannot find anyone who seems to have had a similar issue.
The error is:
Details:
DataSourceKind=SQL
DataSourcePath=Server 1;Database A
Message=The server principal "xxxx" is not able to access the database "Database B" under the current security context.
Number=916
Class=14
