使用服务主体访问令牌刷新Power BI的Snowflake数据源报错求助

问题现象 #

尝试用PowerShell调用Power BI REST API刷新以Snowflake为数据源的数据集时，触发凭证无效错误：

{"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}}}"

已验证的基础情况 #

• 此前用相同逻辑刷新Azure SQL Server数据源成功，核心代码：

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

  服务主体已添加至目标服务器并拥有db_reader/db_writer角色。
• 已完成Azure与Snowflake的服务主体模式OAuth配置，以及Power BI中Snowflake的SSO配置。
• 用Python验证服务主体可正常读写Snowflake表，Postman获取的令牌能通过Snowflake的SYSTEM$VERIFY_EXTERNAL_OAUTH_TOKEN( '<access_token>' )验证有效性。
• 执行更新凭证的PowerShell代码后，Snowflake登录历史可查到服务主体的成功登录记录。

当前更新凭证的PowerShell代码 #

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"

排查推测 #

问题大概率源于Snowflake认证需要同时传递用户名和访问令牌，但当前仅向Power BI提交了令牌，未关联对应的Snowflake用户。

解决方案 #

1. 修改凭证请求体，补充用户信息 #

Power BI的Snowflake OAuth凭证必须同时指定username和accessToken，更新请求Body的credentials字段：

$Body = @"
    {
        "credentialDetails": {
            "credentialType": "OAuth2",
            "credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"<你的Snowflake服务主体用户>\"},{\"name\":\"accessToken\", \"value\":\"$($AccessToken)\"}]}",
            "encryptedConnection": "Encrypted",
            "encryptionAlgorithm": "None",
            "privacyLevel": "Organizational",
            "useEndUserOAuth2Credentials": "False"
        }
    }
"@

关键调整：

• 将<你的Snowflake服务主体用户>替换为服务主体对应的Snowflake用户名（通常为AZURE_<服务主体ID>或OAuth配置时指定的用户）
• 把useEndUserOAuth2Credentials设为False，因为此处使用的是服务主体凭证，而非终端用户OAuth

2. 确认网关连接配置 #

检查Power BI网关中Snowflake数据源的连接设置，确保身份验证方法选择OAuth2，且无残留的用户名密码等其他凭证干扰。

3. 修正令牌请求的资源地址 #

确认Get-AzAccessToken的ResourceUrl为Snowflake账户的URL（格式：https://<账户名>.<区域>.snowflakecomputing.com），而非Azure AD的客户端ID URI，重新获取令牌后再尝试。

4. 验证Snowflake用户权限 #

确保服务主体对应的Snowflake用户拥有数据集所需的数据库/架构/表权限（如USAGE、SELECT），且该用户已启用OAuth登录。

内容的提问来源于stack exchange，提问作者KevinN1992