1

I have a simple scenario where I have an Azure SQL Database and I want to use the data from the DB in a Power BI report. The DB server has only Azure AD authentication enabled. So far I managed to create and refresh the dataset by using my own credentials (authentication method: OAuth2), but I would like a more generic solution which doesn't rely on a user account.

Is it possible to enable a managed identity for the Power BI workspace and use it to connect to the Azure SQL DB and get the data? This way power BI will use the same pattern the other Azure services are using to access the DB.

If Power BI managed identity is not supported yet, what would be the best alternative to pull the data without using a user account?

chris770
  • 15
  • 4

1 Answers1

1

If Power BI managed identity is not supported yet, what would be the best alternative to pull the data without using a user account?

AFAIK, Only Azure Stream analytics job supports Managed Identity as an authentication for Power BI.

But as a workaround you can make use of Azure Service Principal or Azure AD application to authenticate with your Power BI datasets connected to Azure SQL.

I created one Power BI group where my Azure AD Service principal and users exists.

I connected to my Azure SQL server with Powerbi like below:-

enter image description here

enter image description here

Created one PowerBi report out of Azure SQL dataset like below:-

enter image description here

Uploaded it to PowerBi Web :-

enter image description here

I have one PowerBI embed group which has Embed Demo app and users who can access Power BI like below:-

enter image description here

Logged into my Power BI web portal > Settings > Admin Portal > Tenant Settings

enter image description here

Set Allow Embed Settings Enabled :-

enter image description here

Allow Service Principals to use Power BI and give powerbiembedgroup access.

enter image description here

Provide Service Principal access to workspace or specific dataset:-

enter image description here

enter image description here

Access PowerBI via Powershell:-

Install PowerBI Powershell module -

Install-Module -Name MicrosoftPowerBIMgmt

Connect to PowerBI with SP -

Connect-PowerBIServiceAccount -ServicePrincipal -Credential (Get-Credential) -Tenant xxxxxxxx-xxxx-xxexx-99ed-af9038592395

enter image description here

In the Username and password add the App ID and password of the Service principal.

Connected to PowerBI successfully :-

enter image description here

Get the workspace -

   Get-PowerBIWorkspace

References:-

Embed Power BI content in an embedded analytics application with service principal and an application secret - Power BI | Microsoft Learn

Use Managed Identity to authenticate your Azure Stream Analytics job to Power BI output | Microsoft Learn

SiddheshDesai
  • 3,668
  • 1
  • 2
  • 11