SQL Server Reporting Services (SSRS) is a Microsoft server-based reporting platform that enables organizations to create, manage, and deliver a wide range of interactive and paginated reports. When SSRS reports are deployed and configured, they can be accessed through a web browser.
This platform gives you the opportunity to connect to our ODBC Drivers. If you attempt to establish an ODBC connection by utilizing a DSN specified in the "Connection string" field within the data source properties on the SSRS web portal, you may encounter the following error message, despite having successfully established the connection in Visual Studio: “Couldn’t connect”
The issue is related to the default security policy in SSRS disallowing certain assemblies to be loaded. We can however modify the policy to allow the ODBC driver to be loaded.
To do this, you will need to edit the rssrvpolicy.config file on the SSRS server, which for me, is at C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer. In this file, you will need to add a new CodeGroup to the existing list in the following format (CData ODBC Driver for Google BigQuery is used as an example):
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="CData.ODBC.GoogleBigQuery" Description="This code group grants CData.ODBC.GoogleBigQuery full trust.">
<IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\CData\CData ODBC Driver for Google BigQuery\lib64\*" />
</CodeGroup>
Note: this assumes the default installation path of the driver.
This grants the necessary permissions to load our ODBC driver's dll at runtime. After changing the file, you will have to restart the SSRS service before the change takes effect. For clarity, here's what my file looks like after the change:
Now you can go ahead and Test Connection in SSRS and you should be able to connect to our ODBC Driver for Google BigQuery.
The process is the same for every ODBC Driver you want to connect to, for each driver you have to add a CodeGroup block like we did in this article, and obviously modify the Name and Url values according to the data source.
Please reach out to support@cdata.com if that does not resolve the issue for you.
