Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes

There are two reasons why I am choosing such a technically specific subject:

1. In some companies, they prefer that the production environments or workspaces, as called in Power BI, remain untouched by the user. Updating the credentials of a Power BI semantic model via the user interface in Power BI service is not a modern approach. There must be a standard automation process where Power BI semantic models are deployed from a development environment to a production environment via a non-interactive login.
2. It is not an easy task, as everything needs to be done with the Power BI REST APIs and some PowerShell magic. If the code is well-written and tested successfully, this could ensure that fewer mistakes will be made in the future…

In this blog post, the operation will be discussed on how OAuth2 credentials for the Azure SQL server data source in Power BI semantic models can be updated programmatically with PowerShell. To make it more complex, the semantic model uses DirectQuery mode, and additional security on database level is enabled. By the latter, I mean that objects on the data source can only be accessed if the currently logged-in user has access to it based on its user account.

Some theoretical stuff

How does the authentication method OAuth2 work?

Anyone with experience in creating models in Power BI has likely attempted to connect to an Azure SQL server using OAuth2. Therefore, a user account is chosen, such as an admin account or service account, that has access to the Azure SQL server data source. The user account needs to have at least ‘CONNECT’ permission to the database.

Because the semantic model uses DirectQuery, the intention is to only display what is permitted for the currently logged-in user. That is why this option is enabled. If the option is disabled, the account set up for authentication will be used. If this account has access to every small detail in the database, users accessing the Power BI report could see data from tables for which they actually have no permissions.

The rise of service principals as authentication method

To my best knowledge, I honestly haven’t found any information about using a service principal to authenticate to the Power BI service. Google has not been helpful either, so it must be relatively new, or I am not so good at searching.

I am aware that a service principal or an application created in MS Entra ID can be seen as a means to authenticate to services non-interactively and, secondly, as a way to authenticate more securely rather than using a username with password. It is not the intention to discuss the functioning of a service principal, only what is needed to use it for updating the Azure SQL server credentials. For more information, URLs are available at the end of this blog post.

Suppose the service principal or application has been created in MS Entra ID, but no further actions have been taken. When attempting to connect with it, the following error will be given: “Login failed for the service principal”.

As with user accounts, service principals also need to be added to the database to ensure a successful connection. The following T-SQL code will add the service principal, granting it only ‘CONNECT’ permission to the database. This should result in a successful connection to the data source in Power BI.

As with the example for the OAuth2 authentication method, when the option to show only what is permitted to the logged-in user is disabled, the permissions given to the service principal will be used.

Having ‘CONNECT’ permissions for the service principal on the database does not necessarily grant it permissions to read the underlying objects. In the Power BI report, data in a table visual is displayed, but an error arises stating that ‘the SELECT permission was denied on the table’.

To resolve this issue, it is simply a matter of giving ‘READ’ permissions on the objects in the selected database. In this use case, the role ‘db_datareader’ has been assigned to the service principal, providing the ability to read data from all tables and views.

Similar to the OAuth2 authentication method, when enabling the option as shown in the following image, the credentials of the currently logged-in user will be used. Even if the service principal lacks ‘READ’ permissions on the objects in the selected database, these permissions will be overwritten by the permissions of the currently logged-in user.

Another error that might sound familiar when connecting to an Azure SQL server is ‘login failed for user ‘<token-identified principal>’…’. This example is still built upon the previous one where the service principal is used to connect to the data source, and the option is enabled to show objects in the database based on the logged-in user. The Power BI report will render correctly, but again, an error will arise on the table visual, stating that the login failed for the user. This occurs because the currently logged-in user does not have permissions to the Azure SQL server.

Update your credentials via PowerShell

The only way to manipulate the metadata of Power BI semantic models and such is to have permissions to call the Power BI REST APIs. This can be achieved by creating a service principal, which can be the same service principal that has access to the Azure SQL server or a separate service principal. There are two prerequisites: 1) add the service principal as at least a ‘member’ of the workspace where the semantic model resides, and 2) enable the option ‘Allow service principals to use Power BI APIs’ in the admin portal. For more information, this URL provides a whole roadmap to get it working.

Once the service principal has the requested permissions, it is possible to manipulate the semantic models’ metadata. The following PowerShell script will update the credentials, and therefore, some steps will be explained in detail.

1. The Power BI REST APIs will be called from within the function ‘Invoke-API’. This has been put into a function as it will be used multiple times in the PowerShell script. The task ‘Get-PowerBIAccessToken’ takes care of requesting a working access token to get and manipulate the metadata of the semantic model.

2. In this use case, the same service principal is used to call the Power BI REST API’s as having access to the Azure SQL server.
   1. The connection to the ‘back-end’ of Power BI is made with the task ‘Connect-PowerBIServiceAccount’ by filling in a client id, tenant id, and client secret.
   2. A connection to the ‘back-end’ of Azure is made with the task ‘Connect-AzAccount’. This is done to request an access token to fulfill the connection with the Azure SQL server. It is important to use the correct resource URL, like in this example, ‘https://database.windows.net’. Each service has its own resource URL, so it depends on which service you are trying to access. This can be the resource URL for connecting to SharePoint or the resource URL for connecting to MS Outlook (MS Graph). For this, the client id, tenant id, and client secret needs to be filled in.

3. It is crucial that the service principal is the owner of the semantic model. To achieve this, the Power BI REST API ‘Take over in Group’ can be called. If this step is omitted, the service principal would lack the necessary permissions to update the credentials of the semantic model.

4. To update the credentials of the semantic model, some additional parameters are needed, such as the data source id and gateway id. This can be requested with the Power BI REST API to retrieve the data sources.
   1. The body of the Power BI REST API call to update the credentials is crucial. The ‘credentialType’ is ‘OAuth2’. Do not be deceived because, in the Power BI service with the user interface, it might be labeled as ‘service principal’. However, under the hood, it works in the same way as ‘OAuth2’, but here the connection is made with a service principal instead of a user account.
   2. The actual credential is the access token that has been requested in step 2.
   3. The last part of the body is the ‘useEndUserOAuth2Credentials’ section. This is the same as the checkbox in Power BI service to connect to the data source with the currently logged-in user.

After running the above code, the credentials of the Azure SQL server data source for that specific semantic model should be successfully updated.

A small downside is that access tokens requested programmatically, by default, expire within an hour. This means that if credentials have been updated with the service principal without overwriting them based on the logged-in user, and a refresh is scheduled for the next day, it will fail. In that same table visual, as mentioned before, the error ‘Token is expired’ will come up.

• When the credentials are overwritten by the credentials of the logged-in user by setting the option ‘useEndUserOAuth2Credentials’ to ‘True’, no problem will arise, and the refresh will succeed.
• A potential solution could be that if ‘useEndUserOAuth2Credentials’ is set to ‘False’ and the credentials of the service principal are used, is to work with, for instance, an Azure Function that requests a new access token when a refresh is about to happen on the semantic model.

Conclusion

It is perfectly possible to automate such processes, but it requires more coding skills. See it as a long-term investment. If a company has over more than a hundred semantic models divided over many workspaces, it could be time-consuming to maintain this, and there is a greater chance of making mistakes. The only downside is that the official documentation does not provide decent information about the usage of the parameters for most of the Power BI REST API examples.

References

How to create an application in MS Entra ID: https://learn.microsoft.com/en-us/entra/identity-platform/quickstart-register-app

What is an application or service principal: https://learn.microsoft.com/en-us/entra/identity-platform/app-objects-and-service-principals?tabs=browser

Create a service principal to use the Power BI REST APIs: https://learn.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal

How to use the Power BI REST APIs to update the credentials: https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource