How do I update the Credentials after I upload a report to POWER BI

[Jeff_973]

After I publish a report to the Power BI Service, I cannot embed the report until I set the credentials for the source of the data. Currently, I have to log into Power Bi portal and do this manually. I would like to programmatically set the credentials using the Microsoft.PowerBi.API.V2.Models SDK. Is there any good examples out there on how to do this?  

I call the PowerBIClient.Datasets.GetGatewayDatasourcesInGroupWithHttpMessagesAsync to get a reference to the GatewayDatasource object and then call the PowerBIClient.Gateways.UpdateDatasourceWithHttpMessagesAsync to update the credentials. Is this correct?

One issue I am having is constructing a CredentialDetails obj which is needed to create an UpdateDatasourceRequest object which is one of the parameters to call the UpdateDatasource method.

[Dan2]

I am having the same issue. Any updates on where to get the below values? 

gateway.PublicKey.Exponent, gateway.PublicKey.Modulus

[Eric_Zhang]

---

@Jeff_973 wrote:

After I publish a report to the Power BI Service, I cannot embed the report until I set the credentials for the source of the data. Currently, I have to log into Power Bi portal and do this manually. I would like to programmatically set the credentials using the Microsoft.PowerBi.API.V2.Models SDK. Is there any good examples out there on how to do this?  

I call the PowerBIClient.Datasets.GetGatewayDatasourcesInGroupWithHttpMessagesAsync to get a reference to the GatewayDatasource object and then call the PowerBIClient.Gateways.UpdateDatasourceWithHttpMessagesAsync to update the credentials. Is this correct?

One issue I am having is constructing a CredentialDetails obj which is needed to create an UpdateDatasourceRequest object which is one of the parameters to call the UpdateDatasource method. 

---

@Jeff_973

You could check my last reply in this thread.

public static class AsymmetricKeyEncryptionHelper
    {

        private const int SegmentLength = 85;
        private const int EncryptedLength = 128;


        /// <summary>
        /// 
        /// </summary>
        /// <param name="userName"></param> the datasouce user name
        /// <param name="password"></param> the datasource password
        /// <param name="gatewaypublicKeyExponent"></param> gateway publicKey Exponent field, you can get it from the get gateways api response json
        /// <param name="gatewaypublicKeyModulus"></param> gateway publicKey Modulus field, you can get it from the get gateways api response json
        /// <returns></returns>
        public static string EncodeCredentials(string userName, string password, string gatewaypublicKeyExponent, string gatewaypublicKeyModulus)
        {
            // using json serializer to handle escape characters in username and password
            var plainText = string.Format("{{\"credentialData\":[{{\"value\":{0},\"name\":\"username\"}},{{\"value\":{1},\"name\":\"password\"}}]}}", JsonConvert.SerializeObject(userName), JsonConvert.SerializeObject(password));
            using (RSACryptoServiceProvider rsa = new RSACryptoServiceProvider(EncryptedLength * 8))
            {
                var parameters = rsa.ExportParameters(false);
                parameters.Exponent = Convert.FromBase64String(gatewaypublicKeyExponent);
                parameters.Modulus = Convert.FromBase64String(gatewaypublicKeyModulus);
                rsa.ImportParameters(parameters);
                return Encrypt(plainText, rsa);
            }
        }

        private static string Encrypt(string plainText, RSACryptoServiceProvider rsa)
        {
            byte[] plainTextArray = Encoding.UTF8.GetBytes(plainText);

            // Split the message into different segments, each segment's length is 85. So the result may be 85,85,85,20.
            bool hasIncompleteSegment = plainTextArray.Length % SegmentLength != 0;

            int segmentNumber = (!hasIncompleteSegment) ? (plainTextArray.Length / SegmentLength) : ((plainTextArray.Length / SegmentLength) + 1);

            byte[] encryptedData = new byte[segmentNumber * EncryptedLength];
            int encryptedDataPosition = 0;

            for (var i = 0; i < segmentNumber; i++)
            {
                int lengthToCopy;

                if (i == segmentNumber - 1 && hasIncompleteSegment)
                    lengthToCopy = plainTextArray.Length % SegmentLength;
                else
                    lengthToCopy = SegmentLength;

                var segment = new byte[lengthToCopy];

                Array.Copy(plainTextArray, i * SegmentLength, segment, 0, lengthToCopy);

                var segmentEncryptedResult = rsa.Encrypt(segment, true);

                Array.Copy(segmentEncryptedResult, 0, encryptedData, encryptedDataPosition, segmentEncryptedResult.Length);

                encryptedDataPosition += segmentEncryptedResult.Length;
            }

            return Convert.ToBase64String(encryptedData);
        }
    }

You can use it as

var credentials = AsymmetricKeyEncryptionHelper.EncodeCredentials(username, password, gateway.PublicKey.Exponent, gateway.PublicKey.Modulus);

[Jeff_973]

What values would I use for the gatewaypublicKeyExponent and gatewaypublicKeyModulus?

[Jeff_973]

I don't believe I have Gateway Exponent or Modules. I am using Direct Query mode for my PBIX. After uploading the report to the Power BI Service I have to update the credentials.  Here is the code from the Provision example available on GITHUB: 

using (var client = await CreateClient())
{
// Get the datasources from the dataset
var datasources = await client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, datasetId);

// Reset your connection credentials
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = username,
Password = password
}
};

ExecutionReport report = null;
switch (datasources.Value.Count)
{
case 0: return new ExecutionReport(ExecutionLevel.Error, "No datasources exist to update");
case 1:
report = new ExecutionReport(ExecutionLevel.OK, "Connection credentials updated successfully.");
break;
default:
report = new ExecutionReport(ExecutionLevel.Warning, string.Format("Expected one datasource, but {0} exist, Connection credentials updated for the first", datasources.Value.Count));
break;
}

// Update the datasource with the specified credentials
await client.Gateways.PatchDatasourceAsync(workspaceCollectionName, workspaceId, datasources.Value[0].GatewayId, datasources.Value[0].Id, delta);
return report;
}

This is basically what I want to do but I don't see the PatchDatasourceAsync method in the Microsoft.PowerBI.APi.V2.Model Library. 

Here is the test code I have written to try an accomplish the update but I keep getting an internalServerError:

--I removed the two parameters from the code above becuase I dont have a Gateway setup since my data is in the azure cloud.

var credentials = AsymmetricKeyEncryptionHelper.EncodeCredentials("****", "****");

var result = PowerBIClient.Datasets.GetGatewayDatasourcesInGroupWithHttpMessagesAsync(groupid, datasetid).Result.Body.Value;

CredentialDetails cd = new CredentialDetails();
cd.Credentials = credentials;
cd.CredentialType = "Basic";
cd.EncryptedConnection = "Encrypted";
cd.EncryptionAlgorithm = "RSA-OAEP";
cd.PrivacyLevel = "Public";

UpdateDatasourceRequest udr = new UpdateDatasourceRequest(cd);

var update = PowerBIClient.Gateways.UpdateDatasourceWithHttpMessagesAsync(result.First().GatewayId, result.First().Id, udr).Result;

Thank you 

[Anonymous]

I'm having the exact same problem.  I've converted pretty much the rest of the old Provision sample app to the V2 API.  But this is the last bit of code I need to fix...

var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = username,
Password = password
}
};

await client.Gateways.PatchDatasourceAsync(workspaceCollectionName, workspaceId, datasources.Value[0].GatewayId, datasources.Value[0].Id, delta);