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.