Create a new Data Source in SQL Reporting Services

Purpose: Create a Central SQL Report Data Source (else you need one in every custom report)

Once you take upon the quest of creating reports using SQL Reporting Services, you will soon notice that you are doing the same task in every report that you create. Therefore you should create a centralized data source that your reports will be using. This will save you time and nerves (so you can create even more reports, yey!)

Design the Folder Structure

First, think of a great location to store the Data Source. My tip would be to store it before the actual reports, else you will have an empty folder shown in the SCCM 2012 Admin Console monitoring section. Create the folder “Data Sources” and select manage from the drop down menu. Hide the folder from the tile view (to secure it from those who dont know what to do with it).

Create a Data Source

Second, navigate inside your new folder and create the the new centralized Data Source.

Finally, insert the following information into your centralized Data Source:

Name Name the Data Source, ie. ConfigMgr Data Source
X Enable this Data Source
Data Source Type Microsoft SQL Server
Connection String Persist Security Info=False;Initial Catalog=yourConfigMgrDBname;Data Source=yourConfigMgrFQDN;Encrypt=true;TrustServerCertificate=false
Credentials stored Insert an AD Service Account User name and Password
X User as Windows credentials when connection to the Data Source

Test your connection, if shown as “Connection created successfully” you are good to go!
Example shown below:

Leave a Reply

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

two − = one

Post Navigation