Sign In with your
Trend Micro Account
Need Help?
Need More Help?

Create a technical support case if you need further support.

Manual migration of ScanMail database from local SQL Server to remote SQL Server

    • Updated:
    • 9 Mar 2021
    • Product/Version:
    • ScanMail for Exchange 14.0
    • Platform:
    • N/A
Summary

Learn how to manually migrate the ScanMail for Exchange (SMEX) 14.0 database to a remote SQL server, either using SQL Authentication or Windows Authentication.

Below are the prerequisites:

  • The ScanMail installed should be at version 14.0.
  • The original ScanMail database is hosted locally using SQL.
Details
Public
  1. Back up the ScanMail database from the local SQL Server.
    1. On the SQL Server Management Studio (SSMS), connect to the SQL Server Instance of the ScanMail server.
    2. Right-click the ScanMail database and select Tasks > Back Up... to back up the ScanMail database to .bak files (e.g. ScanMail.bak).

      Back up the ScanMail database

    3. Under the Source section, set Backup type: to Full.
    4. Under the Destination section, set Back up to: to Disk, and then specify the destination folder and file name.

      Backup configuration

    5. When ready, click OK. The following message will appear after a successful database backup.

      Successful database backup

    6. Ensure that the database backup is stored on a safe location.
  2. Restore the database to the remote SQL Server.
    1. Copy the database .bak file to the remote SQL server.
    2. Using SSMS, connect to the SQL Server Instance of the remote SQL server.
    3. On the left panel, right-click Databases and click Restore Database... to restore the ScanMail database from the .bak file.

      Restore Database

    4. Under the Source section, select Device and browse for the .bak file you transferred to the remote SQL server.

      Select Device and choose the bak file

    5. Click OK twice to prepare the Restore Database wizard.
    6. When ready, click OK. The message below will appear after a successful database restoration.

      Database restored successfully

  1. On the ScanMail Server, copy the database files ScanMail.mdf and ScanMail_log.ldf and paste them to the remote SQL Server directory.

    Copy and paste ScanMail database files

  2. Go to the remote SQL server and open the SQL Server Management Studio (SSMS).
  3. On the Object Explorer section, right-click Databases and select Attach...

    Attach

  4. On the Attach Databases window, click Add...

    Add files

  5. Select the ScanMail.mdf file you copied earlier from the ScanMail server.

    Select the ScanMail MDF file

  6. Click OK to select the file and go back to the Attach Databases window. It will list the MDF file and database details.

    Attach Databases window

  7. Click OK. The ScanMail database should now be listed on the Object Explorer section.

    ScanMail database

  1. Open the Command Prompt as administrator and run the following:
    mkdir C:\_trendBackup
    mkdir C:\_trendBackup\smex_backup
    mkdir C:\_trendBackup\smex_backup\config
    cd "C:\Program Files\Trend Micro\Smex\config"
    copy dbcfg_* "C:\_trendBackup\smex_backup\config\"
  2. Verify that a backup copy of the configuration files has been created.

Below are the two (2) options to configure the database authentication for the ScanMail databases imported into the remote SQL server:

  1. Check the user login.
    On the SQL Server Management Studio, expand Security and click Logins.
     
    If you do not have a Windows Authentication User Login for SMEX, add one first. Consult your database administrator for further assistance.
  2. Once you have a domain service account, right-click the Windows Service Account and click Properties.

    Windows Service Account

  3. On the Login Properties window, click Server Roles and select dbcreator and public checkboxes.

    Server Roles

  4. On the left panel, click User Mapping.
  5. Put a check on the Map column that corresponds to the ScanMail database, then select the db_owner and public under the Database role membership section.

    User Mapping

  6. Once done, click OK.
  1. Check the user login.
    On the SQL Server Management Studio, expand Security and click Logins.
     
    If you do not have an SQL Authentication User Login for SMEX, add one first. Consult your database administrator for further assistance.
  2. Once you have a domain service account, right-click the Windows Service Account and click Properties.

    Windows Service Account Properties

  3. On the Login Properties windows, click Server Roles and select dbcreator and public checkboxes.

    Server Roles

  4. On the left panel, select User Mapping.
  5. Put a check on the Map column that corresponds to the ScanMail database, then select db_owner and public under the Database role membership section.

  6. Once done, click OK.

Below are some tips on how to retrieve the necessary information for editing the ScanMail configuration files.

  • To list the existing database instance on the network, use the following command (either on SMEX server or SQL server).
    osql -L

  • To get the exact database name of ScanMail, open the SQL Server Management Studio, connect to the remote SQL Server instance, and use the SQL query below:
    select * from sys.databases where name like 'ScanMail%';

    Exact name of ScanMail database

  • To get the database path of the .mdf and .ldf, open the SQL Server Management Studio and go to Database Properties > Files, then check the Path (e.g. D:\SQL_Files\SQL_Data).

 

Follow the procedure based on the database authentication method you chose in "Step 3. Configure database authentication on the remote SQL Server."

  1. Open the Command Prompt as administrator and run the commands below to stop the ScanMail for Microsoft Exchange Master Service and ScanMail for Microsoft Exchange System Watcher.
    sc stop ScanMail_Master
    sc stop ScanMail_SystemWatcher

    To verify if the service status is stopped, run the following:

    sc query ScanMail_Master
    sc query ScanMail_SystemWatcher
  2. Check the Service Dependency of ScanMail for Microsoft Exchange Master Service.

    By default, if you are using a local SQL database on the same server as SMEX, the Master Service will have a service dependency on the local Microsoft SQL Server Instance. Since we are configuring a remote SQL Server, we will need to remove this dependency.

    To view the existing service dependency, open the Command Prompt as administrator and run the following:

    sc qc ScanMail_Master

    Below is a sample output:

    SC] QueryServiceConfig SUCCESS
     
    SERVICE_NAME: ScanMail_Master
            TYPE               : 10  WIN32_OWN_PROCESS
            START_TYPE         : 2   AUTO_START
            ERROR_CONTROL      : 1   NORMAL
            BINARY_PATH_NAME   : "C:\Program Files\Trend Micro\Smex\svcGenericHost.exe" SMEX_Master.exe .\config\cfg_ipcServerDll1.txt SMEX_HOST
            LOAD_ORDER_GROUP   :
            TAG                : 0
            DISPLAY_NAME       : ScanMail for Microsoft Exchange Master Service
            DEPENDENCIES       : MSSQL$SCANMAIL
            SERVICE_START_NAME : LocalSystem
  3. Remove the service dependency to the local SQL Server.

    Open the Command Prompt as administrator and run the command below: 

    sc config ScanMail_Master depend= ""

    To verify, run the following command:

    sc qc ScanMail_Master
  4. Modify the database configuration files to use the remote SQL Server.

    Open the Command Prompt as administrator and run the command below. If you have installed SMEX on a non-default directory, change the directory location accordingly.

    cd "C:\Program Files\Trend Micro\Smex\config"
    CommandDescription
    notepad dbcfg_ConfDBName.txt
    Verify that it uses the correct ScanMail database name.
    notepad dbcfg_ConfInitFileName.txt
    Verify that this file is blank.
    notepad dbcfg_DatabaseInstance.txt
    Modify this file and use the SQL Server FQDN. Make sure that SMEX can resolve the FQDN.
    notepad dbcfg_InstallPath.txt
    Modify this file and use the Path (Directory) of the ScanMail .mdf and .ldf on the remote SQL Server.
    notepad dbcfg_IsSeperateSQLServer.txt
    Modify this file, change the value to 1.
    notepad dbcfg_LogDBName.txt
    Verify that it uses the correct ScanMail database name.
    notepad dbcfg_ReportDBName.txt
    Verify that it uses the correct ScanMail database name.
    notepad dbcfg_ReportInitFileName.txt
    Verify that this file is blank.
    notepad dbcfg_SecurityString.txt
    Verify that it uses the following values: Integrated Security=SSPI;Persist Security Info=False;
    notepad dbcfg_ServerName.txt
    Verify that it uses the hostname of the ScanMail server.
    notepad dbcfg_SQLPassword.txt
    Modify this file and remove all its content, it should be blank.
    notepad dbcfg_SQLServerAuthenticationMethod.txt
    Verify that this file has a value of 1.
    notepad dbcfg_SQLUserId.txt
    Verify that this file is blank.
  5. Modify the ScanMail for Exchange Master Service and System Watcher Service to use the Windows Service Account.

    Open the Command Prompt as administrator and run the commands below. Make sure to replace the command with your domain service account and password, which should be the same domain service account configured on the SQL Windows Authentication.

    sc config ScanMail_Master obj= "DOMAIN\SVC_ACCOUNT" password= "PASSWORD" type= own
    sc config ScanMail_SystemWatcher obj= "DOMAIN\SVC_ACCOUNT" password= "PASSWORD" type= own
  6. Start the ScanMail for Exchange Master Service and System Watcher Service by running the following command in the Command Prompt as administrator.
    sc start ScanMail_Master
    sc start ScanMail_SystemWatcher

    Starting the ScanMail for Exchange Master Service takes a few minutes. Verify the service status again after a few minutes and open the web console to verify that it works. In services.msc, the services should look like below.

  1. Open the Command Prompt as administrator and run the commands below to stop the ScanMail for Microsoft Exchange Master Service and ScanMail for Microsoft Exchange System Watcher.
    sc stop ScanMail_Master
    sc stop ScanMail_SystemWatcher

    To verify if the service status is stopped, run the following:

    sc query ScanMail_Master
    sc query ScanMail_SystemWatcher
  2. Check the Service Dependency of ScanMail for Microsoft Exchange Master Service.

    By default, if you are using a local SQL database on the same server as SMEX, the Master Service will have a service dependency on the local Microsoft SQL Server Instance. Since we are configuring a remote SQL Server, we will need to remove this dependency.

    To view the existing service dependency, open the Command Prompt as administrator and run the following command:

    sc qc ScanMail_Master

    Below is a sample output:

    [SC] QueryServiceConfig SUCCESS
     
    SERVICE_NAME: ScanMail_Master
            TYPE               : 10  WIN32_OWN_PROCESS
            START_TYPE         : 2   AUTO_START
            ERROR_CONTROL      : 1   NORMAL
            BINARY_PATH_NAME   : "C:\Program Files\Trend Micro\Smex\svcGenericHost.exe" SMEX_Master.exe .\config\cfg_ipcServerDll1.txt SMEX_HOST
            LOAD_ORDER_GROUP   :
            TAG                : 0
            DISPLAY_NAME       : ScanMail for Microsoft Exchange Master Service
            DEPENDENCIES       : MSSQL$SCANMAIL
            SERVICE_START_NAME : LocalSystem
  3. Remove the service dependency to the local SQL Server by running the following command in the Command Prompt as administrator:
    sc config ScanMail_Master depend= ""

    To verify, run the command below:

    sc qc ScanMail_Master
  4. Modify the database configuration files to use the remote SQL Server.

    Open the Command Prompt as administrator and run the command below. If you have installed SMEX on a non-default directory, change the directory location accordingly.

    cd "C:\Program Files\Trend Micro\Smex\config"
    CommandDescription
    notepad dbcfg_ConfDBName.txt
    Verify that it uses the correct ScanMail database name.
    notepad dbcfg_ConfInitFileName.txt
    Verify that this file is blank.
    notepad dbcfg_DatabaseInstance.txt
    Modify this file and use the SQL Server FQDN. Make sure that SMEX can resolve the FQDN.
    notepad dbcfg_InstallPath.txt
    Modify this file and use the Path (Directory) of the ScanMail .mdf and .ldf on the remote SQL server.
    notepad dbcfg_IsSeperateSQLServer.txt
    Modify this file, change the value to 1.
    notepad dbcfg_LogDBName.txt
    Verify that it uses the correct ScanMail database name.
    notepad dbcfg_ReportDBName.txt
    Verify that it uses the correct ScanMail database name.
    notepad dbcfg_ReportInitFileName.txt
    Verify that this file is blank.
    notepad dbcfg_SecurityString.txt
    Verify that it uses the following value: Persist Security Info=True;
    notepad dbcfg_ServerName.txt
    Verify that it uses the hostname of the ScanMail server.
    notepad dbcfg_SQLServerAuthenticationMethod.txt
    Verify that this file has a value of 0.
    notepad dbcfg_SQLUserId.txt
    Modify this file and enter the SQL authentication login name.
    notepad dbcfg_SQLPassword.txt
    Note: The following steps will change the encrypted value inside "C:\Program Files\Trend Micro\Smex\config\dbcfg_SQLPassword.txt"
    cd "C:\Program Files\Trend Micro\Smex\"
    toolChangeRemoteDBPWD.exe -p "C:\Program Files\Trend Micro\Smex\config" -c <SQL_ACCOUNT_PASSWORD>

    To verify, use the following commands:

    cd "C:\Program Files\Trend Micro\Smex\config"
    notepad dbcfg_SQLPassword.txt
  5. Start the ScanMail for Exchange Master Service and System Watcher Service by running the commands below in the Command Prompt as administrator.
    sc start ScanMail_Master
    sc start ScanMail_SystemWatcher

    Starting the ScanMail for Exchange Master Service takes a few minutes. Verify the service status again after a few minutes and open the web console to verify that it works. In services.msc, the services should look like below.

Open the ScanMail Management Console and login. Verify if all the configurations are there and no error appears on the console.

Once the ScanMail database has been successfully migrated, uninstall the ScanMail database instance in the local SQL Server. Follow this Microsoft article for the complete procedure: Uninstall an Existing Instance of SQL Server (Setup).

Premium
Internal
Partner
Rating:
Category:
Migrate
Solution Id:
1114008
Feedback
Did this article help you?

Thank you for your feedback!


*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.

If you need additional help, you may try to contact the support team. Contact Support

To help us improve the quality of this article, please leave your email here so we can clarify further your feedback, if neccessary:
We will not send you spam or share your email address.

*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.