Unable to change Power BI Report dataset service user credentials

[AishwaryaThonda]

Hi Everyone,

Context :

I have created 2 reports by fetching the data from Snowflake using "DirectQuery" option. I had published them to Power BI Service. Everything was working fine. We have two snowflake roles - 1. READONLY role 2. OWNER role out of which READONLY role is linked to PBI service user account. I had access to OWNER role & used those details while fetching the data (though it is optional)

I was supposed to update the dataset credentials to have service user credentials so that end users can utilise the reports. Password rotation happens every 3 months for the service user credentials so I had to update the dataset credentials for the 2 reports. 

Issue :

My team had 2 service user credentials 1. Altery service user account 2. PBI service user account.

I was supposed to update the dataset credentials with PBI service user account, but I accidentally updated the dataset credentials with Alteryx service user account & published the reports to PBI service. I'm unable to change the Alteryx service user account credentials with that of PBI service user account. If I try to change this is the error that I get -

I couldn't find much resources online related to snowflake - service user account...hence reaching out to the community. I can't delete & re-publish the report as they are being used by end-users and also can't delete the reports , parameterise the service user account details/ clear data source permissions & re-publish

Any help/suggestions are highly appreciated.

[aj1973 · Accepted solution]

@AishwaryaThonda 

If the owner role has changed then you need to retrun to your desktop and change it from there

Or you can change in Power Query if you know how to do it.

I would recommend you create parameters in Power Query, One for the WH and One for the Role and may be another one for the DB in case you want to switch between Dev and Test environment in teh service.

Let me know if you needed help.

[aj1973]

Hi @AishwaryaThonda 

You need to first take over the ownership of the semantic model, go to its settings and edit data source credentials. Use the Power BI service account to sign in. Make sure that the service account has access to the WH in Snowflake.

[AishwaryaThonda]

Hi @aj1973 ,

I am the owner of the semantic model and I've tried to change the edit data source credentials. The model saves the credentials but report throws an error saying "Cannot load model". This could be because I gave OWNER role while fetching data from snowflake. Also, the service account (alteryx & pbi) have access to the same warehouse. 

I am unable to change/remove the OWNER role associated to the service account from the PBI report. Is there any way to locate the metadata about the OWNER role & change it?

Thanks,

Aishwarya.

[aj1973 · Accepted solution]

@AishwaryaThonda 

If the owner role has changed then you need to retrun to your desktop and change it from there

Or you can change in Power Query if you know how to do it.

I would recommend you create parameters in Power Query, One for the WH and One for the Role and may be another one for the DB in case you want to switch between Dev and Test environment in teh service.

Let me know if you needed help.

[AishwaryaThonda]

@aj1973 thankyou for the suggestion!

I looked into paramterizing option as someone else has also suggested it & Ideally, it's a good practice when creating reports having multiple roles & warehouses involved. If I create parameters in desktop, I would have to re-publish the reports. Is there any other way I can do it in service itself?

Also, could you please have a look at my newest reply under Kirubs comment. I have tried another approach and it has worked but not sure if it is a solid solution to my issue. 

Thanks!

[aj1973]

Sorry to tell that nothing can be done in the service. You have to do it in the desktop and create parameters in order not to see this happen again.

To reply to your statement

Republishing a report does not replace a report if you don't touch anything else but "Data source settings". It is just going to update the connection settings which is somthing very current in Power BI.

Also I would recommend creating different WS, one for Datasets and one For thin Reports. This way when you have a such situation you can only manage the dataset without report.

Last nut not least, End users should consume the reports and dashboards in App and not in Workspaces.

Good luck

[AishwaryaThonda · Accepted solution]

Thankyou Amine!

I will try creating a different workspaces for reports & datasets going forward and share reports via App. 

[Kirubs]

Hi Aishwarya, 

As far as I understood, from Power BI service you were able to change the Alteryx credentials to Pbi credentials and saved it.  The Owner role is defined within the Power BI report and the way I see it is, it can be only updated within the report with Edit credentials option.  I am not sure even with the metadata if we can update the connection details defined within the report.    In your case, you mentioned that you cannot update or re-publish the report (but I do not know why). If you are updating the credentials, It will not affect the funtionality of the report and moreover the users are accessing the report from APP. 

[AishwaryaThonda]

@Kirubs 

I can only update/ manage the data source settings in power bi desktop if I want to clear permissions of roles. I am unable to do it through PBI Service.  I cannot re-publish the reports to replace previous ones because these reports have been tested & approved by end-users so replacing them is the last option for me (which is not a best practice)

The reports are available in designated workspaces but not App.

I tried out this scenario to find out if the issue is solely because I used OWNER role while fetching the data:

1. I created sample report using OWNER role & updated the alteryx service account credentials on PBI service. Reported worked fine because OWNER role is linked to alteryx service account. I have updated PBI service user credentials & it didn't work.
2. I tried by using READ_ROLE & updated with alteryx related service user credentials it didn't work because READ_ROLE is associated to PBI service account.
3. Lastly, I did not give the ROLE detail while importing data, published & updated both credentials & it worked. 

Today I tried out another way to resolve this issue -

I went to the semantic model settings (of the report created using OWNER role), "gateway & cloud connections" and created a new connection instead of using "Personal Cloud Connection".

After adding the service account details, I selected the Alteryx_Service_User , saved it & it worked fine as it should. I selected PBI_Service_User and it displayed the same error "Cannot load model".

But after a while once I refreshed the semantic model & the report, the report visuals were working fine & as expected. My hunch as to why this might have worked - Since both the service accounts have a single & the SAME warehouse it didn't cause any issues.

I am not sure if any one else has tried this but I'm checking if this could be a possible solution. 

Let me know if you have any thoughts. Thanks!

[prateekraina]

Hi @AishwaryaThonda ,

Have you tried going into Dataset settings and trying "Edit Credentails" option found under "Data Source Credentials" ?

[AishwaryaThonda]

Hi, 

Yes, I've tried the Edit Credentials option. When I gave the service account credentials, it said "Credentials saved successfully" but when I open/refresh the report it says "Cannot load model".

[Kirubs]

Hi, 

You do not need to delete, you just need to publish the report with correct credentials and update the workspace.  It will not cause any harm to the users and they can barely notice.  

I assume this error is when you try to about the credentials under "Data source crendentials" in Power service??  Did you try it already? 

Does the Alteryx service account having authorization to connect to your source? 

[AishwaryaThonda]

Hi Kirubs, 

I cannot/ not supposed to replace the report with the new credentials (by publising again to the workspace) as it has been tested & end users are using the reports. So I don't want to disrupt/crash the reports by publishing with new credentials. I am looking for alternate solution. 

Yes that's correct & alteryx service account has the authorization to connect to the source so I don't have any issue in displaying the data/ refreshing the data set. It's just that I want to replace the alteryx service account creds with that of PBI service account creds as we have other reports which are using PBI service account credentials. Ideally all the reports should be authorise using PBI service account credentials. Any ideas?

Thanks!

[Kirubs]

Hi Aishwarya, 
Gotcha.  I assume that the dataset is configured by you but yet you are unable to update data source credentials under the semantic models.  It would help if you could post the details of the error?   Are you using any PBI Gateway to connect to your source? 

[AishwaryaThonda]

Yes, that's correct. Here's the error details.

No, I am not using gateway connection. In the QueryUserError A_....._PROJECT_MDS_WH is common for both READONLY role & OWNER role. I have given details of the  OWNER role while fetching data from snowflake platform. But the OWNER role is not linked to the PBI service user account (only READONLY role is linked to the service user account). I might be able to update the credentials if OWNER role is added to the PBI service user account but it's not something we plan to do because end users should be having only READ access to the data & underlying tables/views in snowflake.

[AishwaryaThonda]

Error Details -