We have a System Center Configuration Manager (SCCM) 2012 server which has the Reporting Services role added to it. The same server hosts a SQL server which has Reporting Services enabled.
When we go to the reporting services web page (myserver.mydomain.com/Reports) and click the configuration manager folder, then the data source, then go to properties we are prompted with a page asking us to configure the data source.
We have set it to a Microsoft SQL Server type, and have supplied it with administrator credentials which have the sysadmin role enabled for the database as well has db_owner role added to the database 'CM_PEI'.
From there we click the 'Test Connection' button and the web page prompts us with a message stating:
Cannot open database "CM_PEI" requested by the login. The login failed. Login failed for user 'MYDOMAIN\User
When we look at the srsrp.log file we are seeing a similar error while it tries to perform a health monitor against the reporting service:
(!) Error retrieving folders - [Cannot open database "CM_PEI" requested by the login. The login failed.~~Login failed for user 'NT AUTHORITY\SYSTEM'.].
We are absolutely stumped as to why this error could be occurring. Does anyone have any thoughts?