Configure credentials programmatically for Power BI

APPLIES TO:  App owns data  User owns data

To configure credentials programmatically for Power BI, follow the steps in this article. Configuring credentials programmatically also allows you to encrypt credentials.

The examples in this article are in .NET in C#. To find examples in Java, Python, and PowerShell, go to the respective repository.

Note

  • The calling user or service principal must be either a semantic model owner or a gateway admin.
  • Cloud data sources and their corresponding credentials are managed at the user level.

Update the credentials flow for data sources

  1. Discover the semantic model's data sources by calling Get Datasources. The response body for each data source contains 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 the credentials string according to the Update Datasource Examples. The contents of the credentials string depends on the type of credentials.

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

    Note

    If you're using cloud data sources, don't follow the next steps in this section. Call Update Datasource to set the credentials using the gateway ID and data source ID that you obtained in step 1.

  3. Retrieve the gateway public key by calling Get Gateway.

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

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

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

    var credentialDetails = new CredentialDetails(
            credentials,
            PrivacyLevel.Private,
            EncryptedConnection.Encrypted,
            credentialsEncryptor);
    
  6. Set credentials by calling Update Datasource.

    pbiClient.Gateways.UpdateDatasource(datasource.GatewayId.Value, datasource.DatasourceId.Value, new UpdateDatasourceRequest(credentialDetails));
    

Configure a new data source for a data gateway

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

  2. Retrieve the gateway ID and public key by calling Get Gateways.

    // Select a gateway
    var gateways = pbiClient.Gateways.GetGateways().Value;
    var gateway = gateways.First();
    
  3. Build credential details by following the procedure that is described in the update credentials flow for data sources section by using the gateway public key that you 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 from the Power BI REST API on an enterprise on-premises gateway, encrypt the credentials value by using the gateway public key.

Note

.NET SDK v3 can also run the following .NET SDK v2 examples.

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 are found when calling get data sources

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

After you create the semantic model, an automatic binding is created between the semantic model and a suitable gateway, which contains matching data sources for all connections. The automatic binding fails if there's no suitable gateway or gateways.

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

To discover gateways that are bindable, use Discover Gateways.