Configure credentials programmatically for Power BI

Follow these steps to configure credentials programmatically for Power BI.

Update credentials flow for data sources

  1. Call Get Datasources to discover the data sources of the dataset. In the response body for each data source, are the type, connection details, gateway, and data source ID.

    // Select a datasource
    var datasources = pbiClient.Datasets.GetDatasources(datasetId).Value;
    var datasource = datasources.First();
    
  2. Build credentials string according to Update Datasource Examples depending on the credentials type.

    var credentials =  new BasicCredentials(username: "username", password :"*****");
    
  3. Call Get Gateway to retrieve the gateway public key.

    var gateway = pbiClient.Gateways.GetGatewayById(datasource.GatewayId);
    
  4. Encrypt the credentials.

    var credentialsEncryptor = new AsymmetricKeyEncryptor(gateway.publicKey);
    
  5. Build credential details with encrypted credentials.

    Use the AssymetricKeyEncriptor class with the public key retrieved in Step 3.

    var credentialDetails = new CredentialDetails(
            credentials,
            PrivacyLevel.Private,
            EncryptedConnection.Encrypted,
            credentialsEncryptor);
    
  6. Call Update Datasource to set credentials.

    pbiClient.Gateways.UpdateDatasource(gatewayId, datasourceId, credentialDetails);
    

Configure a new data source for a data gateway

  1. Install the On-premises data gateway on your machine.

  2. Call Get Gateways to retrieve the gateway ID and public key.

    // Select a gateway
    var gateways = pbiClient.Gateways.GetGateways().Value;
    var gateway = gateways.First();
    
  3. Build credential details in the same way as described in update credentials flow for data sources, using the gateway public key retrieved in step 2.

  4. Build the request body.

    var request = new PublishDatasourceToGatewayRequest(
            dataSourceType: "SQL",
            connectionDetails: "{\"server\":\"myServer\",\"database\":\"myDatabase\"}",
            credentialDetails: credentialDetails,
            dataSourceName: "my sql datasource");
    
  5. Call the Create Datasource API.

    pbiClient.Gateways.CreateDatasource(gateway.Id, request);
    

Credential types

When you call Create Datasource or Update Datasource under an enterprise on-prem gateway using Power BI Rest API, the credentials value needs to be encrypted using the gateway's public key.

Note

.NET SDK v3 can also run the .NET SDK v2 examples listed below.

Windows and basic credentials

// Windows credentials
var credentials = new WindowsCredentials(username: "john", password: "*****");

// Or

// Basic credentials
var credentials = new BasicCredentials(username: "john", password: "*****");

var credentialsEncryptor = new AsymmetricKeyEncryptor(publicKey);
var credentialDetails = new CredentialDetails(credentials, PrivacyLevel.Private, EncryptedConnection.Encrypted, credentialsEncryptor);

Key credentials

var credentials = new KeyCredentials("TestKey");
var credentialsEncryptor = new AsymmetricKeyEncryptor(publicKey);
var credentialDetails = new CredentialDetails(credentials, PrivacyLevel.Private, EncryptedConnection.Encrypted, credentialsEncryptor);

OAuth2 credentials

var credentials = new OAuth2Credentials("TestToken");
var credentialsEncryptor = new AsymmetricKeyEncryptor(publicKey);
var credentialDetails = new CredentialDetails(credentials, PrivacyLevel.Private, EncryptedConnection.Encrypted, credentialsEncryptor);

Anonymous credentials

var credentials = new AnonymousCredentials();
var credentialDetails = new CredentialDetails(credentials, PrivacyLevel.Private, EncryptedConnection.NotEncrypted);

Troubleshooting

No gateway and data source ID found when calling get data sources

This issue means the dataset isn't bound to a gateway. When creating a new dataset, for each cloud connection a data source with no credentials is created automatically on the user's cloud gateway. This gateway is used to store the credentials for cloud connections.

After you create the dataset, an automatic binding is created between the dataset and a suitable gateway, which contains matching data sources for all connections. If there's no such gateway or multiple suitable gateways, the automatic binding fails.

If you're using on-premises datasets, create the missing on-premises data sources, and bind the dataset to a gateway manually by using Bind To Gateway.

To discover gateways that could be bound, use Discover Gateways.