There are two reasons why I am choosing such a technically specific subject:
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.
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.
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.
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.
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.
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.
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
Kevin Naels, data engineer at
Kohera aan de Leie