topic Re: How do I update the Credentials after I upload a report to POWER BI in Developer

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

Jeff_973 — Tue, 01 Aug 2017 17:59:33 GMT

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.

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

Eric_Zhang — Wed, 02 Aug 2017 07:05:30 GMT

@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);

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

Jeff_973 — Wed, 02 Aug 2017 18:27:11 GMT

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

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

Jeff_973 — Thu, 03 Aug 2017 22:32:55 GMT

What values would I use for the gatewaypublicKeyExponent and gatewaypublicKeyModulus?

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

Anonymous — Fri, 11 Aug 2017 06:24:39 GMT

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);

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

Dan2 — Mon, 26 Feb 2018 21:18:34 GMT

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

gateway.PublicKey.Exponent, gateway.PublicKey.Modulus