Tag Archives: cannot connect to DPM database

DPM 2016 - Reporting Services Server cannot connect to DPM Database

After installing System Center Data Protection Manager from scratch or after performing an upgrade from DPM 2012 R2, when you attempt to schedule a report to be mailed, you receive the following popup error or notice that when you generate a report you just receive a white page in your browser that continues to load indefinitely:

Reporting Services Server cannot connect to the DPM database.
To repair the configuration, follow steps for repairing DPM from DPM Setup Help.
ID: 3001
More Information
Just an endless loading page presented in your browser.

Unfortunately, following the repair steps suggested in the More Information link does not resolve the problem.

Resolution for SQL Server 2016 and later

  1. On the DPM server, open Computer Management, expand Local Users and Groups, click Groups, and create a new local group with the following information (replace items in red with the actual server name/hostname of your machine):
    1. Group name: DPMDBReaders$DPMSERVERNAME
    2. Description: This group is internally used by Microsoft System Center 2016 Data Protection Manager.
  2. On the DPM server, open Computer Management, expand Local Users and Groups, click Users, and create a new local user with the following information (replace items in red with the actual server name/hostname of your machine):
    1. User name: DPMR$DPMSERVERNAME
    2. Full name: DPMR$DPMSERVERNAME
    3. Description: This account is used for SQL reporting to generate reports for DPM 2016.
    4. Enter a strong password
    5. Check Password never expires
  3. Select your recently created DPMR$DPMSERVERNAME account and click on the Membership of tab
    1. Add the DPMDBReaders$DPMSERVERNAME group we created in step 1
    2. Click OK
  4. Start Microsoft SQL Server Management Studio and connect to the SQL instance used by DPM.
    1. Expand Security, right-click on the Logins, select New login
      1. Click on the Search... button and add the local group DPMDBReaders$DPMSERVERNAME
      2. Set the Default database to YourDPMDatabase

      3. Click on the User Mapping section, check the checkbox for YourDPMDatabase, and check the checkbox for the db_datareader role.
      4. Click OK
  5. In Microsoft SQL Server Management Studio, expand Databases, expand YourDPMDatabase, expand Security, expand Users, and right click Properties on the DPMDBReaders$DPMSERVERNAME group you granted access to in step 4.
    1. Click on Securables
    2. Click the Search... button
    3. Select Specific objects... and click OK
    4. Click the Object Types... button
    5. Check Stored precedures and click OK
    6. Click the Browse... button
    7. Check [dbo].[prc_MOM_Heartbeat_Get] and [dbo].[prc_MOM_ProductionServerGet] and click OK
    8. Click OK on the Select objects dialog box
    9. Place a checkbox in the Grant column for the Execute row.
      1. Make sure you do this step for both [dbo].[prc_MOM_Heartbeat_Get] and [dbo].[prc_MOM_ProductionServerGet], checking the box once will only update one of the storage procedures.
    10. Click OK
  6. Exit Microsoft SQL Server Management Studio.
  7. Open Reporting Services Configuration Manager and connect to the SqlServer and Instance hosting the DPM reports (as you go through this, replace the items in Red with your applicable values).
    1. Click on the Web Portal URL menu item and click on the listed URL for DPM.
    2. Click on the DPMReports_GUID folder to open the DPM reports page.
    3. Click on the DPMReporterDataSource Data Source to open its properties.
    4. Under Credentials, use the following configuration:
      1. Select the Using the following credentials radio button
      2. Type of credentials: Windows user name and password
      3. User name: DPMR$DPMSERVERNAME
      4. Password: EnterYourPasswordToTheAccount
      5. Ensure Log in using these credentials, but then try to impersonate the user viewing the report is unchecked
      6. Click the Test connection button
        1. Ensure it says Connected successfully

      7. Click Apply
    5. Close out of your web browser
  8. Back in the Reporting Services Configuration Manager window, complete the following steps
    1. Select the Service Account menu item
    2. Ensure Use built-in account is set to Network Service
      1. Check the Use built-in account radio button
      2. Set the account to Network Service
      3. Click Apply
      4. You will be prompted to save an encryption key. Save the key to location of your choosing, and type a password to be used to encrypt the file. Click OK
      5. You will then be prompted to specify an account with administrator privileges. Click OK to use your current account.
  9. Reboot the DPM Server

At this point, you should now be able to schedule e-mail reports without experiencing the original error and your reports should load properly!