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

Question

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:

Snowflake_PBI_Credentials

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 like in this post: Power bi dataset refresh: how to use Service Principal for data source credentials 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: https://docs.snowflake.com/en/user-guide/oauth-azure with a service principal
2. SSO is set up in Power BI for Snowflake: https://docs.snowflake.com/en/user-guide/oauth-powerbi

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: https://community.snowflake.com/s/article/Create-Security-Integration-User-To-Use-With-OAuth-Client-Token-With-Azure-AD (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