Credentials not working on direct query

[Anonymous]

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,

[Anonymous]

Hi @v-janeyg-msft, we cannot switch to import-mode due to business reason. And yes as i mentioned, upgrading powerbi to the latest version will highly probably NOT solve this issue, so we won't due anything about it.

our next plan is to add this service account into new group created by our security it team and it must be member of log on locally in their policy.


Thank you,

dronely

[v-janeyg-msft]

@Anonymous  OK. Hope everything goes well.

[Anonymous]

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 @JeanMartinL, 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.

[v-janeyg-msft]

Hi, @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

[v-janeyg-msft]

Hi, @Anonymous   Any updates?

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly.

Best Regards

Janey Guo

[Anonymous]

Hello @JeanMartinL , thanks for your suggestion

in order to apply it to our main environment, apparently we need administrator privilege which would possibly take hours-days here for avowed, the same goes for generataion of user service ad microsoft sql server account to apply it in our test environment.

I will mark it accepted as solution, once i've applied it and succeed.

Hi @v-janeyg-msft , i would consider upgrading powerbi report server into latest version as last resort on this situation.. but to be sure we're developing pbix files in pbi desktop version may 2020, and once it published into pbirs may 2021 (newer version), there should be no issue (of compatibility or any other), right?

Regards,

dronely

[v-janeyg-msft]

Hi, @Anonymous 

If your problem can be solved, you don't need to rush to upgrade. Although the new version is compatible with the old version, there may be some issue in the early stage. But your version is really old, you can start preparing for the updated version after making a backup.

Best Regards

Janey Guo

[JeanMartinL · Accepted solution]

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.

[Anonymous]

Hi @v-janeyg-msft ,

Currently we installed the pbirs version may 2020, with license on and we don't have any plan to upgrade into latest version. Is there any potential bug within this version (May 2020) ?

Yes, there is no issue occur upon entering credentials in pbirs portal for all pbix files published using import mode, and we are very sure the credentials are the same for all pbix files (both import & direct query)

[v-janeyg-msft]

Hi, @Anonymous 

If you install the May 2020 version, theoretically, support is no longer provided. Support timeline for Power BI Report Server - Power BI | Microsoft Docs

I’m sorry that I can’t see any problems from your description, Have you tried to republish your report in dq mode to the web portal?

You can also refer to the document to upgrade the version and make a backup:

Upgrade Power BI Report Server - Power BI | Microsoft Docs

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-janeyg-msft]

Hi, @Anonymous 

Since I can‘t upgrade to the latest version temporarily, it is hard to reproduce your scenario,I need you to provide more information.

Did this problem occur after upgrading to the latest version of report server? Do you mean that there is no problem after entering the credentials in the webportal with the report of import mode? Are you using the same credentials?

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.