In this article:

• Objective

• Prerequisites

• Steps to follow

• Validation and confirmation

• Tips

• Related topics and articles

Objective

This document will guide users through the process of migrating the ADAudit Plus Microsoft SQL database from an existing SQL Server instance to a new Microsoft SQL Server, ensuring data integrity, minimal downtime, and seamless continuity of auditing operations.

Prerequisites  

• Take a snapshot of both the source (current) and destination (new) SQL Server instances.

• Understand that the process involves restoring the database on the new server and reconfiguring ADAudit Plus to point to it using ChangeDB.bat.

• During restoration, you may be prompted for the Database Master Key (DMK) password if the Service Master Key (SMK) and DMK are not properly associated.

• Service Master Key (SMK): Automatically created during SQL Server installation the root key for encrypting other keys.

• Database Master Key (DMK): Specific to each database, encrypts other keys and certificates.

When the DMK is encrypted by the SMK, SQL Server handles decryption automatically. If not, manual intervention is needed.

Check encryption status:  

1. Check SMK encryption:

Open the SQL query execution tool connected to your SQL Server instance where the ADAudit Plus database resides. Enter the following executable, replacing YourDatabaseName with the name of your database:

USE <YourDatabaseName>;
SELECT * FROM sys.key_encryptions WHERE key_id = (SELECT key_id FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##');

2. Check DMK presence:

USE <YourDatabaseName>;
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

3. Check if DMK is encrypted by SMK:

SELECT name, is_master_key_encrypted_by_server FROM sys.databases WHERE name = DB_NAME();

• 1 = Encrypted by SMK (auto-decryption)

• 0 = Not encrypted (manual password required)

4. Re-encrypt DMK with SMK (if needed):

USE <YourDatabaseName>;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<YourMasterKeyPassword>';
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Validate:

SELECT name, is_master_key_encrypted_by_server FROM sys.databases WHERE name = DB_NAME();

Note 1: If you’ve changed the DMK password, update it in customer-config.xml (under <product_home>/conf).

Note 2: If the error message, The key is not encrypted with the specified decryptor appears, back up and restore the DMK from the old server.

Steps to follow  

Step 1: Back up and restore the DMK  

1. Back up the DMK on the old SQL Server:

USE <YourDatabaseName>;
BACKUP MASTER KEY TO FILE = 'C:\Backup\<YourDatabaseMasterKey>.key'
ENCRYPTION BY PASSWORD = 'YourMasterKeyPassword';

2. Move the backup file: Transfer the .key file to the new SQL Server machine.

3. Restore the DMK on the new SQL Server:

USE <YourDatabaseName>;
RESTORE MASTER KEY FROM FILE = 'C:\Backup\<YourDatabaseMasterKey>.key'
DECRYPTION BY PASSWORD = '<YourMasterKeyPassword>'
ENCRYPTION BY PASSWORD = '<YourMasterKeyPassword>' FORCE;

4. Encrypt the DMK with SMK:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

5. Verify DMK availability:

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

6. Open the DMK to test:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<YourMasterKeyPassword>';

If successful, proceed with ADAudit Plus reconfiguration.

Step 2: Reconfigure ADAudit Plus  

1. Stop the ADAudit Plus service.

2. Open the Command Prompt as the ADAudit Plus service account user.

3. Navigate to the ADAudit Plus installation directory and run <ADAudit Plus installation Directory>\bin\ChangeDB.bat.

4. In the DB Configuration Wizard:

• Select Microsoft SQL as the server type.

• Enter the Host Name, Instance Name, and Database Name.

• Select No if migrating from another Microsoft SQL database.

• Select Yes for SSL only if your SQL Server is SSL-enabled.

• Choose the Authentication Type:

• Windows Authentication: Use the current session credentials.

• SQL Server Authentication: Enter the username and password manually.

• Click Test Connection to verify the details.

• Click Save to initiate the migration.

Validation and confirmation  

• Confirm that the DMK is encrypted by the SMK by running:

SELECT name, is_master_key_encrypted_by_server FROM sys.databases WHERE name = DB_NAME();

• A result of 1 indicates successful encryption.

• Verify that ADAudit Plus connects and functions correctly post-migration.

Tips  

• Always maintain an updated backup of your SQL database and master keys before performing a migration.

• Store the DMK password securely and avoid frequent changes unless necessary.

• For Windows Authentication, ensure the service account has proper permissions on the new SQL Server instance.

• Use consistent folder paths and naming conventions for easier troubleshooting.

Related topics and articles  

• How to configure SQL authentication for ADAudit Plus