Creating A Shared Data Source In SQL Server Reporting Services

There are two basic types of data sources when dealing with SQL Server Reporting Services (SSRS).  The first, an embedded data source, is a data source that is configured directly into the actual report itself.  This type of data source is not visible to any other reports. These can be useful in certain circumstances such as report development or testing but it’s generally a bad idea to use these on published reports.

The other type is a shared data source.  A shared data source is configured on the SSRS front-end and can be assigned to any reports that are published.  This is the ideal method for assigning a data source to a report as it allows you to streamline your data sources as well as provides some useful tools for managing security and tracking reports that are assigned to the shared data source.

Adding and configuring a new shared data source is quite simple.  I have outlined the steps below.

Once you have logged into your Reporting Services front-end/web portal locate the button on the menu labeled New Data Source.

ssrs_web_menu

The New Data Source page will be displayed.  Here you will configure the connection string and credentials that will be used to connect to the database.  You should also provide a name and brief description of the data source.  Depending on your environment you may have numerous data sources for each SQL Server.

In the example below I have configured a shared data source named Development Lab which connects to my lab-sql-2012 database server.  The individual database is specified as the initial catalog.  In this case the initial catalog is set to DevLab.

By default the Connect using option is set to connect using Credentials supplied by the user running the report.  However, best practice is to have a dedicated report account created for your shared data sources to use.  In this case I have a service account named svc_reportcred which I use for all shared data sources.  This can be configured by selecting the option to use Credentials stored securely in the report server and then inputting the credentials.

ssrs_new_datasource_completed

Once the data source has been configured click the Test Connection button confirm that everything was entered correctly.  If successful, click OK.

You should now see the newly created data source within the Details View of your SQL Server Reporting Services front-end.  

Once the shared data source has been created you can view the properties of the data source, which give you a few additional configuration options.  To do this, locate the data source in the Details View and click it.

On the left hand side of the screen you will see the menu which will include Properties, Subscriptions, Dependent Items and Security.

ssrs_datasource_properites_options

The most useful of these are the Dependent Items and Security tabs.  On the Dependent Items tab, any reports that are published and are using the shared data source will be listed here. This is an easy way to determine if any active reports are currently using a certain data source as deleting a data source that is being used will inevitably break the report.

The Security tab allows you to configure user and domain level access to any shared data source.  Though this can be configured on individual reports, in some cases it might be easier to restrict based on the data source.