Unable to update datasource credentials using ProcessHttpRequestAsync

[dahund]

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;

        }

[dahund]

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? 

[dahund · Accepted solution]

!!!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......