Migrating Data between different Databases

ManageEngine Password Manager Pro supports the following databases: PostgreSQL, MySQL and MS SQL, where PostgreSQL comes bundled with the product, by default. In general, there are various reasons to migrate from one database to another which include performance issues, stability, reliability, High Availability issues, costs for the license (dual license, GPL), etc.

This document walks you through the process of migrating your data between the PostgreSQL, MySQL and MS SQL databases. Click the below links for the steps to migrate from one database to another:

  1. Migrating Data from MySQL Database to PostgreSQL Database
  2. Migrating Data from MySQL Database to MS SQL Database
  3. Migrating Data from PostgreSQL Database to MS SQL Database

1. Migrating data from MySQL Database to PostgreSQL Database
(Procedure applicable only for builds 6801 and later)

Important Prerequisites:

1. Please take a backup of the entire Password Manager Pro directory, prior to migrating the database.
2. The users, who use the "Personal" tab to store their personal passwords and use their own personal passphrase to log in to the "Personal" tab, must export their passwords out of Password Manager Pro before migration. They can reimport those personal passwords after the migration.
3. Login to Password Manager Pro UI with an administrator account and navigate to "Admin >> Audit". Check the total number of resource/user/task audits. If the total number of the resource/user/task audits is > 200000, please contact our support at passwordmanagerpro-support@manageengine.com.

Steps Required:

Note: Please take a complete backup of Password Manager Pro, prior to making any changes, or take a VM snapshot, so that you can restore the data in case of a failure.

  1. Stop the Password Manager Pro service and make sure the "mysqld.exe" process is not running.
  2. Open a command prompt and navigate to the  <Password Manager Pro Installation Folder>/bin directory.
  3. Execute MigrateMySQLToPgSQL.bat (in Windows) or MigrateMySQLToPgSQL.sh (in Linux).
  4. Start the Password Manager Pro service. Now, Password Manager Pro will run with PostgreSQL as the backend database.

2. Migrating Data from MySQL Database to the MS SQL Database
(Procedure applicable only for Password Manager Pro builds 6401 and later)

If you are right now using Password Manager Pro with MySQL database and wish to use the MS SQL database instead, follow the steps below to migrate the data from the former to the latter.

Important Prerequisites:

1. This section guides you through only the migration process and does not include any database configuration aspects. Hence, if you have decided to use MS SQL as the dedicated backend database, make sure you have already completed the steps 8.1.1 to 8.1.3 under MS SQL Configuration before you begin the migration.

2. Before commencing the migration, ensure the following aspects are taken care of:

i. Personal Password Management: If users in your organization have used the "Personal Password Management" option in Password Manager Pro by specifying their own passwords (which are NOT stored in Password Manager Pro), those personal passwords will not be migrated as a part of data migration. You need to use the "Export Passwords" option in the "Personal Passwords" section for the same. Learn More.

ii. Bundled Database Only: The migration can be carried out only if your current Password Manager Pro installation uses the database bundled with the product (PostgreSQL). This procedure DOES NOT apply if you are using any other external database.

iii. Before initiating the migration, take a complete back up of the existing Password Manager Pro setup. This will be helpful if the migration is unsuccessful.

Steps to Migrate:

PART 1:

1. Shutdown the Password Manager Pro server.
2. Ensure the process "mysqld" is not running.

PART 2 :

  1. Navigate to <Password Manager Pro Installation Folder>/bin folder and execute the file MigrateDB.bat (Windows) or sh MigrateDB.sh (Linux). In the window that pops up, choose to read the "Best Practices Guide" first and then choose the option "Go to Migration Set Up".

  1. In the window displayed, enter the following details:
    1. Host Name: The name or the IP address of the machine where MS SQL server is installed.
    2. Instance Name: The named instance of SQL server used by Password Manager Pro. If the instance name is not specified, Password Manager Pro will try establishing connection with the default instance on port 1433.
      Since Password Manager Pro connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Password Manager Pro. If you want to specify a port number other than 1433, you can specify it in the Host Name parameter above as <hostname>:<port>.
    3. Database Name: Name of the Password Manager Pro database. Default is "PassTrix". If you want to have a different database name, you may specify here. Password Manager Pro will take care of creating the Master Key, Symmetric Key etc.
    4. Authentication: The way to connect to the SQL server. If you are connecting to the SQL server from Windows, you have the option to make use of the Windows Single-Sign-On facility, provided the Password Manager Pro service is running with a service account, which has the privilege to connect to the SQL server. In this case, choose the option "Windows". Otherwise, select the option "SQL". [It is recommended to choose the option 'Windows' as the username and password used for authentication are not stored anywhere].
    5. User Name and Password: If you have selected the option "SQL", specify the user name and password with which Password Manager Pro connects to the database. [The username and password entered here will be stored in the "database_params.conf" file in Password Manager Pro. So, you need to take care of hardening the host].
      You can also use your Windows login credentials, if you are connecting to the database from Windows. In this case, enter the username as <domain-name>\<username>.
    6. Encryption Key: The key with which your data is to be encrypted and stored in the SQL server. You may either leave it "Default", allowing Password Manager Pro to generate a key, else if you want to have your custom key, select the option "Custom".
    7. If you have selected the option "Custom:" If you have chosen the option 'Custom', you need to create a new database, create Master Key, create Certificate (this will be certificate name) and Create the Symmetric Key using AES 256 encryption. Follow the below steps:

      Create Database -> For details, refer to http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx
      Create Master Key -> For details, refer to http://technet.microsoft.com/en-us/library/ms174382.aspx
      Create Certificate -> For details, refer to http://msdn.microsoft.com/en-us/library/ms187798.aspx
      Create Symmetric Key -> For details, refer to http://msdn.microsoft.com/en-us/library/ms188357.aspx
      After executing the above steps, you need to provide certificate name and symmetric key name in the GUI.

PART 3:

Finally, click "Test" to ensure that the connection settings are proper and then click "Migrate". The status of data migration will be displayed in the textbox. Once the migration is complete, start the Password Manager Pro server.

Troubleshooting Tips:

Error Cause Effect Solution

Server seems to be running. Shutdown Password Manager Pro server and try again.

You might have attempted the database migration when the Password Manager Pro server is running,

You will encounter this error in the DB Migration GUI and the GUI will remain open.

Shut down the server and try again. If the error repeats, delete the ".lock" file under the "<Password Manager Pro-Installation-Folder>/bin" folder and then try again. If the issue persists, contact passwordmanagerpro-support@manageengine.com with the ".lock" file.


Important Note:

After completing the migration, start the Password Manager Pro server and navigate to "Admin >> Resource Additional Fields" and "Admin >> Accounts Additional Fields". Open the GUI and click "Save". This will restore the additional fields added by you in the migrated instance as well.



3. Migrating Data from PostgreSQL Database to the MS SQL Database
(Procedure applicable only for Password Manager Pro builds 6401 and later)

If you are right now using Password Manager Pro with PostgreSQL database and wish to use the MS SQL database instead, follow the steps below to migrate the data from the former to the latter.

Important Prerequisites:

1. This section guides you through only the migration process and does not include any database configuration aspects. Hence, if you have decided to use MS SQL as the dedicated backend database, make sure you have already completed the steps 1 & 2 and step 3 under MS SQL Configuration before you begin the migration.

2. Before commencing the migration, ensure the following aspects are taken care of:

i. Personal Password Management: If users in your organization have used the "Personal Password Management" option in Password Manager Pro by specifying their own passwords (which are NOT stored in Password Manager Pro), those personal passwords will not be migrated as a part of data migration. You need to use the "Export Passwords" option in the "Personal Passwords" section for the same. Learn More.

ii. Bundled Database Only: The migration can be carried out only if your current Password Manager Pro installation uses the database bundled with the product (PostgreSQL). This procedure DOES NOT apply if you are using any other external database.

iii. Before initiating the migration, take a complete back up of the existing Password Manager Pro setup. This will be helpful if the migration is unsuccessful.

Steps to Migrate:

PART 1:

1. Shutdown the Password Manager Pro server.
2. Ensure the process "postgres" is not running.

PART 2 :

  1. Navigate to <Password Manager Pro Installation Folder>/bin folder and execute the file MigrateDB.bat (Windows) or sh MigrateDB.sh (Linux). In the window that pops up, choose to read the "Best Practices Guide" first and then choose the option "Go to Migration Set Up".

  1. In the window displayed, enter the following details:
    1. Host Name: The name or the IP address of the machine where MS SQL server is installed.
    2. Instance Name: The named instance of SQL server used by Password Manager Pro. If the instance name is not specified, Password Manager Pro will try establishing connection with the default instance on port 1433.
      Since Password Manager Pro connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Password Manager Pro. If you want to specify a port number other than 1433, you can specify it in the Host Name parameter above as <hostname>:<port>.
    3. Database Name: Name of the Password Manager Pro database. Default is "PassTrix". If you want to have a different database name, you may specify here. Password Manager Pro will take care of creating the Master Key, Symmetric Key etc.
    4. Authentication: The way to connect to the SQL server. If you are connecting to the SQL server from Windows, you have the option to make use of the Windows Single-Sign-On facility, provided the Password Manager Pro service is running with a service account, which has the privilege to connect to the SQL server. In this case, choose the option "Windows". Otherwise, select the option "SQL". [It is recommended to choose the option 'Windows' as the username and password used for authentication are not stored anywhere].
    5. User Name and Password: If you have selected the option "SQL", specify the user name and password with which Password Manager Pro connects to the database. [The username and password entered here will be stored in the "database_params.conf" file in Password Manager Pro. So, you need to take care of hardening the host].
      You can also use your Windows login credentials, if you are connecting to the database from Windows. In this case, enter the username as <domain-name>\<username>.
    6. Encryption Key: The key with which your data is to be encrypted and stored in the SQL server. You may either leave it "Default", allowing Password Manager Pro to generate a key, else if you want to have your custom key, select the option "Custom".
    7. If you have selected the option "Custom:" If you have chosen the option 'Custom', you need to create a new database, create Master Key, create Certificate (this will be certificate name) and Create the Symmetric Key using AES 256 encryption. Follow the below steps:

      Create Database -> For details, refer to http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx
      Create Master Key -> For details, refer to http://technet.microsoft.com/en-us/library/ms174382.aspx
      Create Certificate -> For details, refer to http://msdn.microsoft.com/en-us/library/ms187798.aspx
      Create Symmetric Key -> For details, refer to http://msdn.microsoft.com/en-us/library/ms188357.aspx
      After executing the above steps, you need to provide certificate name and symmetric key name in the GUI.

PART 3:

Finally, click "Test" to ensure that the connection settings are proper and then click "Migrate". The status of data migration will be displayed in the textbox. Once the migration is complete, start the Password Manager Pro server.

Troubleshooting Tips:

Error Cause Effect Solution

Server seems to be running. Shutdown Password Manager Pro server and try again.

You might have attempted the database migration when the Password Manager Pro server is running,

You will encounter this error in the DB Migration GUI and the GUI will remain open.

Shut down the server and try again. If the error repeats, delete the ".lock" file under the "<Password Manager Pro-Installation-Folder>/bin" folder and then try again. If the issue persists, contact passwordmanagerpro-support@manageengine.com with the ".lock" file.


Important Note:

After completing the migration, start the Password Manager Pro server and navigate to "Admin >> Resource Additional Fields" and "Admin >> Accounts Additional Fields". Open the GUI and click "Save". This will restore the additional fields added by you in the migrated instance as well.

Steps to Re-import Personal Passwords

In general, Password Manager Pro does not allow the migration of personal passwords. Hence, before data migration, personal passwords need to be exported out of Password Manager Pro in the form of an XLS file. Once the migration process is over, the passwords can be re-imported into Password Manager Pro using a CSV or TXT file. Here are the steps:

  • Open the .xlsx file, containing the personal passwords, using MSExcel.
  • Click "File >> Save as" and choose either of the formats .csv or .txt.
  • Click Save.

       

©2014, ZOHO Corp. All Rights Reserved.

Top