Tuesday, September 15, 2015

Attempts to schedule a mailed report in DPM 2012 R2 fail with “Reporting Services Server cannot connect to the DPM database”

Figure 1 below shows this error.
clip_image002
Figure 1.
Following the repair steps suggested in the More Information link does not resolve the problem.
To resolve this error, please use the steps below to make proper configuration changes which will then allow DPM reports to be e-mailed.
NOTE: Some steps may not be necessary if you encounter the problem after an upgrade from DPM 2012 SP1. Review and perform the steps as applicable.
1. On the DPM 2012 R2 server, create a new local group called "DPMDBReaders$<DPMServerName>" In my example the DPMServername is WINB-DPM.
clip_image004
Figure 2.
2. Create a new local user called "DPMR$<DPMServerName> and provide a strong password that never expires. See figure 3 below.
clip_image006
Figure 3.
3. Add the newly created user to the DPMDBReaders$DPMservername group that was created in step 1 above. See figure 4.
clip_image008
Figure 4.
4. Start SQL Management Studio using administrative privileges and connect to the SQL instance used by DPM. UnderSecurity, right-click on the Logins and select New login… then browse to add the local group DPMDBReaders$<DPMServerName >. Once the group is added it will be listed as seen in figure 5.
clip_image010
Figure 5.
5. Right-click the group and select Properties. Under the General section, change the default database to the DPMDB name. See figure 6.
clip_image012
Figure 6.
6. Under the User Mapping section, select the checkbox for the DPMDB name and the checkbox for the db_datareader role. See figure 7.
clip_image014
Figure 7.
7. In SQL Management Studio, navigate to the Databases > DPMDB > Security > Users > and select the DPMDBReaders$<DPMServerName>" group. See figure 8.
clip_image016
Figure 8.
8. Right-click the DPMDBReaders$<DPMServerName>" group and select Properties. Under the Securables section, click onSearch. On the add objects dialog, click OK. On the Select Objects dialog, click the Objects type, then check the stored procedure checkbox. Browse and add the following two stored procedures:
Prc_MOM_Heartbeat_Get
prc_MOM_ProductionServer_Get
Grant the Execute permission for both of the stored procedures added. See figure 9.
clip_image018
Figure 9.
9. Exit SQL Management Studio.
10. Start Reporting Services Configuration Manager and click on the Report Manager URL. See figure 10.  
clip_image020
Figure 10.
11. Click on the URLs: link. This will open the http://localhost/Reports/Pages/Folder.aspx) portal as seen in Figure 11.
clip_image022
Figure 11.
12. Click on the DPMReports_GUID link to open the DPM reports page as seen in Figure 12.
clip_image024
Figure 12.
13. Click on the DPMReporterDataSource to open its properties as seen in figure 13.
clip_image026
Figure 13.
14. On the DPMReporterDataSource configuration page, perform the following steps:
i. Enable the option: "Credentials stored securely in the report server"
ii. Add the “DPMR$<DPMServerName>” user Account and Password created in step 2.
iii. Check the checkbox to "Use as Windows credentials when connecting to the data source."
iv. Click ‘Test connection’ on to verify whether it can connect successfully.
v. Click the Apply.
15. Close the DPMReporterDataSource configuration page to return to the Reporting Services Configuration Manager screen. Click on the Service Account. On this page change Report Server Service Account service to use Network Service. If you are prompted for the Backup Encryption Key, fill on the parameters and continue. See Figure 14 below.
clip_image028
Figure 14.
16. Reboot the DPM Server to ensure all configuration changes take effect.
17. You should now be able to schedule e-mail reports without experiencing the original error.
clip_image030

No comments:

Post a Comment