Credential Management for multiple model developers/roles change?

[Anonymous]

Hi all,

Core question: How do you manage you credentials in a dataset?

Background:
Standard practice is to have a managed shared workspace (I assume this means at minimum not the "myworkspace" one). Within this is the dataset created by the model developer which is shared with report builders. The workspace admin and the modeler are likely the same person for smaller organisations. 

During the dataset development each data source gets attached credentials. These then get loaded up to the powerbi service and used for the data refresh. A majority of these credentials are likely to be AAD/Single sign on/MS multi factor authentication for all MS source e.g. SQL DB, log analytics, Azure Synapse etc. and non MS sources e.g. Zendesk etc. Some of data sources will these will be using non-single sign on, for these the credentials are likely the Modeler's own. Many will likely use their own custom connectors or self-made API calls.

Issue with Credentials:

What happens if the Modeler leaves the company or role changes?

1. AAD account is turned off or no longer has the same security groups in AAD.
2. dataset stops refreshing, as the credentials are no longer valid.
3. To prevent this a new workspace admin needs to be added to the workspace. Then they need all to be given access to all the data sources so they can update the workspace. All this be done in the handover time eeek.

What happens with multiple developers modeling the same .pbix file?

1. documentation suggest source control/version control via OneDrive.
   1.  I currently don't think it matters for this issue, which method you use.
2. Developer uploads the file. Connection to the data sources in the service, now uses their credentials.
3. Next Developer uploads the file. Connection to the data source in the service, credentials are replaced with new developer's.
4. Result the credentials used are from whoever last updated the .pbix. Does each dev really need access to all the source?

Azure resources roles need to be permanent?

1. Security like to have temporary RBACs.
2. This would result in the refresh stopping as soon as the 8 hours are up.
3. To fix this, the roles would need to be not only permanent.

There has got to be a better way to manage the credentials once the dataset is in the service? How do you manage you credentials in a dataset?

Current Solution ideas:

1 - research, code to update creds in the service:

The first link is not that useful but related

https://community.powerbi.com/t5/Desktop/connect-to-the-same-database-with-2-different-users/td-p/75...

https://community.powerbi.com/t5/Developer/Possible-to-update-credentials-via-REST-API/m-p/137509

This last one I find a little hard to follow. It seems to imply that for some dataset we can update the credentials used via the PowerBI API. Ether in C# or PowerShell.

I am hoping there might be an easier method? They do say that a service principle can added as an workspace admin as a part of this process. & anyway it seems limited in application.  It does seem useful if you have a single source of data e.g. SQL.  Is this interpretation accurate? How many azure sources can it be used for? There definitly seems to be sources it wont work with.

2 - Have a external single source of data.
Great if you have one, but it you do not then... This still results in one data source to manage but as long as it is in Azure that should be easy with security groups. Practically though it means someone would have to manage the access until a replacement is found.

3 - You can now interact with dataset via Azure Synapse workspace or ADF.  This suggests that azure synapse uses the devs AAD but maybe the access can use managed identity/service principle..? Access via Managed identity or SP would be great... I would suspect that you would need synapse to manage the pipelines to the datasets. So it has similar issue as 2, in that you still need that external source.

4 - password manager.  Store all the non MS access cred info in a shared manager. Which gets regenerated whenever someone leaves. But this also means updating the credentials in the service too manually.

Non of these ideas really fills me with confidence in a reliable environment. Unless you can build that external hub. 

If a service principle can be added to the workspace, could be not use that to manage to access other MS resources?

Your thoughts and ideas will be much appreciated.

[lbendlin]

You haven't even mentioned the biggest kicker yet.  There's also the gateway connection credentials to think about. That has a much bigger impact than the dataset "owner" topic.  

Power BI has been designed for the single developer, not for an enterprise environment. There are some changes happening with tentative support of deployment pipelines etc, but these are slow and often insufficient.  My advice would be to shift your focus to procedures, like excessive communication among developers, using sharepoint or github for smaller pbix files, adopting pbi.tools or other methods that suit your developer mentality. Be good friends with your tenant admin and your O365 admin. Ask them for advice on service principals and other workarounds.

[Anonymous]

Yes there is that too. I agree that the single developer design is an issue for enterprise. Another way I think about it is that is an office product not an azure product. I can see how this came about, it is an extension/combination of access, excel and powerpoint in terms of design and function (plus cloud service). That being said, would it have such a nice ux if it had been an azure product? probably not. It is also fare to say it is for non-developers like me, likely because it is an office product. Although ADF & Synpase Workspace are both very non-dev... so maybe not? I kinda whish thought that instead of trying to make PowerBI enterprise/azure friendly. They kept PowerBI as self-service and then build a seperate product in Azure that was both PowerBI & PBI Embedded / enterprise, keeping a similar ux if possible. However, there is a fair argument that this already exists with Azure Analysis Services (minus the visuals) even though they seem to be giving Premium all the fancy stuff over AAS.

additional, there is dicussion on the gateway, in the link I shared above:
 https://community.powerbi.com/t5/Developer/Possible-to-update-credentials-via-REST-API/m-p/137509
and I have seen other conversations on the gateway issue.