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

Unable to update datasource credentials using ProcessHttpRequestAsync

Hey Followed another thread in this forum to build a DLL (C#) that will help updating SQL credentials on datasets, code dosnt return any error, but is not processed as PowerBI dashboard will not update, any help would be appricated?

 

Code:

 

public async Task<bool> UpdateSQLDatasource(string gatewayId, string datasourceID, string sqluser, string sqlPassword)
        {
            var getCredentialsResult = await GetTokenCredentials();
            if (!getCredentialsResult)
            {
                // The error message set in GetTokenCredentials
                return false;
            }
            try
            {
                //Create PowerBI Client
                using (var client = new PowerBIClient(new Uri(_ApiUrl), m_tokenCredentials))
                {    
                    var request = new System.Net.Http.HttpRequestMessage(
                        new HttpMethod("PATCH"),
                        $"https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources/{datasourceID}");
                    request.Content = new StringContent(
                    $@" {{
                        'encryptedConnection': 'Encrypted',
                        'encryptionAlgorithm': 'RSA - OAEP',
                        'privacyLevel': 'Public',
                        'credentialType': 'Basic',
                        'basicCredentials': {{
                            'username': '{sqluser}',
                            'password': '{sqlPassword}'
                        }}
                    }}");
                    request.Content.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json");

                    await client.Credentials.ProcessHttpRequestAsync(request, new CancellationToken());

                }

            }
            catch (Exception)
            {
            }
            return true;

        }

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
dahund Frequent Visitor
Frequent Visitor

Re: Unable to update datasource credentials using ProcessHttpRequestAsync

!!!SOLVED!!!

 

So after almost giving in, I decided to test if I could reset password using Postman and a JSON body (https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource#basic_credentials_examp...

 

Postman replied with a msg telling me that the user was unatherized!!!!

 

Apparently PowerBI will test credentials against source and if not working will relply with "Bad request" ...........  DAMM

 

So tried resetting password on SQL server with new Password, then waited a couple of minutes (PowerBI) apparently need some time to realize its data source is broken. Once PowerBI complained about credential i used following method to update with success :-)

 

public async Task<bool> UpdateSQLDatasource(string gatewayId, string datasourceID, string sqluser, string sqlPassword)
        {
            var getCredentialsResult = await GetTokenCredentials();
            if (!getCredentialsResult)
            {
                // The error message set in GetTokenCredentials
                return false;
            }
            try
            {
                //Create PowerBI Client
                using (var client = new PowerBIClient(new Uri(_ApiUrl), m_tokenCredentials))
                {
                    var updateDataSourceRequest = new UpdateDatasourceRequest
                    {
                        CredentialDetails = new CredentialDetails
                        {
                            CredentialType = "Basic",
                            Credentials = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"" + sqluser + "\"},{\"name\":\"password\", \"value\":\"" +sqlPassword+ "\"}]}",
                            EncryptedConnection = "Encrypted",
                            EncryptionAlgorithm = "None",
                            PrivacyLevel = "None"

                        }
                    };
                    var result = await client.Gateways.UpdateDatasourceAsync(gatewayId, datasourceID, updateDataSourceRequest);

                }

            }
            catch (Exception)
            {
            }
            return true;

        }

So if anyone else has issues, YOU CANNOT USE a dummy account......

2 REPLIES 2
dahund Frequent Visitor
Frequent Visitor

Re: Unable to update datasource credentials using ProcessHttpRequestAsync

So found another thread where person was using and instance of "UpdateDataSourceRequst" and "UpdateDataSourceAsync" to update credentials, see code:

 

 var updateDataSourceRequest = new UpdateDatasourceRequest();
                    updateDataSourceRequest.CredentialDetails = new CredentialDetails();
                    updateDataSourceRequest.CredentialDetails.CredentialType = "Basic";
                    updateDataSourceRequest.CredentialDetails.Credentials = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"MYUSER\"},{\"name\":\"password\", \"value\":\"MYPASSWORD\"}]}";                    
                    updateDataSourceRequest.CredentialDetails.EncryptedConnection = "Encrypted";
                    updateDataSourceRequest.CredentialDetails.EncryptionAlgorithm = "None";
                    updateDataSourceRequest.CredentialDetails.PrivacyLevel = "None";


                    var result = await client.Gateways.UpdateDatasourceAsync(datasetSource.Value[0].GatewayId, datasetSource.Value[0].DatasourceId, updateDataSourceRequest);

This atleast responds with a bad request! assuming its the "credentials" string, i have searched and searched but, found nothing on how i create that string....... Anyone? 

Highlighted
dahund Frequent Visitor
Frequent Visitor

Re: Unable to update datasource credentials using ProcessHttpRequestAsync

!!!SOLVED!!!

 

So after almost giving in, I decided to test if I could reset password using Postman and a JSON body (https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource#basic_credentials_examp...

 

Postman replied with a msg telling me that the user was unatherized!!!!

 

Apparently PowerBI will test credentials against source and if not working will relply with "Bad request" ...........  DAMM

 

So tried resetting password on SQL server with new Password, then waited a couple of minutes (PowerBI) apparently need some time to realize its data source is broken. Once PowerBI complained about credential i used following method to update with success :-)

 

public async Task<bool> UpdateSQLDatasource(string gatewayId, string datasourceID, string sqluser, string sqlPassword)
        {
            var getCredentialsResult = await GetTokenCredentials();
            if (!getCredentialsResult)
            {
                // The error message set in GetTokenCredentials
                return false;
            }
            try
            {
                //Create PowerBI Client
                using (var client = new PowerBIClient(new Uri(_ApiUrl), m_tokenCredentials))
                {
                    var updateDataSourceRequest = new UpdateDatasourceRequest
                    {
                        CredentialDetails = new CredentialDetails
                        {
                            CredentialType = "Basic",
                            Credentials = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"" + sqluser + "\"},{\"name\":\"password\", \"value\":\"" +sqlPassword+ "\"}]}",
                            EncryptedConnection = "Encrypted",
                            EncryptionAlgorithm = "None",
                            PrivacyLevel = "None"

                        }
                    };
                    var result = await client.Gateways.UpdateDatasourceAsync(gatewayId, datasourceID, updateDataSourceRequest);

                }

            }
            catch (Exception)
            {
            }
            return true;

        }

So if anyone else has issues, YOU CANNOT USE a dummy account......