public static void PatchSqlDatasourceCredentials(Guid WorkspaceId, string DatasetId, string UserName, string UserPassword)
{
PowerBIClient pbiClient = TokenManager.GetPowerBiAppOnlyClient();
var datasources = (pbiClient.Datasets.GetDatasourcesInGroup(WorkspaceId, DatasetId)).Value;
// find the target SQL datasource
foreach (var datasource in datasources)
{
if (datasource.DatasourceType.ToLower() == "sql")
{
// get the datasourceId and the gatewayId
var datasourceId = datasource.DatasourceId;
var gatewayId = datasource.GatewayId;
// Create UpdateDatasourceRequest to update Azure SQL datasource credentials
UpdateDatasourceRequest req = new UpdateDatasourceRequest {
CredentialDetails = new CredentialDetails(
new BasicCredentials(UserName, UserPassword),
PrivacyLevel.None,
EncryptedConnection.NotEncrypted)
};
// Execute Patch command to update Azure SQL datasource credentials
pbiClient.Gateways.UpdateDatasource((Guid)gatewayId, (Guid)datasourceId, req);
}
}
;
}
/// <summary>
/// Updates Datasource credentials
/// </summary>
/// <param name="gatewayId">Gateway Id of corresponding dataset</param>
/// <param name="datasourceId">Datasource Id of corresponding gateway</param>
/// <param name="dataSourceRequest">Request body for Update Datasource API</param>
public void UpdateDatasource(Guid gatewayId, Guid datasourceId, UpdateDatasourceRequest dataSourceRequest)
{
PowerBIClient pbiClient = this.GetPowerBIClient();
// Update credentials
pbiClient.Gateways.UpdateDatasource(gatewayId, datasourceId, dataSourceRequest);
}
public static async Task PatchAnonymousDatasourceCredentials(string WorkspaceId, string importName)
{
PowerBIClient pbiClient = GetPowerBiClient();
var datasets = (await pbiClient.Datasets.GetDatasetsInGroupAsync(WorkspaceId)).Value;
foreach (var dataset in datasets)
{
if (importName.Equals(dataset.Name))
{
string datasetId = dataset.Id;
var datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(WorkspaceId, datasetId)).Value;
foreach (var datasource in datasources)
{
if (datasource.DatasourceType == "OAuth" || datasource.DatasourceType == "File")
{
var datasourceId = datasource.DatasourceId;
var gatewayId = datasource.GatewayId;
// create credentials for Azure SQL database log in
CredentialDetails details = new CredentialDetails("");
UpdateDatasourceRequest req = new UpdateDatasourceRequest(details);
// Update credentials through gateway
await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId, datasourceId, req);
}
}
}
}
return;
}
public IActionResult UpdateDatasource(UpdateDatasourceMap updateDatasourceMap)
{
try
{
// Capture credential details
var credentialDetails = powerBIService.GetCredentialDetails(updateDatasourceMap.GatewayId, updateDatasourceMap.CredentialType, updateDatasourceMap.Credentials, updateDatasourceMap.PrivacyLevel);
// Create datasource request body for Updating the datasource
var dataSourceRequest = new UpdateDatasourceRequest {
CredentialDetails = credentialDetails
};
// Update gateway credentials
powerBIService.UpdateDatasource(updateDatasourceMap.GatewayId, updateDatasourceMap.DatasourceId, dataSourceRequest);
return(Ok("Successfully updated data source credentials"));
}
catch (HttpOperationException ex)
{
Console.Error.WriteLine(ex.Message + "\n\n" + ex.StackTrace);
// Set status code of the response
Response.StatusCode = (Int32)ex.Response.StatusCode;
return(Content("Error " + Response.StatusCode + " " + ex.Message));
}
catch (Exception ex)
{
Console.Error.WriteLine(ex.Message + "\n\n" + ex.StackTrace);
return(BadRequest(ex.Message));
}
}
/// <summary>
/// Update data source credentials on Workspace defined <paramref name="config"/> using the Power BI client instance defined on <paramref name="client"/>.
/// </summary>
/// <param name="config">Power BI Workspace and datasource configuration</param>
/// <param name="client">Power BI client instance</param>
static void UpdateDatasourceCredentials(PowerBIWorkspaceConfig config, PowerBIClient client)
{
var userName = config.DatasourceUserID;
var password = config.DatasourceUserPassword;
var credentials = string.Format("{{\"credentialData\":[{{\"name\":\"username\",\"value\":{0}}},{{\"name\":\"password\",\"value\":{1}}}]}}", JsonConvert.SerializeObject(userName), JsonConvert.SerializeObject(password));
var requestBody = new UpdateDatasourceRequest
{
CredentialDetails = new CredentialDetails
{
Credentials = credentials,
CredentialType = "Basic",
EncryptedConnection = "Encrypted",
EncryptionAlgorithm = "None",
PrivacyLevel = "None"
}
};
var reports = client.Reports.GetReportsInGroupAsync(config.PowerBIGroupID).Result;
foreach (Report report in reports.Value)
{
Console.WriteLine("Report Name: {0}, Group ID: {1}, Updating to Datasource UserName: {2}", report.Name, config.PowerBIGroupID, config.DatasourceUserID);
try
{
var dataSources = client.Datasets.GetDatasources(config.PowerBIGroupID, report.DatasetId).Value;
var gatewayId = dataSources.First().GatewayId;
var datasourceId = dataSources.First().DatasourceId;
client.Gateways.UpdateDatasource(gatewayId, datasourceId, requestBody);
Console.WriteLine("Success");
}
catch (Exception ex)
{
Console.WriteLine("Error {0}", ex.Message);
}
}
}
public static void PatchAnonymousDatasourceCredentials(Guid WorkspaceId, string DatasetId)
{
PowerBIClient pbiClient = TokenManager.GetPowerBiClient(requiredScopes);
var datasources = pbiClient.Datasets.GetDatasourcesInGroup(WorkspaceId, DatasetId).Value;
foreach (var datasource in datasources)
{
if (datasource.DatasourceType == "OAuth" || datasource.DatasourceType == "File")
{
var datasourceId = datasource.DatasourceId;
var gatewayId = datasource.GatewayId;
// create credentials for Azure SQL database log in
CredentialDetails details = new CredentialDetails {
CredentialType = CredentialType.Anonymous,
PrivacyLevel = PrivacyLevel.None
};
UpdateDatasourceRequest req = new UpdateDatasourceRequest(details);
// Update credentials through gateway
pbiClient.Gateways.UpdateDatasourceAsync((Guid)gatewayId, (Guid)datasourceId, req);
}
}
return;
}
public async Task UpdateCredentials(Guid groupId)
{
using (var client = await _pbiService.GetPowerBIClient())
{
var datasources = await client.Datasets.GetDatasetsInGroupAsync(groupId);
var dataSetId = datasources.Value.First().Id;
var gatewayresponse = await client.Datasets.GetDatasourcesInGroupAsync(groupId, dataSetId);
var gateway = gatewayresponse.Value.First();
if (gateway == null)
{
throw new Exception($"Gateway is null for {groupId}");
}
var updateRequest = new UpdateDatasourceRequest
{
CredentialDetails = new CredentialDetails(
new BasicCredentials(_settings.DatabaseUserName, _settings.DatabasePassword),
PrivacyLevel.Private,
EncryptedConnection.Encrypted)
};
try
{
client.Gateways.UpdateDatasource(gateway.GatewayId.Value, gateway.DatasourceId.Value, updateRequest);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return;
}
/// <summary>
/// Updates the credentials of the specified datasource from the specified
/// gateway.
/// </summary>
/// <remarks>
/// <br/>To get the gateway and datasource ids for a dataset, use [Get
/// Datasources](/rest/api/power-bi/datasets/getdatasources) or [Get
/// Datasources In
/// Group](/rest/api/power-bi/datasets/getdatasourcesingroup)<br/><br/>**Required
/// scope**: Dataset.ReadWrite.All <br/>To set the permissions scope, see
/// [Register an
/// app](https://docs.microsoft.com/power-bi/developer/register-app).
/// </remarks>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='gatewayId'>
/// The gateway id
/// </param>
/// <param name='datasourceId'>
/// The datasource id
/// </param>
/// <param name='updateDatasourceRequest'>
/// The update datasource request
/// </param>
/// <param name='cancellationToken'>
/// The cancellation token.
/// </param>
public static async Task <object> UpdateDatasourceAsync(this IGateways operations, string gatewayId, string datasourceId, UpdateDatasourceRequest updateDatasourceRequest, CancellationToken cancellationToken = default(CancellationToken))
{
using (var _result = await operations.UpdateDatasourceWithHttpMessagesAsync(gatewayId, datasourceId, updateDatasourceRequest, null, cancellationToken).ConfigureAwait(false))
{
return(_result.Body);
}
}
/// <summary>
/// Updates the credentials of the specified datasource from the specified
/// gateway.
/// </summary>
/// <remarks>
/// <br/>To get the gateway and datasource ids for a dataset, use [Get
/// Datasources](/rest/api/power-bi/datasets/getdatasources) or [Get
/// Datasources In
/// Group](/rest/api/power-bi/datasets/getdatasourcesingroup)<br/><br/>**Required
/// scope**: Dataset.ReadWrite.All <br/>To set the permissions scope, see
/// [Register an
/// app](https://docs.microsoft.com/power-bi/developer/register-app).
/// </remarks>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='gatewayId'>
/// The gateway id
/// </param>
/// <param name='datasourceId'>
/// The datasource id
/// </param>
/// <param name='updateDatasourceRequest'>
/// The update datasource request
/// </param>
public static object UpdateDatasource(this IGateways operations, string gatewayId, string datasourceId, UpdateDatasourceRequest updateDatasourceRequest)
{
return(operations.UpdateDatasourceAsync(gatewayId, datasourceId, updateDatasourceRequest).GetAwaiter().GetResult());
}
/// <summary>
/// Updates the credentials of the specified datasource from the specified
/// gateway.
/// </summary>
/// <remarks>
/// <br/>To get the gateway and datasource ids for a dataset, use [Get
/// Datasources](/rest/api/power-bi/datasets/getdatasources) or [Get
/// Datasources In
/// Group](/rest/api/power-bi/datasets/getdatasourcesingroup)<br/><br/>**Required
/// scope**: Dataset.ReadWrite.All <br/>To set the permissions scope, see
/// [Register an
/// app](https://docs.microsoft.com/power-bi/developer/register-app).
/// </remarks>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='gatewayId'>
/// The gateway id
/// </param>
/// <param name='datasourceId'>
/// The datasource id
/// </param>
/// <param name='updateDatasourceRequest'>
/// The update datasource request
/// </param>
/// <param name='cancellationToken'>
/// The cancellation token.
/// </param>
public static async Task UpdateDatasourceAsync(this IGatewaysOperations operations, System.Guid gatewayId, System.Guid datasourceId, UpdateDatasourceRequest updateDatasourceRequest, CancellationToken cancellationToken = default(CancellationToken))
{
(await operations.UpdateDatasourceWithHttpMessagesAsync(gatewayId, datasourceId, updateDatasourceRequest, null, cancellationToken).ConfigureAwait(false)).Dispose();
}
/// <summary>
/// Updates the credentials of the specified datasource from the specified
/// gateway.
/// </summary>
/// <remarks>
/// <br/>To get the gateway and datasource ids for a dataset, use [Get
/// Datasources](/rest/api/power-bi/datasets/getdatasources) or [Get
/// Datasources In
/// Group](/rest/api/power-bi/datasets/getdatasourcesingroup)<br/><br/>**Required
/// scope**: Dataset.ReadWrite.All <br/>To set the permissions scope, see
/// [Register an
/// app](https://docs.microsoft.com/power-bi/developer/register-app).
/// </remarks>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='gatewayId'>
/// The gateway id
/// </param>
/// <param name='datasourceId'>
/// The datasource id
/// </param>
/// <param name='updateDatasourceRequest'>
/// The update datasource request
/// </param>
public static void UpdateDatasource(this IGatewaysOperations operations, System.Guid gatewayId, System.Guid datasourceId, UpdateDatasourceRequest updateDatasourceRequest)
{
operations.UpdateDatasourceAsync(gatewayId, datasourceId, updateDatasourceRequest).GetAwaiter().GetResult();
}
