Unable to update dataset credentials after uploading a pbix report

[Anonymous]

So I have been working on being able to import pbix reports through my application and then being able to view them through it.  I have this almost all teh way working right now.  The only issue still outstanding is after I inport a report, the credentials on the dataset seem to get lost.  

I have been trying to use etiher the C# API Wrapper, or the direct Rest API url's to do this, but so far to no avail.  My current code will be below, but right now, everything works, and I do not get any errors.  However, I do not get any success message back either, and after I update the connection string, the report still will not load in my application or in Power BI Itself.  It continues to say it needs credentials.  

If anyone can help me figure out whats wrong with the way I am currently doing this, or give me working code of another way, say using the C# Wrapper of the API, I would apprecaite it very much.

if (importResponse == "Accepted")
                    {
                        ODataResponseListDataset oRld = client.Datasets.GetDatasetsInGroup(GroupId);
                        Dataset dataSet = oRld.Value.Where(x => x.Name == fileNameWithoutExtension).FirstOrDefault();
                        if(dataSet != null)
                        {
                            HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://api.powerbi.com/v1.0/myorg/groups/" + GroupId + "/datasets/" + dataSet.Id + "/Default.SetAllConnections");

                            request.Method = "POST";
                            request.KeepAlive = true;
                            request.ContentType = "application/json";
                            request.Headers.Add("Authorization", String.Format("Bearer {0}", token.ToString()));

                            using (System.IO.Stream s = request.GetRequestStream())
                            {
                                using (System.IO.StreamWriter sw = new System.IO.StreamWriter(s))
                                {
                                    connectionObject conn = new connectionObject
                                    {
                                        connectionString = ConfigurationManager.AppSettings["pbiConnString"].ToString()
                                    }; 
                                    string json = JsonConvert.SerializeObject(conn);
                                    sw.Write(json);
                                }
                                    
                            }

                            using (System.IO.Stream s = request.GetResponse().GetResponseStream())
                            {
                                using (System.IO.StreamReader sr = new System.IO.StreamReader(s))
                                {
                                    var jsonResponse = sr.ReadToEnd();
                                    System.Diagnostics.Debug.WriteLine(String.Format("Response: {0}", jsonResponse));
                                }
                            }

                            repo.Create(new ReportsModel
                            {
                                CompanyId = user.CompanyID,
                                DateUploaded = DateTime.Now,
                                Name = fileNameWithoutExtension,
                                URL = ""
                            });

                        }
                    }

Where the connectionString object looks like....

public struct connectionObject
        {
            public string connectionString { get; set; }
        }

[Anonymous · Accepted solution]

So for anyone that is struggling with this, or anything else that goes along with trying to work with the PowerBI API when trying to use the new PowerBI Premium stuff, I actually found this video, and project that goes along with it, that has EVERYTHING you need.  The video expains it step by step, and should answer any questions you have, like I did, on what things mean, or how to proceed in certian situations, ect...

The one thing his project does not do, that I had to do to make the Patch credentials, and get DataSets work, is to add in the groups/{GroupID}/ to the URL directly after myorg/.  The reason for this is in his project he is just using MyWorkspaces in PowerBI.com.  However, I needed to use a an App workspace that was created for just my project.  If you just use the URL he shows, without adding the groups stuff in, you will only see datasets in MyWorkspace.   You can get the group ID from the URL when you are in PowerBI.com, and click on your app workspace.  Just FYI.

The URL for Patching the credentials in PowerBI ends up looking like...

https://api.powerbi.com/v1.0/myorg/groups/{GroupId}/........

Below here is the link to the video.

http://curvetube.com/Publishing_PBIX_Files_with_the_Power_BI_REST_API/ZSaBFf3ziUk.video

Below this is the link to the project that goes along with the above video...

https://github.com/CriticalPathTraining/PbixInstallerForPowerBI

Hope this all helps you as much as it did me.  Below is the function I used to Patch the dataset also.

Stizz001

/// <summary>
        /// Resets the credentials of a dataset after the PBIX was imported. After import, a direct query dataset loses its credentials
        /// </summary>
        /// <param name="importName">Name of the imported file</param>
        /// <param name="sqlAzureUser">The user name for the datasource</param>
        /// <param name="sqlAzurePassword">the users password for the datasource</param>
        private static void PatchDatasourceCredentials(string importName, string sqlAzureUser, string sqlAzurePassword)
        {

            string restUrlDatasets = PowerBiServiceRootUrl + "/groups/" + GroupId + "/datasets/";
            string jsonDatasets = ExecuteGetRequest(restUrlDatasets);

            DatasetCollection datasets = JsonConvert.DeserializeObject<DatasetCollection>(jsonDatasets);
            foreach (var dataset in datasets.value)
            {
                // find dataset whose name matches import name
                if (importName.Equals(dataset.name))
                {
                    //Console.WriteLine("Updating data source for dataset named " + dataset.name);
                    // determine gateway id and datasoure id
                    string restUrlDatasetToUpdate = restUrlDatasets + dataset.id + "/";
                    string restUrlDatasetDefaultGateway = restUrlDatasetToUpdate + "Default.GetBoundGatewayDataSources";
                    string jsonDefaultGateway = ExecuteGetRequest(restUrlDatasetDefaultGateway);

                    PbixInstallerForPowerBI.Model.Gateway defaultGateway = (JsonConvert.DeserializeObject<GatewayCollection>(jsonDefaultGateway)).value[0];
                   
                    // create URL with pattern myorg/gateways/{gateway_id}/datasources/{datasource_id}
                    string restUrlPatchCredentials =
                      PowerBiServiceRootUrl +
                      "gateways/" + defaultGateway.gatewayId + "/" +
                      "datasources/" + defaultGateway.id + "/";

                    // create C# object with credential data
                    DataSourceCredentials dataSourceCredentials =
                      new DataSourceCredentials
                      {
                          credentialType = "Basic",
                          basicCredentials = new PbixInstallerForPowerBI.Model.BasicCredentials
                          {
                              username = sqlAzureUser,
                              password = sqlAzurePassword
                          }
                      };

                    // serialize C# object into JSON
                    string jsonDelta = JsonConvert.SerializeObject(dataSourceCredentials);

                    // add JSON to HttpContent object and configure content type
                    HttpContent patchRequestBody = new StringContent(jsonDelta);
                    patchRequestBody.Headers.ContentType = new MediaTypeWithQualityHeaderValue("application/json");

                    // prepare PATCH request
                    var method = new HttpMethod("PATCH");
                    var request = new HttpRequestMessage(method, restUrlPatchCredentials);
                    request.Content = patchRequestBody;

                    HttpClient client = new HttpClient();
                    client.DefaultRequestHeaders.Add("Accept", "application/json");
                    client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);

                    client.SendAsync(request);
                }
            }
        }

where

PowerBiServiceRootUrl  = "https://api.powerbi.com/v1.0/myorg";

[Eric_Zhang]

@Anonymous

You can see my demo. I get stuck by using the Power BI Api lib to update the credential so I just call the REST API directly.
By the way,  this demo only works for the datasource in direct connect, in this demo, the datasource is a Azure SQL DB. For the datasources that need gateways and datasources configured, you can see this thread.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//Install-Package Microsoft.PowerBI.Api -Version 2.0.2
using Microsoft.PowerBI.Api.V2;
using Microsoft.PowerBI.Api.V2.Models; 
//Install-Package Newtonsoft.Json 
using Newtonsoft.Json;

using Microsoft.Rest;
using System.Net;
using System.IO;

namespace SetCredential
{
    class Program
    {
        static string accesstoken = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USIsImtpZCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USJ9.eyJhdWQiOiJodHRwczovL2FuYWx5c2lzLndpbmRvd3MubmV0L3Bvd2VyYmkvYXBpIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0LyIsImlhdCI6MTUwMzM5NDAxNiwibmJmIjoxNTAzMzk0MDE2LCJleHAiOjE1MDMzOTc5MTYsImFjciI6IjEiLCJhaW8iOiJBU1FBMi84RUFBQUFwMmZob2UrMzdhTHhhWVZCVXJWc3ppbEdPdDJSSTZsNHJXRmVQcjBLdWY4PSIsImFtciI6WyJwd2QiXSwiYXBwaWQiOiI4NzFjMDEwZi01ZTYxLTRmYjEtODNhYy05ODYxMGE3ZTkxMTAiLCJhcHBpZGFjciI6IjIiLCJlX2V4cCI6MjYyODAwLCJmYW1pbHlfbmFtZSI6IlpoYW5nIiwiZ2l2ZW5fbmFtZSI6IkVyaWMiLCJpcGFkZHIiOiIxNjcuMjIwLjI1NS4xMiIsIm5hbWUiOiJFcmljIFpoYW5nIiwib2lkIjoiNzYzNGI5MzYtNjk1Yy00M2IzLWJkMTYtOWUxNDJkYjZjYjZkIiwib25wcmVtX3NpZCI6IlMtMS01LTIxLTMwMTc0MzkxODUtMzI3NTMyODQxNi0xMTI3NTIxMzg1LTcwMzc0IiwicGxhdGYiOiIzIiwicHVpZCI6IjEwMDM3RkZFOTc5NzczM0MiLCJzY3AiOiJ1c2VyX2ltcGVyc29uYXRpb24iLCJzdWIiOiJpd3NWZE5iLU1YTmlITlB1MTJqWGZ4cnhmaGxSdE4zWUw5N3pRSnR4dGpBIiwidGlkIjoiMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0IiwidW5pcXVlX25hbWUiOiJlcmljemhAd2ljcmVzb2Z0LmNvbSIsInVwbiI6ImVyaWN6aEB3aWNyZXNvZnQuY29tIiwidmVyIjoiMS4wIn0.PPPYobO52n71mQhbdRkuG9WgYymhLrLvadZCGkJLgTVjbJ6kEvZs6yYCJ5E9sd0a1cl6rfJWkz6gCI3Vi2caexCUBj38NGR5BYXPK1kTm-_DOKOpkOtusd-HAJmL4Zqgv6Cooia9f3mX_-92kSQUYlsaiZ7iVYL4pUkNMheZeIvBCAs2xU0LUgTg8B3bCL1Ezgr_iBKF_jbZcz7SNrq93K12YiL2xjmoJbBSi2LYOeCP4bUTo1tb2LRnmyCTR8pK8JvhM19u22VfmFOL0SyAL-11UBjfDp9Uk6pYwRzNtCApMsW73kmuIi52AkADZ4048tZNhqoWZsFz2lqmrg0CYw";

        static void Main(string[] args)
        {


             var tokenCredentials = new TokenCredentials(accesstoken, "Bearer");
            string datasetid = "0894c31c-1425-4a3c-a1fc-57f8fbef0e88";


            try
            {
                using (var client = new PowerBIClient(new Uri("https://api.powerbi.com/"), tokenCredentials))
                {



                    ConnectionDetails connDetail = new ConnectionDetails();
                    connDetail.ConnectionString = "data source = linl3.database.windows.net; initial catalog = AdventureWorksLT; persist security info = True; encrypt = True; trustservercertificate = False";

                    client.Datasets.SetAllDatasetConnections(datasetid, connDetail);



                    var dataSources = client.Datasets.GetGatewayDatasources(datasetid);




                    /**

                    CredentialDetails credential = new CredentialDetails();

                    credential.CredentialType = "Basic";

                    //I WAS STUCK ON what is the credentials string like
                    credential.Credentials = "{\"credentialData\":[{\"name\":\"username\",\"value\":\"LinLeng\"},{\"name\":\"password\",\"value\":\"Password01!\"}]}";

                    credential.Credentials= "{\"basicCredentials\": {    \"username\": \"LinLeng\", \"password\": \"Password01!\"  }}";

                    UpdateDatasourceRequest udr = new UpdateDatasourceRequest(credential);

                    var a = client.Gateways.UpdateDatasource(dataSources.Value[0].GatewayId, dataSources.Value[0].Id, udr);

                    **/


                    string credential = " {\"credentialType\": \"Basic\", \"basicCredentials\": { \"username\": \"LinLeng\", \"password\": \"Password01!\"  } }";


                    updateCredential(credential, dataSources.Value[0].GatewayId, dataSources.Value[0].Id);





                }
            }
            catch (HttpOperationException ex) {

                Console.WriteLine(ex.Response.Content);

            }
             
            Console.ReadKey(); 
        }


        public static void updateCredential(string credential, string gatewayid, string datasourceid)
        {
            string responseStatusCode = string.Empty;

            string url = "https://api.powerbi.com/v1.0/myorg/gateways/"+gatewayid+"/datasources/"+datasourceid;
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            request.ContentType = "application/json";
            request.Method = "PATCH";
            request.KeepAlive = true;
            request.Headers.Add("Authorization", String.Format("Bearer {0}", accesstoken));


            //POST web request
            byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(credential);
            request.ContentLength = byteArray.Length;

            //Write JSON byte[] into a Stream 

            try
            {
                using (Stream writer = request.GetRequestStream())
                {
                    writer.Write(byteArray, 0, byteArray.Length);

                    var response = (HttpWebResponse)request.GetResponse();

                    Console.WriteLine(string.Format("credential is updated {0}", response.StatusCode.ToString()));

                }
            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }
             
        }


    }
}

[Anonymous]

@Eric_Zhang you have been absolutly great in answering all my questions, and I sincerly appreacite all your help.  

When looking at your repsonse, what I found funny is you seem to have gotten confused in the exact same place I did, when trying to figure out what to setthe Credentials property of the CredentialDetails object. I didnt post that in my original question, but struggled with the exact same thing, and tried the same things you roughly did.  Glad to see I am not the only one confused about that.

As far as your response, this time I am actually not going to be trying your code to see if it works because as you may see in my original post, I have responded with a solution to my problem. I found a video online, that had a project that went along with it, that step by step explains everthing from registering your app, to importing and patching credentials, and the only modification I had to make was to change his URL's to use the App Workspace I had created for this project instead of just using MyWorkspace by adding /groups/{GroupID}/.... to the base urls and everything worked great for me.  

So since I will not be accepting your answer as the solution, because I have not tried it, I do want to say thank you to you for answering all of my questions, normally within 24 hours and just being so helpful.  You have been a huge to help in me being able to get this work done by my deadline. So thanks again.

Stizz001

[ibuchan]

@Eric_Zhang Your link to the thread for setting up authentication with a gateway appears to be returning a 403 for me.  Is there any way this information can be made publicly available?

Attempting to access the following link provided in your previous posting:
https://community.powerbi.com/t5/Community-Knowledge-Base/Power-BI-REST-API-demos-in-C/ta-p/211155