SSRS Shared Data Soure

A shared data source in SSRS defines a connection to an external data source. With a shared data source, you can create and maintain the settings for the data source connection separately from the reports, models, and data-driven subscriptions that use the data source. So it can be used by any report on the report manager for rendering data and subscriptions.

In BIDS, under the project we can define the Shared Data source to use it in multiple reports as:

Navigate down the project folder in the solution explorer, we have “Shared Data Sources” folder, do right click to add new data source and on the editor, provide details as name and connection string as:

Open Report Manager, and navigate to the folder in which you want to create a data source. In the toolbar, Click on “New Data Source” option and you must have content manager permissions to create a shared Data source on report manager and on data source editor page, we have to provide Name, description, Connection string (Syntax of the connection string is specific to data provider or we can say a connection string for a Database Server) and at the last you need to provide authentication ways to access database server which you have mentioned in the connection string as:

 

For Data Source Types, which are supported by the Report manager, different types of data source are available, you can choose from the given list as: if our data source is SQL Server then select Microsoft SQL Server or OLE DB. If our data source is SQL Server Analysis Service then select Microsoft SQL Server Analysis Services.

For Connection Properties option, we have to specify the Data Source (Provider), Server Name (or Instance Name), Login credentials and Database Name.

For Connect Using option: we have to mention the Credentials which are required for connecting the Reports with mentioned database server in Connection string
·         Credentials supplied by the user running the report: When the report is connecting to data source, it will prompt the given text (Type or enter a user name and password to access the data source). If you check mark, Use as Windows credentials when connecting to the data source option then reporting server will pass the current user windows credentials to external server instead of prompting otherwise, it will prompt for credentials.
·         Credentials stored securely in the report server: If you select this option, Please specify the user name and password to log into data source and these credentials will be stored inside the report. This option is very important for Report Subscriptions.
·         Windows Integrated Security: It will pass the current user windows credentials to external server for connect to the data source. For now, We are using this option
·         Credentials are not required: Report will not use any credentials to connect with data source. For instance, If we are accessing file in local file system then we can select this option. However, try to avoid this option.

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!