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!)
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).
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: