Migration from PostgreSQL database server to MS SQL

Migration from PostgreSQL database server to MS SQL

ADSelfService Plus comes bundled with a PostgreSQL database server to store basic user attributes and enrollment data. It also allows you to migrate from the built-in PostgreSQL to MS SQL. This guide will walk you through the database server migration process.

Supported versions of MS SQL Server: 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, and 2019.

Prerequisites

In the MS SQL server instance used for migration, make sure that the following conditions are satisfied:
  1. The SQL Server Browser service must be up and running.
  2. For SQL Server Network Configuration, TCP/IP protocol must be enabled.
  3. All the client protocols must be enabled.
  4. SQL Server Access is delegated to a user with sysadmin and db_owner permissions at the server and database levels respectively.
Refer to Appendix A for configuring Prerequisites 1, 2, and 3. Refer to Appendix B for configuring Prerequisite 4.

Migrating from PostgreSQL to MS SQL

Migrating data from ADSelfService Plus’ PostgreSQL database to MS SQL consists of the following steps:
  1. Backing up the ADSelfService Plus database.
  2. Migrating PostgreSQL data to the MS SQL server.

Step 1: Backing up the ADSelfService Plus database

If you're installing ADSelfService Plus for the first time and don’t have any data to back up, skip this step and proceed to the next step. However, if you want to backup the data, follow the steps given below:
      1. Stop ADSelfService Plus:
                  i. If ADSelfService Plus is running in console mode, click the Windows icon, search for ADSelfService Plus, and choose Stop ADSelfService Plus.
                 ii. If ADSelfService Plus is running as a service, click the Windows icon. Search for Services. In the window that appears, choose ManageEngine ADSelfService Plus, and select the Stop the service option.
      2. Navigate to <ADSelfService Plus installation directory>\bin.
Note: By default, ADSelfService Plus is installed in: C:\ManageEngine\ADSelfService Plus.
      3. Run the backupDB (Windows Batch) file as an administrator. Don’t terminate until the process is finished.
Backing up the ADSelfService Plus database
      4. Data in the default database of ADSelfService Plus will be backed up and stored under <ADSelfService Plus installation                                                             directory>\backup\OfflineBackup _<Backup Time>.
Note: If the error below shows up while running the backupDB file, make sure the product has been stopped before proceeding.

Step 2: Migrating PostgreSQL Data to MS SQL

It's important to make sure you stop ADSelfService Plus before this process.
      1. If the MS SQL Server is installed in a remote computer, install the necessary command line utilities and the MS SQL Native Client, on the remote                    computer, and proceed to the next step.
Note: The links provided below will redirect you to the main Microsoft SQL feature pack page. On pressing the corresponding download button, you get a set of command line utilities and native client formats. Install the corresponding SQL Native Client or command line utilities as per the MS SQL Server version and CPU type of the machine where ADSelfService Plus is installed. The command line utilities have the term SQLCMD in them, and the native client file can be found under the name sqlncli.

      2. Copy the following files to <ADSelfService Plus installation directory>\bin folder.
                  i. bcp.exe: \Tools\Binn\bcp.exe
                 ii. bcp.rll: \Tools\Binn\Resources\1033\bcp.rll
Note: In order to ensure that bcp.exe's dependencies are installed correctly, Open Command Prompt in <ADSelfService Plus installation directory>\bin and run bcp.exe /v. If the bcp.exe file's version is displayed it means the dependencies are properly installe
      3. Navigate to <ADSelfService Plus installation directory>\bin.
      4. If you don't have any data to migrate, run ChangeDB (Windows batch file) as an administrator.
Migrating PostgreSQL Data to MS SQL
Note: To migrate with the data intact, open Command Prompt, navigate to the <ADSelfService Plus installation directory>\bin, and run the changeDB.bat true command with admin privileges.
      5. The DB Configuration wizard will open. Select MS SQL Server as the Server Type, and enter the Host Name of the MS SQL Server instance.
      6. Ensure that SQL browser server is running and the ports 1433 for TCP, and 1434 for UDP are open so that the available MS SQL Server instances are            fetched and displayed. The values for Port number and Available MS SQL Server Instances will be automatically loaded. From the displayed list of                    instances, select the one you'd like to use, and enter the Database Name.
      7. If you choose Windows Authentication, provide the Domain Name, Username and Password of the user account that has access to the server.
Note:
  1. Windows Authentication can't be used when ADSelfService Plus server and MS SQL server are residing in different domains. In this case, use SQL Server Authentication.
  2. If ADSelfService Plus is already running as a Windows service, you can use its service account for the database configuration or create a separate account. If you are creating a separate account, ensure that the ADSelfService Plus service account also has the permission required to access the MS SQL server. Check out Appendix B to know about the necessary permissions.
MS SQL database setup
      8. Click Test to check whether the entered credentials are correct. If the connection fails, the entered credentials may be wrong. Try using the correct                credentials.
      9. Click Save.

Appendix A

Configuring MS SQL Server

If you already have a functional MS SQL Server instance, then this step is not required. Follow the steps below to configure a freshly installed MS SQL Server instance:
  1. Open SQL Server Configuration Manager, or run compmgmt.msc in the Command Prompt.
  2. Go to SQL Server Services > SQL Server Browser. Make sure the SQL Server Browser is running.Configuring MS SQL Server
  3. Go to SQL Server Network Configuration, and double-click Protocols for <Instance_name>.
  4. Click on the TCP/IP protocol, and enable it.
  5. Go back to SQL Server Configuration Manager. In the left pane:
    1. Navigate to SQL Server Network Configuration > Protocols for SQLEXPRESS, and enable all the protocols.
    2. Navigate to SQL Native Client Configuration > Client Protocols, and enable all the protocols.
  6. Restart the SQL Server Service for the changes to take effect.
Note: SQLEXPRESS is the instance name provided while configuring MS SQL Server in general, however, it can be changed. SQLEXPRESS will be used in this document hereafter.

Appendix B

Delegating MS SQL Server access to users

To complete the migration process, you need a database admin account for the MS SQL connection. If ADSelfService Plus is already running as a Windows service, you can use its service account as the MS SQL database admin account. Ensure the account has the required privileges. If there is no such account in MS SQL, follow the steps given below to create a new user account and assign the required permissions to it.
  1. Log in to SQL Server Management Studio.
  2. In the left pane, navigate to Machine Name > SQLEXPRESS > Security > Logins.
  3. Right-click on Logins, and select New Login.
  4. Provide a Login Name, and choose whether to use Windows Authentication or SQL Server Authentication.
    1. If you choose Windows Authentication, enter the Windows NT name of the user to whom access must be granted.
    2. If you choose SQL Server Authentication, you will be prompted to create a new Username and Password.
  5. The new user must have the sysadmin role in the server level and db_owner role in the database level. Follow these steps to provide the sysadmin and db_owner role permission:
    1. Navigate to Machine Name > SQLEXPRESS > Security > Logins. Right-click the user > Properties.
    2. Go to Server Roles, select the sysadmin check box, and click OK.
    3. Go to User Mapping in the left pane. In the Users mapped to this login list, check the box next to the database. In the Database role membership for list, select db_owner, and click OK.
Delegating MS SQL Server access to users
Note: For details about user roles, refer to the following documents:
  1. For Server-Level Roles: http://msdn.microsoft.com/en-us/library/ms188659.aspx
  2. For Database-Level Roles: http://msdn.microsoft.com/en-us/library/ms189121.aspx
In general, the configured account needs one of these three sets of privileges to complete the migration process successfully:

Required database
Required permissions
Set 1
db_owner
Not required
Set 2
db_datareader
db_datawriter
db_ddladmin
db_backupoperator
Not required
Set 3

db_ddladmin
ALTER ANY TABLE,
ALTER ANY AGGREGATE,
ALTER ANY DEFAULT,
ALTER ANY FUNCTION,
ALTER ANY PROCEDURE,
ALTER ANY QUEUE,
ALTER ANY RULE,
ALTER ANY SYNONYM,
ALTER ANY TYPE,
ALTER ANY VIEW,
ALTER ANY XML SCHEMA COLLECTION,
ALTER ANY REFERENCES,
CONTROL ON CERTIFICATE::[ZOHO_CERT] TO [user],
CONTROL ON SYMMETRIC KEY::[##MS_DatabaseMasterKey##] TO [user],
CONTROL ON SYMMETRIC KEY::[ZOHO_SYMM_KEY] TO [user]
Important: Please note that you must have the db_owner permission while migrating PostgreSQL to MS SQL for the first time. After a successful migration, you can revoke the db_owner permission for the account, and provide the set 2 or set 3 permissions.


      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • How to migrate the ADSelfService Plus installation from one machine to another

                      Description This article will guide you through the process for migrating the ADSelfService Plus installation from one machine to another. Important: Before you start the migration process, please update your ADSelfService Plus installation to the ...

                    • Migrating from ADSelfService Plus 32-bit to ADSelfService Plus 64-bit

                      This article will help you migrate from ADSelfService Plus 32-bit version to the 64-bit version. Before you begin 32-bit to 64-bit migration is possible only between the same builds. For example, you cannot migrate from a 32-bit version of build 5310 ...

                    • Encryption and data storage in ADSelfService Plus database

                      Encryption in the ADSelfService Plus database ADSelfService Plus' database uses the following encryption methods to store sensitive data: Database Encryption method PostgreSQL AES-256-CBC Microsoft SQL AES-256-CBC The following sensitive information ...

                    • Configuring high availability in ADSelfService Plus

                      ADSelfService Plus utilizes automatic failover to support high availability in case of system and product failures. Essentially, this means that when the ADSelfService Plus service on one machine fails, another instance of ADSelfService Plus running ...

                    • ADSelfService Plus product startup issues

                      What do you need to know before troubleshooting You need to have administrator access to ADSelfService Plus. When you experience an error with ADSelfService Plus, check if these prerequisites are satisfied: Install ADSelfService Plus as a service ...

                      Related Products