Skip to main content

How to migrate SSRPM's SQL database - Knowledgebase / SSRPM - Tools4ever NW Support

How to migrate SSRPM's SQL database

Authors list

At some point, it may become necessary to migrate your SSRPM's database from one server to another. Perhaps your SQL server is getting a bit long in the tooth, or perhaps you're experiencing hardware issues. No matter the cause, the steps to migrate the SQL database from one server to another are relatively straight forward.

Step One: Identify what database SSRPM is using

SSRPM is capable of storing its data in either a fully-fledged Microsoft SQL database, or in a Microsoft Jet database file. Your first step in migration is to determine which of these databases your instance of SSRPM is using.

  1. Open the SSRPM Admin Console
  2. Select Service Management > Configure... in the menu bar
  3. Click on the Database tab
  4. Take note of which radio button is selected under the Database header.

If your instance has Default (Microsoft Jet) selected, then the migration of your database is outside the scope of this article. However, if Microsoft SQL is selected, then you may continue on.

Step Two: Create a backup of the database object

Before you can migrate the SSRPM database object to another server, you must first create a backup of it. In order to identify which database to back up, you can examine the connection string shown in the SSRPM Admin Console. 

  1. Open the SSRPM Admin Console
  2. Select Service Management > Configure... in the menu bar
  3. Click on the Database tab


The server/instance name of the SQL server that holds the SSRPM database can be found next to the Data Source entry. The name of the actual database object is next to the Initial Catalog entry. Armed with those two pieces of information, you're ready to create a backup.

In order to create the backup, we recommend using SQL Server Management Studio and following the instructions in this article from Microsoft.

Step Three: Restore the backup onto the new server

After you create the backup copy of the SSRPM database, it's time to restore it to the target server. First, copy the backup file that you create in the previous step from the old server to the new server. Then, follow the instructions in this article from Microsoft to restore the database.

Step Four: Add necessary permissions to the database

By default, SSRPM will connect to its SQL database in the context of its service account (Windows authentication). On the server that is running the SSRPM service, open the services console and make note of the account that is running the Self Service Reset Password Management Service. Once you've made note of that, head back to SQL Server Management Studio and follow the instructions in this article from Microsoft to grant that account the DBOwner role on the SSRPM database object. This will give the SSRPM service the right to connect to its own database and update it as necessary.

Step Five: Update the connection string in the admin console

Now that the SSRPM database has been migrated to a new server and the service account has been given the proper rights to it, it's time for you to update the SSRPM service's SQL connection string.

  1. Open the SSRPM Admin Console
  2. Select Service Management > Configure... in the menu bar
  3. Click on the Database tab
  4. Click Connection String Wizard...
  5. On the Provider tab, select Microsoft OLE DB Provider for SQL Server and click Next
  6. On the Connection tab, do the following:
    1. In box 1, type the name/instance of your new SQL server instance.
    2. In area 2, select Use Windows NT Integrated Security (Connection tests are done in the context of the account running the admin console. Either make sure your account has rights to the SSRPM database, or run the console in the context of the SSRPM service account).
    3. In box 3, select the SSRPM database object from the list of databases provided.
    4. Click Test Connection and then click OK in the event of a successful test. If your test does not succeed, or you run into errors, go back and verify all permissions to the SQL instance and database.
  7. Click OK when you are finished. The new connection string will be sent to the SSRPM service, and you will be notified of a success or a failure in the update. If the update fails, go back and double check the permissions on the SQL server instance and database object.