Migrating from PostgreSQL Database to MS SQL / Azure MS SQL Database

ManageEngine PAM360 supports the following databases: PostgreSQL, MS SQL and Azure 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 from the PostgreSQL database to the MS SQL/Azure MS SQL databases.

Steps Required

  1. Prerequisites
  2. Migrating Data from PostgreSQL to MS SQL / Azure MS SQL
  3. Re-importing Personal Passwords

1. 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/Azure MS SQL as the dedicated backend database, make sure you have already completed these steps under MS SQL and Azure MS SQL configurations before you begin the migration.
  2. Before commencing the migration, ensure the following aspects are taken care of:
    1. Personal Password Management: If users in your organization have used the Personal Password Management option in PAM360 by specifying their own passwords (which are NOT stored in PAM360), 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.
    2. Bundled Database Only: The migration can be carried out only if your current PAM360 installation uses the database bundled with the product (PostgreSQL). This procedure DOES NOT apply if you are using any other external database.
    3. Before initiating the migration, take a complete back up of the existing PAM360 setup. This will be helpful if the migration is unsuccessful.
  3. If you have PAM360 installed in a Linux environment, do the steps that follow to migrate data from PostgreSQL to MS SQL/Azure MS SQL database:
    1. Install the respective XRDP based on your Linux operating system.
    2. Post the XRDP installation, take a remote session to the Linux environment using the native Remote Desktop Connection or any other third-party application from a Windows or Windows Domain resource.
    3. Enter the username and password of your Linux environment. Ensure that you use the Xvnc module as the session type.
      4. Now you can manage your Linux environment graphically to execute the below operations for migration.

2. Migrating Data from PostgreSQL to MS SQL/Azure MS SQL

  1. Shutdown the PAM360 server and ensure that PostgreSQL is not running.
  2. Navigate to <PAM360 Installation Folder>/bin folder and execute the file MigrateDB.bat (Windows) or sh MigrateDB.sh (Linux). In the window that pops up, choose the option Go to Migration Set Up.
  3. In the pop-up that opens, read the Best Practices Guide first by clicking the 'Open the best practices document' button and then click the 'Continue with migration setup' button to proceed with the migration process.
  4. In the pop-up that appears, enter the following details:
    1. Host Name: The name or the IP address of the machine where SQL server is installed.
    2. Instance Name: The named instance of SQL server used by PAM360. If the instance name is not specified, PAM360 will try establishing connection with the default instance on port 1433.
      Since PAM360 connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for PAM360. 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 PAM360 database. If you want to have a different database name, you may specify here. PAM360 will create the Master Key, Symmetric Key etc. If the Database name is not specified, PAM360 will create a database with the name PassTrix.
    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 PAM360 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 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 PAM360 connects to the database. [The username and password entered here will be stored in the database_params.conf file in PAM360. 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 as Default, allowing PAM360 to generate a key, or you can select Custom.
    7. If you choose 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

    8. Enter the Certificate Name and Symmetric Key Name in the GUI.
    9. Instance Type: PAM360 allows both 'Local Instance' (on-premise) and 'Azure SQL' as backend databases. Select either one of them to continue with the DB migration process.
    10. Connection Properties: After selecting this checkbox, you will have the option to specify custom connection properties in the following format: "key1=value1;key2=value2;".

      Note: The existing server details in the DB migration setup window and properties, such as SSL and encrypt, cannot be added as custom connection properties.

    11. Now, mention the Migration Status and click Test to verify the connection settings.
    12. Click Migrate.
  5. The status of data migration will be displayed in the textbox. Once the migration is complete, start the PAM360 server.

3. Re-importing Personal Passwords

In general, PAM360 does not allow the migration of personal passwords. Hence, before data migration, personal passwords need to be exported out of PAM360 in the form of an XLS file. Once the migration process is over, the passwords can be re-imported into PAM360.

Troubleshooting Tips

Error Cause Effect Solution

Server seems to be running. Shutdown PAM360 server and try again.

You might have attempted the database migration when the PAM360 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 <PAM360-Installation-Folder>/bin folder and then try again. If the issue persists, contact PAM360 support with the ".lock" file.


Important Note:

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