Forum Discussion
Credentials not working on direct query
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.
Helloo v-janeyg-msft ,
From our preliminary analysis within this past week,
It seems that our local IT Security policy preventing the stored username/password in 1 server machine to connect with DB from other server machine depending on privileges(this could be why test connection button always failing). As also discussed with local Microsoft support, the stored credential saved in data source pbirs should be working whenever pbix is accessed through the report server. We cannot check it on our main server since there is no data yet. Hopefully this works, and then should be no issue.
-> In this case, upgrading the report server into latest version should not help.
And I have checked suggestion from Anonymous, about granting "Allow log on locally" on service account...after got the administrator approval withiin this week, unfortunately this service account is not member of administrator which is on the list group of users whom has access to log on locally, this could be why test connection button not working on report server for pbix which using direct query mode.
Community SupportHi, Anonymous
I am not sure if this issue will disappear in the latest version, but your version is indeed too old. If you encounter problems, we can't provide support for the corresponding version.
Secondly, I just ask you if you have solved the problem, not forcing you to do anything. But If you can’t add the account to admin, you may only use the import mode.
Best Regards
Janey Guo