Logo
banner

Blogs

SSRS – How to handle multi tenants in single instance?

, June 11, 2019 4153 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.

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

4 responses to “SSRS – How to handle multi tenants in single instance?”

  1. Matías Bello says:

    What about Shared DataSets ? This works well with embedded DataSets that use *that* connection, but Shared DataSets use their own connection, which – as far I understand – is not editable on runtime.

    • Admin says:

      Hello,

      You are right. The suggested approach will not work for a shared Dataset.

      However, what I understand is, it does not need to work. As the whole purpose of shared datasets is to use common/same connection details for all the reports referring to it. And if we want them different, we have got the embedded datasets.

      I hope this answers your query. If not, please feel free to write back & I shall re-address it.

      Thanks!

  2. Dhaval Charadva says:

    Hi

    Thanks for this article.

    But I am unable to run the report even with the first step where I set parameter for dynamic database name.

    What this I am missing?

    Any help would be appreciated.

    • Admin says:

      Hello Dhaval,

      Could you please share the exact steps that you performed to implement this (screenshots would be very helpful) and the description of the issue/error you’re facing?

Leave a Reply

Your email address will not be published. Required fields are marked *

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?