PowerBI Embedded V2 Direct Query - Azure Sql credentials not updated programatically

Anonymous — Fri, 25 Jan 2019 14:41:46 GMT

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?

Re: PowerBI Embedded V2 Direct Query - Azure Sql credentials not updated programatically

dahund — Thu, 07 Mar 2019 08:31:02 GMT

same problem here, seems like credentials string is incorrect but not sure how it should be reflected!

topic PowerBI Embedded V2 Direct Query - Azure Sql credentials not updated programatically in Developer

PowerBI Embedded V2 Direct Query - Azure Sql credentials not updated programatically

Re: PowerBI Embedded V2 Direct Query - Azure Sql credentials not updated programatically