Change report connectionstring and credentials

[pzeller]

We have  multitenant system and we are trying to use the same report for each, but with updating the connectionstring/credentials. We have a default report that was built in Power BI Desktop based off of a azure database (that is the same per tenant) that we upload, change the connectionstring and then update the credentials. We aren't receiving any errors with the API calls, but we are running into an issue when trying to then view that report.

Here is our upload/change code:

 public static IPowerBIClient CreateClient()
 {
         var credentials = new TokenCredentials(AppSettings.AccessKey, "AppKey");
         return new PowerBIClient(credentials) { BaseUri = new Uri(AppSettings.ApiUrl) };
 } 

public static string UploadReport(string workspaceId, string reportName, Stream fileStream, string connectionString)
        {
            SqlConnectionStringBuilder lConnectonString = new SqlConnectionStringBuilder(connectionString);

            string lUserID = lConnectonString.UserID;
            string lPassword = lConnectonString.Password;

            using (var lClient = CreateClient())
            {
                Import lImport = lClient.Imports.PostImportWithFile(AppSettings.WorkspaceCollection, workspaceId, fileStream, reportName);

                //Poll for import to complete
                int lCount = 0;
                while (lCount++ < 20 && lImport.ImportState != "Succeeded" && lImport.ImportState != "Failed")
                {
                    System.Threading.Thread.Sleep(500);
                    lImport = lClient.Imports.GetImportById(AppSettings.WorkspaceCollection, workspaceId, lImport.Id);
                }

                if (lImport.Reports.Count != 1)
                    throw new ArgumentOutOfRangeException("Invalid report ID");

                string lReportID = lImport.Reports[0].Id;

                Report lReport = lClient.Reports.GetReports(AppSettings.WorkspaceCollection, workspaceId).Value.First(x => x.Id.Equals(lReportID));

                Dataset lDataset = lClient.Datasets.GetDatasets(AppSettings.WorkspaceCollection, workspaceId).Value.First(x => x.Name.Equals(lReport.Name));

                SqlConnectionStringBuilder lNewConnectionString = new SqlConnectionStringBuilder()
                {
                    DataSource = lConnectonString.DataSource.Replace(",1433", "").Replace("tcp:", ""),
                    InitialCatalog = lConnectonString.InitialCatalog,
                    UserID = lUserID,
                    Password = lPassword,
                    PersistSecurityInfo = false
                };

                var connectionParameters = new Dictionary<string, object>
                    {
                        { "connectionString", lNewConnectionString.ConnectionString }
                    };

                lClient.Datasets.SetAllConnections(AppSettings.WorkspaceCollection, workspaceId, lDataset.Id, connectionParameters);

                // Get the datasources from the dataset
                var lDataSources = lClient.Datasets.GetGatewayDatasources(AppSettings.WorkspaceCollection, workspaceId, lDataset.Id);
                GatewayDatasource lDataSource = lDataSources.Value[0];

                // Reset your connection credentials
                GatewayDatasource lGatewayDatasource = new GatewayDatasource
                {
                    CredentialType = "Basic",
                    BasicCredentials = new BasicCredentials
                    {
                        Username = lUserID,
                        Password = lPassword
                    }
                };

                // Update the datasource with the specified credentials
                lClient.Gateways.PatchDatasource(AppSettings.WorkspaceCollection, workspaceId, lDataSource.GatewayId, lDataSource.Id, lGatewayDatasource);

                return lReportID;
            }
        }

This is the error we are receiving when trying to then view the report:

[Eric_Zhang]

@pzeller

That code works based on my test, I can update connection string and set credential with your code.

Before everything, ensure that you can connect to the target Azure SQL DB in SQL Server Management Studio.

Then debug what you've set the correct connection string. normally a connection string is like 

{"connectionString":"data source=MyAzureDB.database.windows.net;initial catalog=Sample2;persist security info=True;encrypt=True;trustservercertificate=False"} 

By the way, is the Power BI workspace collection and the Azure SQL database in the sample subscription? If not and you can confirm that the connection string is correct, try to set the Azure SQL database firewall as below for testing purpose.

[pzeller · Accepted solution]

We figured out the issue we were facing. @Eric_Zhang, you are correct that all the code works and we knew that, but the part that was giving us trouble was then viewing the report itself.

SOLUTION:

Our problem was due to having a multitenant system, our data structures weren't always the same, meaning in one DB we could have 10 columns and in the other we could have 12 columns. This causes the report to fail and unfortuntely only gives a very generic error message. There does not appear to be a work around for this, but for our situation we were simply able to make a view with a standard set of columns that will be used across all tenants. This solved our problem.

SUGGESTION:

These error messages we are receiving are very minimal and cause us to run around searching every possible issue. If the messages were a little more detailed solving the issue would be a much easier task. Please update your error messaging to be more detailed and/or applicable to the issue at hand.

[pzeller]

We have  multitenant system and we are trying to use the same report for each, but with updating the connectionstring/credentials. We have a default report that was built in Power BI Desktop based off of a azure database (that is the same per tenant) that we upload, change the connectionstring and then update the credentials. We aren't receiving any errors with the API calls, but we are running into an issue when trying to then view that report.

Here is our upload/change code:

 public static IPowerBIClient CreateClient()
 {
         var credentials = new TokenCredentials(AppSettings.AccessKey, "AppKey");
         return new PowerBIClient(credentials) { BaseUri = new Uri(AppSettings.ApiUrl) };
 } 

public static string UploadReport(string workspaceId, string reportName, Stream fileStream, string connectionString)
        {
            SqlConnectionStringBuilder lConnectonString = new SqlConnectionStringBuilder(connectionString);

            string lUserID = lConnectonString.UserID;
            string lPassword = lConnectonString.Password;

            using (var lClient = CreateClient())
            {
                Import lImport = lClient.Imports.PostImportWithFile(AppSettings.WorkspaceCollection, workspaceId, fileStream, reportName);

                //Poll for import to complete
                int lCount = 0;
                while (lCount++ < 20 && lImport.ImportState != "Succeeded" && lImport.ImportState != "Failed")
                {
                    System.Threading.Thread.Sleep(500);
                    lImport = lClient.Imports.GetImportById(AppSettings.WorkspaceCollection, workspaceId, lImport.Id);
                }

                if (lImport.Reports.Count != 1)
                    throw new ArgumentOutOfRangeException("Invalid report ID");

                string lReportID = lImport.Reports[0].Id;

                Report lReport = lClient.Reports.GetReports(AppSettings.WorkspaceCollection, workspaceId).Value.First(x => x.Id.Equals(lReportID));

                Dataset lDataset = lClient.Datasets.GetDatasets(AppSettings.WorkspaceCollection, workspaceId).Value.First(x => x.Name.Equals(lReport.Name));

                SqlConnectionStringBuilder lNewConnectionString = new SqlConnectionStringBuilder()
                {
                    DataSource = lConnectonString.DataSource.Replace(",1433", "").Replace("tcp:", ""),
                    InitialCatalog = lConnectonString.InitialCatalog,
                    UserID = lUserID,
                    Password = lPassword,
                    PersistSecurityInfo = false
                };

                var connectionParameters = new Dictionary<string, object>
                    {
                        { "connectionString", lNewConnectionString.ConnectionString }
                    };

                lClient.Datasets.SetAllConnections(AppSettings.WorkspaceCollection, workspaceId, lDataset.Id, connectionParameters);

                // Get the datasources from the dataset
                var lDataSources = lClient.Datasets.GetGatewayDatasources(AppSettings.WorkspaceCollection, workspaceId, lDataset.Id);
                GatewayDatasource lDataSource = lDataSources.Value[0];

                // Reset your connection credentials
                GatewayDatasource lGatewayDatasource = new GatewayDatasource
                {
                    CredentialType = "Basic",
                    BasicCredentials = new BasicCredentials
                    {
                        Username = lUserID,
                        Password = lPassword
                    }
                };

                // Update the datasource with the specified credentials
                lClient.Gateways.PatchDatasource(AppSettings.WorkspaceCollection, workspaceId, lDataSource.GatewayId, lDataSource.Id, lGatewayDatasource);

                return lReportID;
            }
        }

This is the error we are receiving when trying to then view the report: