the nullfactory - Obfuscating a SharePoint-Integrated SSRS DataSource's Connection String

Obfuscating a SharePoint-Integrated SSRS DataSource's Connection String

16 Feb 2015 | SharePoint | SQL Server | SQL Server Reporting Services | Code Access Security

When SQL Server Reporting Services (SSRS) is deployed as an SharePoint integrated solution, it enables much of its functionality to be managed right from within SharePoint. Starting from the 2013 version, the integration between SharePoint and SQL Server Reporting Services 2012 is more tightly coupled than previous iterations.

One feature in integrated mode is the ability to have the data sources (.rsds) and report files (.rdl) within a document library itself. This means that reports can reference a DataSource within any document library in the SharePoint site.

In order for the report to work the user should have read permission on both the data source as well as the report file. The problem with this is that the same user can now potentially view the settings within the data source file, including the connection string.

The Solution

In order to protect the connection string, I came up with a solution to obscure it through encryption. The solution can be broken down to two major steps:

Force the reports to get the connection string by evaluating an expression embedded within itself.
Within this expression, call some custom code which manages the retrieval and decryption of the connection string.

One of the limitations with this method is that you can no longer use a shared data source and each report has to have its credentials embedded.

In my example below, I will be retrieving the configuration string from a configuration list stored in the same SharePoint server.

Create the report extensions assembly

Here's a summary of steps used to create report extension. You can find a link to the full source at the bottom of the post.

Create new a class library to host your custom code.
Next, sign the assembly as we would be deploying it into the Global Assembly Cache.
In order for the report server to call the custom code, the strong-named assembly must be marked with the [assembly: AllowPartiallyTrustedCallers] attribute. Let's do this now.
Create a new public class and method that would retrieve the connection string. This method would take the report server url as its only parameter. This parameter would be parsed and later used to generate the REST call to SharePoint. Next, add a [SecuritySafeCritical] attribute to the method, we need to this to perform operations that require access outside of the sandbox.
Now, create a helper method that retrieves the default credentials. This too would be decorated with the [SecuritySafeCritical] attribute. In the body of the method, I explicitly assert the EnvironmentPermission(EnvironmentPermissionAccess.Read, "USERNAME") permissions before using the CredentialStore. We would run into a security exception if the explicit assertion is not done.
Finally, create the method that makes the REST call to retrieve the obfuscated connection string from a SharePoint configuration list.
The EnvironmentPermission assertion operation has to be in its own method with its own [SecuritySafeCritical] attribute for it to work together with the WebPermission assertion. Otherwise the following exception would be thrown:
```
Exception: System.Security.SecurityException Exception Message: Stack walk modifier must be reverted before another modification of the same type can be performed. Stacktrace:    at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
   at System.Security.CodeAccessPermission.Assert()
```
After numerous failed attempts at accessing the SPList via the SharePoint object model (I kept running into exceptions stating that I required full trust), I find that partial trust mode is no longer supported and has been deprecated. As I was unable to find anyone online who had successfully got it to work, I opted to access the list via the REST service for its lesser dependencies and security permission requirements.
Compile and deploy the assembly into the Global Assembly Cache.

Setting up the Report

Open up a copy of your report using the latest version of Report Builder.
Right click on the work space area outside to bring up the context menu. Select the Report Properties... menu item which opens up the Report Properties dialog.
Navigate to the References tab and add an entry for our custom assembly.
Next, create a new entry for the class containing our custom logic. Ensure that the class name is referred to using it full namespace and provide an instance name that would be used to call our code from within the report.
Now that our declarations have been done, edit the report data source for the report.
Click on the fx button to open up the expressions dialog.
Edit the expression to use the new custom method created previously. Pass in Globals!ReportServerUrl as the method parameter.
Now, upload the updated report back into the SharePoint list hosting our report files. If you already have a report with the same name and with a shared data source linked to it, our connection details would be overwritten. In order to avoid this, delete the report in the list and re-upload our new one.
Next, edit the Report Data Source by selecting the Manage Data Sources menu item in the context menu. Ensure that connection type is set to Custom data source and connection string to Use connection string expression defined in the report.
For the credentials, I will be using stored credentials. Note that the Test Connection button does not work if the connection string needs to be evaluated at runtime.

Final Thoughts

You can find the source here. It is meant only to be a template and should be extended to work with your own requirements.
Since the custom assembly needs to be deployed into the Global Assembly Cache, I suggest including it as part of the SharePoint Solution Package when automating your deployment process.
Although this implementation was tested with a Microsoft SQL Server data source type, I suspect it can be altered to work with different connection types.

I would finally like to highlight the following articles as they gave me a lot of insight into Code Access Security (CAS) and better understanding of the security requirements I needed to address:

Obfuscating a SharePoint-Integrated SSRS DataSource's Connection String

The Solution

Create the report extensions assembly

Setting up the Report

Final Thoughts

References