This article describes how to migrate a DualShield server with Microsoft-SQL to a DualShield server with MySQL.
1 - Install a new DualShield authenticatiron server with MySQL as the database engine. Make sure that the FQDN of the new DualShield server is identical to the FQDN of the old DualShield server.
2 - On the new server, Install MySQL Workbench
3 - On the new server, create an ODBC Data Source for Microsoft-SQL
Before running MySQL Migration Wizard, we need to create an ODBC data source for MS-SQL
On the target server, launch the ODBC Data Source Administrator
Click Add
Select "SQL Server"
Click Finish
Enter a Name, Description
Enter the address of the source Microsoft SQL server
Click Finish
Click Test Data Source...
4 - Stop the DualShield service on both the old and new servers
5 - Migrate database from the old MS-server to the new MySQL using the MySQL Migration Wizard
In MySQL Workbench, select Database | Migration Wizard
Click Start Migration
In Database System, select "Microsoft SQL Server"
In Connection Methods, select "ODBC Data Source"
Click "Test Connection"
Click Next
Click "Test Connection"
Click Next
Click Next
Select the "dualshield" schema
Click Next
Click Next
Click Next
Click Next
Click Next
Enable "Create s SQL script file"
Click Next
Click "Yes"
Click Next
We need to make some changes to the schema
Select the "alert" table
Change "VARCHAR(0)" to "LONGTEXT", in the advance_options and message columns
Click the Apply button
Click Next
Click Next
This process might take a while depending on the size of the database
6 - Fix issues after the database migration
Fix 'Alert' table schema
The following 2 fields in the alert table should have been fixed in the process of the migration wizard.
If they were not fixed, due to mistakes in operation, then run the following SQL commands to fix them
alter table alert modify column message longtext; alter table alert modify column advanced_options longtext;
Below is the corrected scheme of the alert table
Fix 'DatabaseChangeLog' table
Run the SQL command below to fix an issue in the 'databasechangelog' table
update databasechangelog set md5sum=null;
Fix auto increment in all tables
MySQL Workbench has a bug
https://bugs.mysql.com/bug.php?id=96231
that causes auto increment to be missing on all tables
To fix it, run the SQL commands below
SET FOREIGN_KEY_CHECKS = 0; alter table `activation_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `agent` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `alert` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `application` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `application_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `attribute_definition` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `authentication_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `authorization_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `certificate_activation_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `certificate_authority` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `certificate_request` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `certificate_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `challenge_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `configuration` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `connection_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `customization_config` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `customization_template` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `data_cache` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `database_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `deepnet_authenticator` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `dna_auth_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `domain` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `dualshield_system` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `enum_value` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `fail_thru_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `history` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `i18n_message` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `identity_source` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `image` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `image_repository` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `internal_user_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `ip2location_db3` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `ip2location_db3_ipv6` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `jvmobservation` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `ldap_server_connection` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `license_group` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `log` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `log_field` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `log_filter` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `logon_procedure` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `logon_step` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `message` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `message_body` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `message_gateway` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `message_template` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `message_template_item` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `network` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `policy_category` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `product` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `product_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `push_notification_gateway` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `qna` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `question` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `radius_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `radius_client` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `radius_profile` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `radius_proxy` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `radius_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `realm` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `report` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `report_data` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `report_result` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `repository` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `role` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `serial_generator` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `server_certificate` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `server_job` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `site_stamp` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `spattribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `sso_federation` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `sso_identity_provider` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `sso_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `sso_service_provider` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `store` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `store_folder` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `store_item` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `system_binary_data` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `system_job` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `systicket` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `task` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `tbl_custom_application` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `tbl_policy` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `tbl_user` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `temp_pass` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `token` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `token_assignment` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `token_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `token_auth_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `token_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `unit` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `user_certificate` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `user_configuration` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `user_device` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `user_registration_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `user_secure_cookie` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; alter table `usergroup` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT; SET FOREIGN_KEY_CHECKS = 1;
Copy the CA certificate
copy the CA certificate file from the old DualShield server to the new server. It is located at:
C:\Program Files\Deepnet DualShield\jre\lib\security\cacerts
Copy the database encrypt key files
copy the database encryption key files from the old DualShield server to the new server. They are located at:
C:\Program Files\Deepnet DualShield\config\dbenc.jks
C:\Program Files\Deepnet DualShield\config\storeconf.xml