Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm programatically creating a workspace and importing a Direct-Query report into PowerBI Embedded V2. Everything works fine except the report data source credentials which are not updated.
This is the code for the import flow:
await _powerBIService.ImportPbixAsync(newWorkspaceId, reportNameWithoutExtension, fileStream); Console.WriteLine("Imported report {0} for {1} ({2})", reportNameWithoutExtension, persona.Name, persona.Id); string datasetId = null; while (datasetId == null) { //get DataSource Id Thread.Sleep(5000); datasetId = await _powerBIService.GetDatasetIdFromWorkspace(newWorkspaceId, reportNameWithoutExtension); } //update the connection details await _powerBIService.UpdateConnectionAsync(newWorkspaceId, datasetId, persona.SQLUser, persona.SQLPassword); Console.WriteLine("Updated connection details for dataset {0}", reportNameWithoutExtension); // get gateway ID var gatewayId = await _powerBIService.GetGatewayIdFromWorkspaceAndDataset(newWorkspaceId, datasetId); if (gatewayId != null) { //update credentials await _powerBIService.UpdateGatewayDatasourcesCredentials(gatewayId, persona.SQLUser, persona.SQLPassword); Console.WriteLine("Updated connection details for gateway {0}", reportNameWithoutExtension); }
These are the individual methods:
public async Task UpdateConnectionAsync(string workspaceId, string datasetId, string sqlUser, string sqlPwd) { var bearerToken = await GetBearerTokenAsync(); var tokenCredentials = new TokenCredentials(bearerToken, "Bearer"); using (var client = new PowerBIClient(new Uri(_pbiApiUrl), tokenCredentials)) { var dataSourcesResponse = await client.Datasets.GetDatasourcesInGroupAsync(workspaceId, datasetId); var sqlDataSources = dataSourcesResponse.Value?.Where(s => s.DatasourceType == "Sql") .Where(s => !s.ConnectionString.Contains(_reportsSQLServer) || !s.ConnectionString.Contains(_reportsSQLServer)); foreach (var sqlDataSource in sqlDataSources) { var updateDataSourceConnectionRequest = new UpdateDatasourceConnectionRequest(); updateDataSourceConnectionRequest.ConnectionDetails = new DatasourceConnectionDetails(_reportsSQLServer, _reportsSQLDatabase); //updateDataSourceConnectionRequest.DatasourceSelector = new Datasource(datasourceId: sqlDataSource.DatasourceId); var datasourcesRequest = new UpdateDatasourcesRequest(); datasourcesRequest.UpdateDetails = new List<UpdateDatasourceConnectionRequest>() { updateDataSourceConnectionRequest }; var result = await client.Datasets.UpdateDatasourcesInGroupAsync(workspaceId, datasetId, datasourcesRequest); //await client.Gateways.UpdateDatasourceAsync() } } } public async Task UpdateGatewayDatasourcesCredentials(string gatewayId, string sqlUser, string sqlPassword) { var bearerToken = await GetBearerTokenAsync(); var tokenCredentials = new TokenCredentials(bearerToken, "Bearer"); using (var client = new PowerBIClient(new Uri(_pbiApiUrl), tokenCredentials)) { var datasourcesResult = await client.Gateways.GetDatasourcesAsync(gatewayId); var sqlGatewayDatasources = datasourcesResult.Value? .Where(s => s.DatasourceType == "Sql") .Where(s => s.ConnectionDetails.Contains(_reportsSQLServer) && s.ConnectionDetails.Contains(_reportsSQLDatabase)); foreach (var gatewayDatasource in sqlGatewayDatasources) { var updateDataSourceRequest = new UpdateDatasourceRequest(); updateDataSourceRequest.CredentialDetails = new CredentialDetails(); updateDataSourceRequest.CredentialDetails.CredentialType = "Basic"; updateDataSourceRequest.CredentialDetails.Credentials = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"" + sqlUser + "\"},{\"name\":\"password\", \"value\":\"" + sqlPassword + "\"}]}"; updateDataSourceRequest.CredentialDetails.EncryptedConnection = "Encrypted"; updateDataSourceRequest.CredentialDetails.EncryptionAlgorithm = "None"; updateDataSourceRequest.CredentialDetails.PrivacyLevel = "None"; var result = await client.Gateways.UpdateDatasourceAsync(gatewayId, gatewayDatasource.Id, updateDataSourceRequest); } } }
When I attempt to view the report there is no data being fetched from the database.
Any suggestions on what is missing here?
same problem here, seems like credentials string is incorrect but not sure how it should be reflected!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
1 | |
1 | |
1 |