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;
}
Thomisza. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.