Update Credentials for Import mode Datasources in Power BI via API

Question

I am using the PowerBI API to upload some pbix files. Most of these files are using the Import mode for the SQL.

When I use the rest API to upload the files, the credentials do not get updated on the website. I know the credentials do not live on the actual file. I also know there is API to patch these credentials using the API but I have not been able to make it work with the Import Mode. Only seems to work with DirectQuery.

I have also tried Set All connections which is documented to only work with direct query connections using this format:

Data Source=xxxx.mydb.net; Initial Catalog=dbname;User ID=xxx;Password=xxxx;

My problem now is that the way Power BI manages cached credentials make it hard to figure out which credentials are being used. There is some magic happening where updating one file sometimes makes the other files which use the same credential also allow refresh.

This is the error I am getting for all files uploaded via API.

Data source errorScheduled refresh has been disabled due to at least one data source not having credentials provided. Please provide credentials for all data sources, and then turn scheduled refresh back on.

Cluster This-is-not-relevant.net
Activity ID00000000-0000-0000-0000-000000000000
Request ID00000000-0000-0000-0000-000000000000
Time2020-09-99 99:54:11Z

Thank you,
Chéyo

Chéyo Chéyo · Accepted Answer · 2018-05-08T20:44:56

This is the solution using the PowerBI Csharp SDK. Make sure the JSON payload is property scaped.

var request = new UpdateDatasourceRequest
{
    CredentialDetails = new CredentialDetails
    {
        Credentials = $"{{\"credentialData\":[{{\"name\":\"username\",\"value\":{JsonConvert.SerializeObject(credential.Username)}}},{{\"name\":\"password\",\"value\":{JsonConvert.SerializeObject(credential.Password)}}}]}}",
        CredentialType = "Basic",
        EncryptedConnection = "Encrypted",
        EncryptionAlgorithm = "None",
        PrivacyLevel = "None"
    }
};

await PowerBI.Client().Gateways.UpdateDatasourceAsync(gatewayId: datasource.GatewayId, datasource.DatasourceId, updateDatasourceRequest: request);

Update Credentials for Import mode Datasources in Power BI via API

1 Answers