Issue description   

Organizations often migrate the ADManager Plus database from the built-in PostgreSQL to an external Microsoft SQL Server or other external databases to enhance performance, scalability, and reliability.

An external database is better suited for handling large datasets, especially when managing multiple domains. It also supports high availability configurations, ensuring continuous service during failures. Additionally, external databases offer advanced backup, recovery options, and centralized management, while meeting stricter security and compliance requirements.

However, migration can sometimes fail, resulting in errors such as the process stopping unexpectedly, data transfer issues, or connection failures.

Possible causes   

The database migration failure may occur due to one or more of the following reasons:

1. Missing bcp files: The bcp.exe and bcp.rll from the SQL Server installation must be placed in the ADManager Plus bin folder.

2. Incorrect ODBC driver or command-line utilities: The correct ODBC driver and SQL command-line utilities, corresponding to the SQL Server version, must be installed.

3. Connectivity issues between ADManager Plus and SQL Server: Firewall rules or network restrictions may be blocking the connection.

4. The service account does not have the necessary permission: Windows authentication and SQL Server authentication do not have permission to create a database.

5. The logged-in user lacks permissions to the installation folder: The account that executed the ChangeDB.bat file from the Command Prompt may not have adequate permissions to update configuration files in the Conf directory.

6. Password complexity for the SQL account: If an SQL account is used, it must comply with the SQL Server password policy.

Prerequisites   

Before troubleshooting, ensure:

• SQL Server is accessible from the ADManager Plus server.

• The necessary ODBC drivers and SQL command-line utilities are installed.

• The service account has the necessary permissions.

Resolution  

Follow these steps to diagnose and fix the Microsoft SQL database migration failure.

Step 1: Verify and copy the required files  

1. Navigate to the SQL Server installation directory on your database server.

2. Locate the following files:

• bcp.exe: Found under:
  C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\<version>\Tools\Binn\

• bcp.rll: Found under:
  C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\<version>\Tools\Binn\Resources\1033\

3. Copy both the bcp.exe and bcp.rll files.

4. Paste them into the ADManager Plus bin folder:

• C:\ManageEngine\ADManager Plus\bin\

Step 2: Install the correct ODBC driver and SQL command-line utilities  

1. Identify your SQL Server version.
   

2. Download the corresponding ODBC driver and SQL command-line utilities from the official Microsoft Download Center.

3. Install the ODBC driver and utilities on the ADManager Plus server.

4. Restart the server and retry the migration.
   

Step 3: Verify connectivity between ADManager Plus and SQL Server  

1. Open the Command Prompt on the ADManager Plus server.

2. Run the following command to test connectivity:

• tnc <SQL_SERVER_HOSTNAME> -port <SQL_SERVER_PORTNUMBER>

3. If the test connection fails, check the firewall settings.

4. Ensure that the SQL Server port (1433 by default) is accessible from the ADManager Plus server.

Step 4: Grant permissions to the service account  

To grant the required permissions:

1. Open SQL Server Management Studio (SSMS).

2. Right-click the user (service account used for migration) > Properties > Server Roles. Check whether the user has been assigned the sysadmin role and follow the steps below:

• If yes, proceed to migration.

• If not, check the Sysadmin box and then click OK.

Note: To grant the user only the minimum permissions required instead of a sysadmin role, follow the two steps below:

• Right-click the user > Properties > User Mapping. Check the boxes next to db_datareader, db_datawriter, and db_ddladmin and click OK.

• Right-click the database > Properties > Permissions, provide Execute permission for the user, and click OK.
  

Step 5: Ensure the logged-in user has installation folder permissions  

1. Navigate to the ADManager Plus installation folder (default: C:\ManageEngine\ADManager Plus).
   
2. Right-click the folder and select Properties.

3. Go to the Security tab.

4. Select the logged-in user and ensure they have Full Control.

5. Click Apply and OK.

6. Retry the migration process.
   

Step 6: Verify SQL account password complexity requirements  

1. Open SSMS.

2. Navigate to Security > Logins.

3. Right-click the SQL account used for migration and select Properties.

4. Go to the General tab and verify the password complexity settings.

5. If needed, reset the password to meet the SQL Server password policy.

6. Retry the database migration. 

Tips 

• Create a dedicated SQL service account with necessary permissions instead of using high-privileged accounts like the sysadmin role for better security and fewer conflicts.

• Install the ODBC driver and SQL command-line utilities matching the SQL Server version to avoid compatibility issues during migration.

How to reach support