Now, a bit of background here; the OpsMgr environment that I am working in has RMS, DB, DW + Reporting, ACS Collector & ACS DB all deployed into 5 different servers. Now with best practice in mind you would normally have a Data Reader/Data Write account created for your Data Warehouse action accounts. However the same was not stressed with the ACS Database. So what entails then?
So now you have successfully uploaded the ACS reporting components to the Reporting Server you would thought that you are ready to generate the ACS reports? Well, not exactly because you will most likely hit with your report giving you the following error.
From the looks of things, it seems that your report has problems creating a connection to the data source. Now what could be the problem?
Because you have a specific Data Reader account that accesses your Data Warehouse tables/views but this does not apply to ACS Database and because your Data Reader does not have access rights to the ACS Database, your connection will fail.
What would be the workaround for this?
The workaround for this is relatively simple, you would just need to access the SQL Server Reporting Services for the Reporting Server via your internet browser.
For example: http://reportingservername/reports
- From the SQL Server Reporting Services Home page, click on Audit Reports
- Inside Audit Reports, click on Show Details. You shall see DB Audit appears on the screen
- Click on DB Audit to launch the setting properties
- Scroll down to Connect Using, and select Credentials stored securely in the report server
- Enter the User credentials that has access to the ACS Database.
- Click Apply to store the settings.
