Blogs

SSRS – How to handle multi tenants in single instance?

Inkey, June 11, 2019 4739 Views

Problem Statement:

We have different SQL databases for different tenants(or clients), and we want the same SSRS report to fetch data from different databases based on the user who is executing the report.

To achieve this, we can have a data source that is dynamic by using an expression-based connection string. Below is a quick explanation of the approach.

We can have 4 parameters – ServerName, DatabaseName, DatabaseUsername, and DatabasePassword and set the values to these parameters based on the logged in user.

We can then use the following expression in our Data Source to set the Connection String –

="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!DatabaseName.Value & ";User Id=" & Parameters!DatabaseUsername.Value & ";Password=" & Parameters!DatabasePassword.Value

The above expression can be then set in the connection string expression as shown below:

This will work like a charm and will fetch data from different databases according to the tenants.

But the problem arises when we need to refresh the fields for a dataset while designing the report. When we click on “Refresh Fields” in Dataset Properties, it shows the error as in the below image which refers to the dynamic data source that we created using an expression. The problem is that SSRS doesn’t allow to evaluate the expression-based connection string at design time (See Below).

This now becomes a very painful situation for the report designer because whenever there is any change in the query (field selection) which is pulling the data, until we change the connection string of the related data source to a static one, we can not get the new fields. And once we change it, we need to remember to revert it back to the expression before deploying on the report server. As this resulted in report crash every now & then, as the developers tend to miss reverting it every time, we have come up with a solution to this issue.

Hence, the solution we have found to counter this is –
1. Make the connection string static – pointing to the DEV data source. This will relieve the report designers from facing the refresh error.
2. And post-deployment on the PROD environment, run the below code to loop through all the reports & reset the connection string to a dynamic one.

Solution:

This approach, allows us to have a static connection string for the Data Source and can refresh the fields whenever necessary while developing reports.

The plan is to iterate through all the reports on the report server and then change the static connection string to expression-based one using SQL Server Reporting Services (SSRS) Web Service.

The following steps have helped us to achieve our goal.

Step 1: Provide the appropriate connection details to connect the report server as shown below:

#region Setting the credentials for connecting to Report Server
System.Net.CredentialCache CredentialCache = new System.Net.CredentialCache();

CredentialCache.Add(
  new Uri(WebServiceURL),
  "Basic",
  new System.Net.NetworkCredential(
    REPORTSERVER_USERNAME,
    REPORTSERVER_PASSWORD
  )
);
ReportExecutionService.Credentials = CredentialCache;
ReportingService.Credentials = CredentialCache;
#endregion

Step 2: Get the report definition for a specific report. Iterate through the data source we want to change the connection string for and set the definition back to the report server. For this, we use Web service methods called “GetItemDefinition” for getting the report definition and “SetItemDefinition” to apply the changes back to the report server. Please see the below code in which “itemPath” is the path to the specific report.

Please note that the definitions are nothing but just an XML.

try
{
  //Get report definition
  byte[] reportDefinition = ReportingService.GetItemDefinition(itemPath);
  XmlDocument doc = new XmlDocument();
  MemoryStream stream = new MemoryStream(reportDefinition);

  doc.Load(stream);

  //Get the Data sources
  XmlNodeList dataSources = doc.SelectNodes("//*[local-name()='DataSources']/*[local-name()='DataSource']");
  if (dataSources != null)
  {
    //Loop through all the data sources
    foreach (XmlNode dataSource in dataSources)
    {
      //Read the connection details
      XmlNodeList connectionProperties = dataSource.SelectNodes("//*[local-name()='DataSource']/*[local-name()='ConnectionProperties']");
      if (connectionProperties != null
           && connectionProperties.Count == 1)
      {
        //Read the connection string
        XmlNodeList connectionStrings = connectionProperties[0].SelectNodes("//*[local-name()='ConnectionProperties']/*[local-name()='ConnectString']");
        if (connectionStrings != null
             && connectionStrings.Count == 1)
        {
          //Set the connection string
          connectionStrings[0].InnerText = "=\"Data Source = \" & Parameters!serverName.Value & \"; Initial Catalog = \" & Parameters!databaseName.Value & \"; User Id = \" & Parameters!DatabaseUsername.Value & \"; Password = \" & Parameters!DatabasePassword.Value";

          #region Set report definition
          Encoding encoding = Encoding.UTF8;
          byte[] RDLAsBytes = encoding.GetBytes(doc.OuterXml);

          ReportingService.SetItemDefinition(itemPath, RDLAsBytes, null);
          #endregion

          #region Update datasource to refer Expression-Based ConnectionString
          //We need to perform the below operation to persist the change made to the connection string.
          DataSource[] ds = ReportingService.GetItemDataSources(itemPath);
          DataSourceDefinition definition = (DataSourceDefinition)ds[0].Item;

          if (definition == null)
            throw new Exception();

          definition.UseOriginalConnectString = true;
          definition.Enabled = true;
          definition.EnabledSpecified = true;
          definition.Extension = "SQL";
          definition.Prompt = null;
          definition.WindowsCredentials = false;
          definition.UserName = DATABASE_USERNAME;
          definition.Password = DATABASE_PASSWORD;

          ReportingService.SetItemDataSources(itemPath, ds);
          #endregion
        }
      }
    }
  }
}
catch (Exception e)
{
  Console.WrileLine(e.Message.ToString())
}

For more details, please see this link.

Hence, a flawless and free from obstacles solution to this issue is using Report Server Web Service.

If you have got a better solution to this problem or a suggestion to improve this solution, please feel free to leave a comment in the comment section below.