Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
naelske_cronos
Helper III
Helper III

Power BI dataset refresh: use service principal to refresh Snowflake data with access token

I'm trying to refresh a Power BI dataset that has a Snowflake server as datasource, this with the Power BI REST API's via PowerShell and the Power BI modules:

naelske_cronos_0-1701269193699.png

 

 

 

When running my PowerShell code I got the following error:

{"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The credentials provided for the Snowflake source are invalid. (Source at bla.west-europe.azure.snowflakecomputing.com;COMPUTE_WH.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceKind","detail":{"type":1,"value":"Snowflake"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceOriginKind","detail":{"type":1,"value":"Snowflake"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceOriginPath","detail":{"type":1,"value":"bla.west-europe.azure.snowflakecomputing.com;COMPUTE_WH"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourcePath","detail":{"type":1,"value":"bla.west-europe.azure.snowflakecomputing.com;COMPUTE_WH"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"AccessUnauthorized"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Snowflake\",\"path\":\"bla.west-europe.azure.snowflakecomputing.com;COMPUTE_WH\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"AccessUnauthorized"}}],"exceptionCulprit":1}}}

I already managed to do the same thing with an Azure SQL server this by using the following code in PowerShell:

(Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token

Because my service principal has been added to the server and has db_reader/db_writer roles, it worked.

I tried to do the same thing with Snowflake so I made sure that:

  1. it is possible to request an access token from Azure to Snowflake
  2. SSO is set up in Power BI for Snowflake

I tested everything by reading from/writing to a table in Snowflake connecting with my service principal in Python and it worked and even in Postman I receive an access token and this one passed with the following code in Snowflake:

SYSTEM$VERIFY_EXTERNAL_OAUTH_TOKEN( '<access_token>' )

Everything has been set up in Snowflake and Azure and I tried to update my credentials with PowerShell and Power BI like in the following code:

Connect-PowerBIServiceAccount -ServicePrincipal -Credential $Credential -Tenant $Tenant_ID | Out-Null

$AccessToken = (Get-AzAccessToken -ResourceUrl "api://8760a.../76cee...").Token
$AccessToken

$GroupID = '60f50...'
$DatasetID = '6a989...'
$DatasourceID = 'd5dcf...'
$GatewayID = 'ae770...'
$DatasourceURL = "/gateways/" + $GatewayID + "/datasources/" + $DatasourceID

$Body = @"
    {
        "credentialDetails": {
            "credentialType": "OAuth2",
            "credentials": "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"$($AccessToken)\"}]}",
            "encryptedConnection": "Encrypted",
            "encryptionAlgorithm": "None",
            "privacyLevel": "Organizational",
            "useEndUserOAuth2Credentials": "True"
        }
    }
"@

$GetAPIResult = Invoke-API -URL $DatasourceURL -Method 'Patch' -Body $Body -ContentType 'application/json'
Write-Output "The credentials for the datasource $DatasourceID have been updated successfully"

When I also run the following code in Snowflake after trying to update my credentials, I see that my service principal has logged in successfully to Snowflake:

select *
from table(information_schema.login_history(TIME_RANGE_START => dateadd('hours',-1,current_timestamp()),current_timestamp()))
order by event_timestamp;

By checking some things out, my concern is that in Snowflake it seems that to authenticate, a combination of a user and access token is needed. I even needed to do that with Python so I'm afraid that's also the case for Power BI? As I'm only passing an access token without the paired Snowflake user, that's maybe why I received the error?

 

Someone that could help me with the issue?

 

Kind regards

5 REPLIES 5
tackytechtom
Super User
Super User

Hi @naelske_cronos,

 

Have you tried to set the service principal as the owner of the dataset?

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hello ,

This is something that I do before changing the metadata in my datasets, so yes: 

Capture.PNG

The thing is that I don't know if the problem is on the side of Power BI or Snowflake. Like in my intitial post, I told that my access token gives me access to the tables in my Python script and it seems that to authenticate I also need a user which is binded to that access token.

snowflake_engine = create_engine(
    URL(
        authenticator="oauth",
        user = 'bla',
        token = 'bla',
        account = 'bla',
        warehouse = 'COMPUTE_WH',
        database = 'MYTESTDB',
        schema = 'PUBLIC',
        role = 'ANALYST'
    )
)

In Power BI it does not work, so I'm afraid that I also need like some kind of user along with my access token but I don't think that is possible. I cannot find any information or experiences by other people on the internet. 

 

Kind regards

 

 

 

Hi @naelske_cronos ,

 

That is actually an interesting one! 🙂

Do you fetch the access token with via same service principal that is also the owner of the semantic model? 

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hello,

 

In addition, I'm trying to do the same workflow as with an Azure SQL server. In Snowflake it goes like this when you implement SSO in Power BI for Snowflake (https://docs.snowflake.com/en/user-guide/oauth-powerbi)

naelske_cronos_0-1701417954253.png

Via the UI it works perfectly but I'm trying to do the same programmatically.

 

 

Kind regards

 

Hello,

 

Yes, my service principal:

  • has the correct permissions in the Azure Portal to call the Snowflake client:
    Capture.PNG
  • Is part of a security group that which is possible to use the Power BI REST API's
  • Is member of the workspace where the dataset resides in
  • Is owner of the dataset of which I want to change the credentials

In my script I'm working with my service principal to publish the report, making it as owner, updating the credentials and further on. I managed to put basic authentication with the service principal so it has the correct priviliges on Power BI side

Capture.PNG

Even on Snowflake side it should be correct because I see in my logs that the service principal which is bounded to a user in Snowflake has connected:

Capture.PNG

 

 

Kind regards

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.