Set (Cloud) credentials during dataset deployment #195

mthierba · 2022-10-16T20:44:26Z

Credential Types

Reference

https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource

- Implemented: Basic creds (see: https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource#basic-credentials-example)

- #195 Set (Cloud) credentials during dataset deployment - OAuth2 - #195 Case-insensitive matching of data sources - Manifest Schema Changes - manifest.credentials[].updateMode: { NotSpecified, Always, Never, BeforeRefresh } - manifest.credentials[].type: { Basic, Anonymous, OAuth2 } - manifest.credentials[].authority - manifest.credentials[].validateAuthority - manifest.credentials[].tenantId - manifest.credentials[].clientId - manifest.credentials[].clientSecret - manifest.credentials[].scopes - manifest.credentials[].useDeploymentToken

ghost · 2023-04-25T23:16:34Z

Hi, can i get a sample .pbixproj.json to set dataset credentials for Snowflake using "OAuth2". I have used below one for our devOPS, but is not working. After each deployment, dataset refresh is not being scheduled; it's throwing credential error. Thanks in advance.

============
....
.....
"credentials": [
{
"match": {
"datasourceType": "Snowflake",
"connectionDetails": {
"server": "<>.snowflakecomputing.com;SAMPLE_WH",
"database": "<>"
}
},
"updateMode": "NotSpecified",
"type": "OAuth2"
}
],
"options": {
"import": {
"nameConflict": "CreateOrOverwrite"
},
"dataset": {
"replaceParameters": true,
"setCredentials": true,
"deployEmbeddedReport": true
}
},
"parameters": {
"[REPORT_NAME]" : "",
"[TARGET_NAME]" : ""
},
"environments": {
"DEPLOY": {
"workspace": "{{[TARGET_NAME]}}",
"displayName": "{{[REPORT_NAME]}}",
"refresh": {
"enabled": true,
"skipNewDataset": false,
"type": "Full",
"objects": {

          }
        }
      }

============

ghost · 2023-04-25T23:18:37Z

and this is the error from PBI workspace after deploying

Data source error: Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh.
Cluster URI: WABI-WEST-US-redirect.analysis.windows.net
Activity ID: ***********
Request ID: **********
Time: 2023-04-25 23:06:47Z

mthierba · 2023-04-26T16:03:23Z

Hi @dfg2023,

the OAuth2 credentials support for cloud data sources currently has various limitations:

Only data sources which accept Azure AD access tokens are supported (that excludes Snowflake in your case)
Due to the automated nature of pbi-tools deployments, only non-interactive authentication flows are supported, specifically only client credentials aka service principals at this point

The first limitation can be addressed in a future release of pbi-tools, however, the second one, by design, can never be removed. All OAuth2 credentials that require an interactive login need to be supplied via the Power BI service portal (directly on the dataset or via an Enterprise Gateway).

Regards,
Mathias

mthierba · 2023-04-26T16:09:17Z

@dfg2023 Unless you have integrated your Snowflake instance with Azure AD, and client credentials are accepted?

ghost · 2023-04-26T17:17:40Z

Hi mthierba,
Thanks for your response.
Yes, our snowflake is with azure ad, client credentials are accepted.

here is deployment logs; it's working fine upto deployment, then failing while initiating for refresh! hope it helps. Thanks again.

2023-04-26T05:27:08.6770183Z Starting dataset refresh (Full) ...
2023-04-26T05:27:10.1781209Z Using default refresh type: Full
2023-04-26T05:27:10.1782254Z Refreshing model (Full)...
2023-04-26T05:27:10.8293208Z An unhandled exception occurred.
2023-04-26T05:27:10.8295251Z System.AggregateException: One or more errors occurred. (Refresh failed because of missing credentials. See https://docs.microsoft.com/power-bi/enterprise/service-premium-connect-tools#setting-data-source-credentials for further details.)
2023-04-26T05:27:10.8296953Z ---> PbiTools.Deployments.DeploymentException: Refresh failed because of missing credentials. See https://docs.microsoft.com/power-bi/enterprise/service-premium-connect-tools#setting-data-source-credentials for further details.
2023-04-26T05:27:10.8301129Z ---> Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: '{"error":{"code":"DMTS_DatasourceHasNoCredentialError","pbi.error":{"code":"DMTS_DatasourceHasNoCredentialError","details":[{"code":"Url","detail":{"type":1,"value":"Snowflake"}},{"code":"ConnectionType","detail":{"type":0,"value":"Extension"}}],"exceptionCulprit":1}}}
2023-04-26T05:27:10.8301801Z
2023-04-26T05:27:10.8302209Z Technical Details:
2023-04-26T05:27:10.8302896Z RootActivityId: 0d5c1252-fbd9-47f8-8abc-bbca53b263e6
2023-04-26T05:27:10.8304321Z Date (UTC): 4/26/2023 5:27:10 AM
2023-04-26T05:27:10.8304749Z '.
2023-04-26T05:27:10.8305198Z at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveFlags flags, Int32 maxParallelism)
2023-04-26T05:27:10.8305704Z at Microsoft.AnalysisServices.Tabular.Model.SaveChanges()
2023-04-26T05:27:10.8306392Z at PbiTools.Deployments.XmlaRefreshManager.RunRefresh() in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\XmlaRefreshManager.cs:line 69
2023-04-26T05:27:10.8307077Z --- End of inner exception stack trace ---
2023-04-26T05:27:10.8310337Z at PbiTools.Deployments.XmlaRefreshManager.RunRefresh() in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\XmlaRefreshManager.cs:line 112
2023-04-26T05:27:10.8311353Z at PbiTools.Deployments.DeploymentManager.DeployDatasetAsync(PbiDeploymentManifest manifest, String label, String environment) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\DeploymentManager.Dataset.cs:line 443
2023-04-26T05:27:10.8312283Z at PbiTools.Deployments.DeploymentManager.DeployAsync(String profileName, String environment) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\DeploymentManager.cs:line 84
2023-04-26T05:27:10.8312870Z --- End of inner exception stack trace ---
2023-04-26T05:27:10.8409053Z at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
2023-04-26T05:27:10.8409536Z at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
2023-04-26T05:27:10.8409820Z at System.Threading.Tasks.Task.Wait()
2023-04-26T05:27:10.8411254Z at PbiTools.Cli.CmdLineActions.Deploy(String folder, String label, String environment, String basePath, Boolean whatIf) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Cli\Deploy.cs:line 41
2023-04-26T05:27:10.8411865Z --- End of stack trace from previous location ---
2023-04-26T05:27:10.8412093Z at PowerArgs.ArgAction.Invoke()
2023-04-26T05:27:10.8412497Z at PbiTools.Program.Main(String[] args) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Program.cs:line 89
2023-04-26T05:27:10.8412768Z /home/vsts/work/1/s/pbixs
2023-04-26T05:27:10.8434354Z total 12
2023-04-26T05:27:10.8434767Z drwxr-xr-x 3 vsts docker 4096 Apr 26 05:27 .
2023-04-26T05:27:10.8435370Z drwxr-xr-x 6 vsts docker 4096 Apr 26 05:26 ..
2023-04-26T05:27:10.8435732Z drwxr-xr-x 5 vsts docker 4096 Apr 26 05:26 customer_demographics
2023-04-26T05:27:10.8523977Z ##[section]Finishing: Command Line Script

mthierba · 2023-04-26T17:24:42Z

Understood. Alongside type: OAuth2, you'll need to specify "tenantId", "clientId", "clientSecret" (all string), and "scopes" (as string array). Those settings will then use to retrieve an access token for that refresh.

ghost · 2023-04-26T18:18:36Z

Thank you; still it's not working after adding "tenantId", "clientId", "clientSecret", and "scopes"...

Pasting - 1) the latest deployment logs and 2) .pbixproj.json
Please suggest if there is anything wrong in .pbixproj.json.

logs start <<
....
....
2023-04-26T18:11:08.1370804Z Report Created: 04/26/2023 18:11:07
2023-04-26T18:11:08.1374044Z Report Updated: 04/26/2023 18:11:07
2023-04-26T18:11:08.1435389Z Starting dataset refresh (Full) ...
2023-04-26T18:11:09.6632347Z Using default refresh type: Full
2023-04-26T18:11:09.6633242Z Refreshing model (Full)...
2023-04-26T18:11:10.3911291Z An unhandled exception occurred.
2023-04-26T18:11:10.3913452Z System.AggregateException: One or more errors occurred. (Refresh failed because of missing credentials. See https://docs.microsoft.com/power-bi/enterprise/service-premium-connect-tools#setting-data-source-credentials for further details.)
2023-04-26T18:11:10.3915405Z ---> PbiTools.Deployments.DeploymentException: Refresh failed because of missing credentials. See https://docs.microsoft.com/power-bi/enterprise/service-premium-connect-tools#setting-data-source-credentials for further details.
2023-04-26T18:11:10.3919190Z ---> Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: '{"error":{"code":"DMTS_DatasourceHasNoCredentialError","pbi.error":{"code":"DMTS_DatasourceHasNoCredentialError","details":[{"code":"Url","detail":{"type":1,"value":"Snowflake"}},{"code":"ConnectionType","detail":{"type":0,"value":"Extension"}}],"exceptionCulprit":1}}}
2023-04-26T18:11:10.3920736Z
2023-04-26T18:11:10.3921382Z Technical Details:
2023-04-26T18:11:10.3922395Z RootActivityId: bdddc6ec-86a0-408c-a5fd-bd090aeb7efe
2023-04-26T18:11:10.3923209Z Date (UTC): 4/26/2023 6:11:09 PM
2023-04-26T18:11:10.3923967Z '.
2023-04-26T18:11:10.3924900Z at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveFlags flags, Int32 maxParallelism)
2023-04-26T18:11:10.3925365Z at Microsoft.AnalysisServices.Tabular.Model.SaveChanges()
2023-04-26T18:11:10.3926117Z at PbiTools.Deployments.XmlaRefreshManager.RunRefresh() in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\XmlaRefreshManager.cs:line 69
2023-04-26T18:11:10.3926551Z --- End of inner exception stack trace ---
2023-04-26T18:11:10.3927080Z at PbiTools.Deployments.XmlaRefreshManager.RunRefresh() in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\XmlaRefreshManager.cs:line 112
2023-04-26T18:11:10.3927939Z at PbiTools.Deployments.DeploymentManager.DeployDatasetAsync(PbiDeploymentManifest manifest, String label, String environment) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\DeploymentManager.Dataset.cs:line 443
2023-04-26T18:11:10.3928642Z at PbiTools.Deployments.DeploymentManager.DeployAsync(String profileName, String environment) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Deployments\DeploymentManager.cs:line 84
2023-04-26T18:11:10.3929053Z --- End of inner exception stack trace ---
2023-04-26T18:11:10.3929319Z at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
2023-04-26T18:11:10.3929634Z at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
2023-04-26T18:11:10.3929897Z at System.Threading.Tasks.Task.Wait()
2023-04-26T18:11:10.3930691Z at PbiTools.Cli.CmdLineActions.Deploy(String folder, String label, String environment, String basePath, Boolean whatIf) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Cli\Deploy.cs:line 41
2023-04-26T18:11:10.3931112Z --- End of stack trace from previous location ---
2023-04-26T18:11:10.3931329Z at PowerArgs.ArgAction.Invoke()
2023-04-26T18:11:10.3931724Z at PbiTools.Program.Main(String[] args) in C:\src-tmdl\pbi-tools-tmdl\src\PBI-Tools\Program.cs:line 89
2023-04-26T18:11:10.4021440Z /home/vsts/work/1/s/pbixs
2023-04-26T18:11:10.4036689Z total 12
2023-04-26T18:11:10.4037502Z drwxr-xr-x 3 vsts docker 4096 Apr 26 18:11 .
2023-04-26T18:11:10.4037978Z drwxr-xr-x 6 vsts docker 4096 Apr 26 18:10 ..
2023-04-26T18:11:10.4084864Z drwxr-xr-x 5 vsts docker 4096 Apr 26 18:10 customer_demographics
2023-04-26T18:11:10.4132207Z ##[section]Finishing: Command Line Script
logs end <<

.pbixproj.json <<
{
"version": "0.12",
"created": "2022-03-01T18:09:54.2556434+00:00",
"deployments": {
"Report": {
"description": "v2: deploy to target env based on git merge",
"mode": "Dataset",
"source": {
"type": "Folder",
"path": "./pbixs/1"
},
"authentication": {
"type": "ServicePrincipal",
"tenantId": "%PBI_TENANT_ID%",
"clientId": "%PBI_CLIENT_ID%",
"clientSecret": "%PBI_CLIENT_SECRET%"
},
"credentials": [
{
"match": {
"datasourceType": "Snowflake",
"connectionDetails": {
"path": "account_name.snowflakecomputing.com;SAMPLE_WH",
"database": "database_name"
}
},
"updateMode": "Always",
"type": "OAuth2",
"tenantId": "%PBI_TENANT_ID%",
"clientId": "%PBI_CLIENT_ID%",
"clientSecret": "%PBI_CLIENT_SECRET%",
"scopes": ["Organizational"]
}
],
"options": {
"refresh": {
"enabled": true,
"skipNewDataset": false,
"method": "XMLA",
"type": "Full",
"objects": {
}
},
"import": {
"nameConflict": "CreateOrOverwrite"
},
"dataset": {
"replaceParameters": true,
"setCredentials": true,
"deployEmbeddedReport": true,
"applyRefreshPolicies": false,
"keepRefreshPolicyPartitions": true
}
},
"parameters": {
"[REPORT_NAME]" : "",
"[TARGET_NAME]" : ""
},
"environments": {
"DEPLOY": {
"workspace": "{{[TARGET_NAME]}}",
"displayName": "{{[REPORT_NAME]}}",
"refresh": {
"objects": {}
},
"parameters": {},
"report": {}
}
}
}
}
}
.pbixproj.json <<

mthierba · 2023-04-26T18:25:54Z

Are you sure "Organizational" is a valid scope for the Snowflake integration? All of the examples given here: https://docs.snowflake.com/en/user-guide/oauth-azure seem to use a session: prefix. Also, do your "match" arguments correspond to the ones listed in the datasources table in the deployment logs? Your example omits that section.

ghost · 2023-04-26T20:17:39Z

will get back to you in couple of days. seems some issues at sf integration. thank you..

mthierba · 2023-05-09T10:05:24Z

For reference, updated release notes with two specific examples how OAuth2 credentials are specified in the deployment manifest:

https://github.com/pbi-tools/pbi-tools/releases/tag/1.0.0-rc.3

christoffp · 2023-10-23T06:31:23Z

I don't see Service Principal as a credential option in the manifest. Is it due to a limitation or lack of documentation in the PowerBI REST API? If not then I would not mind contributing to the code to make it happen. I see the manifest only supports the current options currently:
manifest.credentials[].type: { Basic, Anonymous, OAuth2 }

mthierba added this to the Dataset Deployments milestone Oct 16, 2022

mthierba added a commit that referenced this issue Oct 16, 2022

#195 Set (Cloud) creds during dataset deployment

bbf991c

- Implemented: Basic creds (see: https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource#basic-credentials-example)

mthierba added this to Testing in 1.0.0-rc.2 Jan 6, 2023

mthierba mentioned this issue Mar 7, 2023

Refresh failed because of missing credentials #248

Open

mthierba mentioned this issue Mar 29, 2023

1.0.0-rc.3+preview.1 #255

Merged

mthierba closed this as completed Apr 26, 2023

mthierba reopened this Apr 26, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Set (Cloud) credentials during dataset deployment #195

Set (Cloud) credentials during dataset deployment #195

mthierba commented Oct 16, 2022 •

edited

ghost commented Apr 25, 2023 •

edited by ghost

ghost commented Apr 25, 2023

mthierba commented Apr 26, 2023

mthierba commented Apr 26, 2023

ghost commented Apr 26, 2023 •

edited by ghost

mthierba commented Apr 26, 2023

ghost commented Apr 26, 2023

mthierba commented Apr 26, 2023

ghost commented Apr 26, 2023

mthierba commented May 9, 2023

christoffp commented Oct 23, 2023

Set (Cloud) credentials during dataset deployment #195

Set (Cloud) credentials during dataset deployment #195

Comments

mthierba commented Oct 16, 2022 • edited

Credential Types

Reference

ghost commented Apr 25, 2023 • edited by ghost

ghost commented Apr 25, 2023

mthierba commented Apr 26, 2023

mthierba commented Apr 26, 2023

ghost commented Apr 26, 2023 • edited by ghost

mthierba commented Apr 26, 2023

ghost commented Apr 26, 2023

mthierba commented Apr 26, 2023

ghost commented Apr 26, 2023

mthierba commented May 9, 2023

christoffp commented Oct 23, 2023

mthierba commented Oct 16, 2022 •

edited

ghost commented Apr 25, 2023 •

edited by ghost

ghost commented Apr 26, 2023 •

edited by ghost