cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jeff_973 Frequent Visitor
Frequent Visitor

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

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.

 

 

 

 

5 REPLIES 5
Highlighted
Moderator Eric_Zhang
Moderator

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


@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 Frequent Visitor
Frequent Visitor

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

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 

 

Jeff_973 Frequent Visitor
Frequent Visitor

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

What values would I use for the gatewaypublicKeyExponent and gatewaypublicKeyModulus?

Anonymous
Not applicable

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

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

 

 

 

 

Dan2 Regular Visitor
Regular Visitor

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

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

gateway.PublicKey.Exponent, gateway.PublicKey.Modulus

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 318 members 3,086 guests
Please welcome our newest community members: