Forum Discussion
Credentials not working on direct query
Hi,
I am using PBIRS version May 2020 on premise, and already published pbix source files into report server.
Currently, we managed to use domain AD username for setting data sources in report server.
Unfortunately, the credential works fine for pbix files with import-mode, not the ones with direct query..even, the network team has ensured to type password correctly. Can Microsoft/anyone please suggest to me what to do?
Thank you,
Hello Anonymous ,
We've encountered a very similar issue lately. Just like you our Import schedules would work but both our Direct Queries and SSRS data sources (using the same account) would fail.
For us the solution was to grant "Allow log on locally" in the Local Security Policy to the service account on the Report Server. Which is not really what we wanted, it kind of bypass the whole idea behind a service account but it's the only thing that worked.
This was based on this article. It's technically for SSRS but as we know Report Server shares a lot of the same codebase. Specifically this paragraph:
If you use stored credentials to connect to an external data source, the Windows domain user account must have permission to log on locally. This permission allows the report server to impersonate the user on the report server and send the request to the external data source as that impersonated user.
You can potentially test it by changing the user account currently used in the data source by one which already have access to the server. For example, for us the connection would fail with our service account (since it didn't have any logon permissions) but would succeed when using my own account (which can log on the server).
Hopefully that helps.