I would like to create a Powerbi Datasource to a SQL Server using C# with Oauth2 credentials and an OnPremise datagateway.

I use the credentials of an Application Registration in my Azure tenant to access the SQL Server. I am already able to create the datasource from the portal using the same credentials, but would like to do it programmatically in C#. I followed this great video https://www.youtube.com/watch?v=VoZoumwaZsY to get started, unfortunately, i am unable to create a datasource, as I receive a “Bad request” with not much information.

However, what is interesting, is that I am able to update a datasource (if I first create it from the PowerBi portal), and if I change the second token scope to “https://database.windows.net/.default” (first token is for access powerbi API with scope: “https://analysis.windows.net/powerbi/api/.default”, second is for datasource access).

What worries me, is that in the video, at 20:20, he says that “The build-in Power BI connectors do not support connecting as service principal”. I am not sure I understand this phrase, as I am able to create a connection on PBI portal to a SQL Server using a Service principal.
Should i use another scope to create a datasource ? Or the problem lies elsewhere ?

The failing code to create the datasource
I receive a 400 Bad Request

Guid gatewayId = new Guid(AppSettings.ONPREM_GATEWAY_ID);

var tokenCredentials = new TokenCredentials(GetAccessTokenForServicePrincipal(), "Bearer");

PowerBIClient pbiClient = new PowerBIClient(new Uri(urlPowerBiServiceApiRoot), tokenCredentials);

var gateway = pbiClient.Gateways.GetGateway(gatewayId);

// configure datasource connection details
string connectionDetails =
     JsonSerializer.Serialize(new {
         server = AppSettings.SQL_SERVER,
         database = AppSettings.SQL_DATABASE
     });

// create encryptor from Gateway's public key
var credentialsEncryptor = new AsymmetricKeyEncryptor(gateway.PublicKey);

var dbTokenCredentials = GetAccessTokenForServicePrincipal();

// create credential details object with Basic Credentials
var credentialDetails = new CredentialDetails(
     new OAuth2Credentials(dbTokenCredentials),
     PrivacyLevel.Private,
     EncryptedConnection.Encrypted,
     credentialsEncryptor
);
PublishDatasourceToGatewayRequest requestToAddDatasource = new PublishDatasourceToGatewayRequest {
     DataSourceName = "TEST DATASOURCE",
     DataSourceType = "Sql",
     ConnectionDetails = connectionDetails,
     CredentialDetails = createCredentialDetails
};

pbiClient.Gateways.CreateDatasource(gatewayId, requestToAddDatasource); // 400 BAD REQUEST

The successful code to update the datasource

Guid gatewayId = new Guid(AppSettings.ONPREM_GATEWAY_ID);
Guid datasourceId = new Guid(AppSettings.DATASOURCE_ID);

var tokenCredentials = new TokenCredentials(GetAccessTokenForServicePrincipal(), "Bearer");

PowerBIClient pbiClient = new PowerBIClient(new Uri(urlPowerBiServiceApiRoot), tokenCredentials);

var gateway = pbiClient.Gateways.GetGateway(gatewayId);

// configure datasource connection details
string connectionDetails =
     JsonSerializer.Serialize(new {
         server = AppSettings.SQL_SERVER,
         database = AppSettings.SQL_DATABASE
     });

// create encryptor from Gateway's public key
var credentialsEncryptor = new AsymmetricKeyEncryptor(gateway.PublicKey);

var dbTokenCredentials = GetAccessTokenForServicePrincipal(**["https://database.windows.net/.default"]**);

// create credential details object with Basic Credentials
var credentialDetails = new CredentialDetails(
     new OAuth2Credentials(dbTokenCredentials),
     PrivacyLevel.Private,
     EncryptedConnection.Encrypted,
     credentialsEncryptor
);

UpdateDatasourceRequest updateDatasourceRequest = new UpdateDatasourceRequest {
   CredentialDetails = credentialDetails
};


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

public static string GetAccessTokenForServicePrincipal(string[] scopes = null) {
        if(scopes == null || scopes.Length == 0) {
            scopes = new string[] { "https://analysis.windows.net/powerbi/api/.default" };
        }
        

        var appConfidential = ConfidentialClientApplicationBuilder.Create(APP_REGISTRATION_CLIENT_ID)
                                .WithClientSecret(APP_REGISTRATION_SECRET)
                                .WithAuthority("https://login.microsoftonline.com/"+MY_TENANT)
                                .Build();


        var authResult = appConfidential.AcquireTokenForClient(scopes).ExecuteAsync().Result;
        return authResult.AccessToken;
        
    }