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

Create a technical support case if you need further support.

Migrating the Deep Security or Vulnerability Protection database

    • Updated:
    • 18 May 2016
    • Product/Version:
    • Deep Security 8.0
    • Deep Security 9.0
    • Deep Security 9.5
    • Deep Security 9.6
    • Vulnerability Protection 2.0
    • Platform:
    • Windows 2003 Enterprise 64-bit
    • Windows 2003 Enterprise Server
    • Windows 2008 Standard Server Edition
    • Windows 2008 Standard Server Edition 64-bit
Summary

Learn how to migrate the Deep Security or the Vulnerability Protection database. This article discusses the migration of the following:

  • From SQL Server Express to Microsoft SQL Server Enterprise
  • From Apache Derby to Microsoft SQL 2005/2008
Details
Public

To migrate the database from SQL Server Express to Microsoft SQL Server Enterprise:

  1. Back up the data you want to migrate using the scheduled task:
    1. Do the following:
      • For Deep Security 9.0 and Vulnerability Protection: Go to Administration > Scheduled Task > New.
      • For Deep Security 8.0 or lower: Go to System > Scheduled Task > New.
    2. Select Once Only as the frequency.
    3. Select the Backup task type and set the path (i.e. C:\dbbackup).
    4. Let the task run.
    5. Monitor the System Events for the Backup Finished event.
  2. Stop the Deep Security or Vulnerability Protection service when the event shows up. This will ensure that new logs/data are not created and restored.
  3. Go to the folder where you saved the database backup file and copy it to the machine where the new database will be saved.
  4. Restore the backup. You may create a new database called "dsm-restore1".
  5. Right-click the file and click Tasks > Restore.
  6. Link the file in the Devices area and then go to the Options tab and select to Overwrite Existing Database.
     
    The exact settings here may vary.
  7. After migrating the database, you need to point your Deep Security Manager (DSM) or Vulnerability Protection Manager (VPM) to the new database.
  8. Edit the following file on the DSM/VPM server host:

    Open the [Deep Security/Vulnerability Protection install directory]\webclient\webapps\ROOT\WEB-INF\dsm.properties file to see the database connection properties. Make sure to create a backup first before modifying the file.

  9. Update the file.

    Sample dsm.properties file looks like this:

     #Wed Jun 11 16:19:19 EDT 2008
    database.SqlServer.user=sa
    database.name=IDF
    database.directory=null\\
    database.SqlServer.password=$1$87251922972564e6bb3e2da917463fb1de
    4a5fcea848e688cd4ceb42b9bfb17a942c3c8ad99ff05938c81a60a2a11f4c3c6
    c1af5c9d01f3c8bfa60e634502aba112b9394ee4f73c970a6970fc9db6f96ba0cc
    80600ad4e36869881bddc3bdfc1abf8a7b2be459ff92c5dfeabbd8e7fd8
    database.SqlServer.instance=DSM
    mode.demo=false
    database.SqlServer.namedPipe=true
    database.type=SqlServer
    database.SqlServer.server=.
    manager.node=1

    It should be modified to look like this:

    #Wed Jun 11 16:19:19 EDT 2008
    database.SqlServer.user=sa
    database.name=idf-restore1
    database.directory=null\\
    database.SqlServer.password=<type the password of the sql account>
    database.SqlServer.instance=
    mode.demo=false
    database.SqlServer.namedPipe=false
    database.type=SqlServer
    database.SqlServer.server=<name of the sql server>
    manager.node=1

     
    Your options may vary, but be sure that if you chose named pipes, the proper windows authentication/trust exists between the DSM/VPM server host and the database host. If you chose TCP, make sure that it is enabled on the database.
  10. Start the Deep Security Manager or Vulnerability Protection Manager service.

If you are using a non-English SQL server, follow this KB article to change the language settings of the SQL admin for the DSM: Issues might occur when installing the Deep Security Manager (DSM) with non-English SQL server.

Prior to the database operations, delete all imported software:

  1. On the DSM, go to System > Updates > Software Updates.
  2. Click View Imported Software.
  3. Mark all packages and then click the Delete icon.

To migrate the DSM database from Apache Derby to SQL 2005/2008:

Step 1. Export tables from Apache Derby embedded database

This procedure will generate an SQL query file named DSMData.sql in the DSM installation folder.

  1. Open a command prompt (cmd.exe).
  2. Navigate to <install_path>\Program Files\Trend Micro\Deep Security Manager.
  3. Execute the following command:

    dsm_c -action createinsertstatements -databasetype sqlserver -generateDDL -goparameter GO

    Optional: To exclude security logs and make the resulting SQL file more manageable, include the following after "GO" above:

    -excludetables packetlogs,agentinstallers,integrityevents,loginspectionevents,payloadlogs

Step 2. Import tables into Microsoft SQL

Before importing the database, make sure that the MS SQL server DSM database is already in place. There are two (2) ways to perform the DSM database creation in MS SQL:

  • Using SQL Server Management Studio: Open MS SQL Server Management Studio and go to Database > New Database > Database name: dsm > Save.
  • Using OSQL command line: On the command prompt, type: "osql -U <username> -P <password>"

    Below is a sample:

    1> USE master
    2> GO
    1> CREATE DATABASE dsm
    2> GO
    1> quit

The following procedure is based on OSQL, but SQLCMD will work as well. To import the database:

  1. Open a command prompt (cmd.exe).
  2. Type the following command:

    SQLCMD -U <username> -P <password> -d <database_name> -i "<full_path to DSMData.sql>"

    Below is a sample:

    SQLCMD -U sa -P sa -d dsm -i "C:\Program Files\Trend Micro\Deep Security Manager\DSMData.sql"

     

    If your MS SQL server is hosted on a separate machine, you need to copy DSMData.sql to the local C:\temp drive first. For example: SQLCMD -U sa -P sa -d dsm -i "C:\temp\DSMData.sql".

    When you encounter an error in importing an SQL query that contains "$()" characters, append SQLCMD option "-x". Below is an example:

    SQLCMD -x -U <username> -P <password> -d <database_name> -i "<full_path to DSMData.sql>"

Step 3. Modify the dsm.properties file

When DSM is set to use an embedded Derby database, the contents of dsm.properties appear as follows:

database.name=dsm
database.directory=...\\Deep Security Manager
database.type=Embedded
mode.demo=false

Replace the values with entries required for operation with an MS-SQL database. Note that the syntax below is case sensitive. To update the entries:

  1. Create a backup of the dsm.properties file. This can be found in <install_path>\Trend Micro\Deep Security Manager\webclient\webapps\ROOT\WEB-INF\.
  2. Open the dsm.properties file in a text editor and then replace the following contents:

    database.name=<database_name>
    database.SqlServer.server=<hostname>
    database.SqlServer.user=<username>
    database.SqlServer.password=<password>
    database.SqlServer.namedPipe=true
    database.type=SqlServer
    mode.demo=false

    Below is a sample:

    database.name=dsm
    database.SqlServer.server=SQL_Server
    database.SqlServer.user=sa
    database.SqlServer.password=sa
    database.SqlServer.namedPipe=true
    database.type=SqlServer
    mode.demo=false

     
    If your MS SQL server is hosted on a separate machine, set the "database.SqlServer.namedPipe" parameter to "false". Doing so means that it uses TCP/IP instead of namedPipe.
  3. Save and close the dsm.properties file.
  4. Restart the Trend Micro Deep Security Master Manager service for the changes to take effect.

The following are indications that the changes are working:

  • You are able to log in to the DSM web console.
  • The value of the "database.SqlServer.password=" parameter in the dsm.properties file is encrypted. Below is an example:

    database.SqlServer.password=!CRYPT!20DE3D96312D6803A53C0D1C691FE6DEB7476104C0A

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

Thank you for your feedback!

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.

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.


Need More Help?

Create a technical support case if you need further support.