Forum Discussion

dahund's avatar
dahund
Regular Visitor
7 years ago
Solved

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 upda...
Need Help
  • dahund's avatar
    dahund
    7 years ago

    !!!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_example) 

     

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

dahund's avatar
dahund
Regular Visitor
7 years ago

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's avatar
    dahund
    Regular Visitor
    7 years ago

    !!!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_example) 

     

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