topic Re: Power BI dataset refresh: use service principal to refresh Snowflake data with access token in Developer

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

naelske_cronos — Wed, 29 Nov 2023 14:47:03 GMT

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:

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:

it is possible to request an access token from Azure to Snowflake
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

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

tackytechtom — Thu, 30 Nov 2023 10:31:40 GMT

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/

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

naelske_cronos — Thu, 30 Nov 2023 12:23:53 GMT

Hello ,

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

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

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

tackytechtom — Thu, 30 Nov 2023 14:31:09 GMT

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/

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

naelske_cronos — Fri, 01 Dec 2023 07:59:02 GMT

Hello,

Yes, my service principal:

has the correct permissions in the Azure Portal to call the Snowflake client:
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

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:

Kind regards

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

naelske_cronos — Fri, 01 Dec 2023 08:06:54 GMT

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)

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

Kind regards