Centrify DirectAudit 3.x
How does one migrate database associated with a Centrify DirectAudit Management Console from one SQL database server to another?
Here is the step by step process to migrate Centrify DirectAudit Management database from one database server to another.
1. Don't make any major config change; for example, DB rotation, adding new auditors, etc.
2. Note the Outgoing Account that is currently defined for <YOUR_DA_ManagementDB_NAME>
- Right-click on <Installation_NAME> inside DirectManage Audit Manager and select 'Management Databases'
- Select <YOUR_DA_ManagementDB_NAME> and then click 'Properties' at the bottom
- On the properties page, go to the 'Advanced' tab and verify the stting for 'Account type'.
- If the Account type is 'Windows Authentication', it indicates the management database is currently using its machine account to talk to the Audit Stores and the authentication method is Windows auth. If the Account type is 'SQL Authentication', note down the 'Account name'.
3. Use simple SQL database backup/restore mechanism to move the DB from old database server to new database server
4. Once the DB is moved, set its owner to [sa] and reset the trustworthy flag to ON for that database:
ALTER AUTHORIZATION ON DATABASE::<YOUR_DA_ManagementDB_NAME> TO [sa]
ALTER DATABASE <YOUR_DA_ManagementDB_NAME> SET TRUSTWORTHY OFF
ALTER DATABASE <YOUR_DA_ManagementDB_NAME> SET TRUSTWORTHY ON
After the above two commands, run the following store procedure to verify <YOUR_DA_ManagementDB_NAME> is ready:
if the result pane shows a version number (19...), then <YOUR_DA_ManagementDB_NAME> is prepared correctly.
4. Make sure you have a SQL login for NT AUTHORITY\SYSTEM on the new SQL server and that login is a member of sysadmin server role
Now DirectAudit Management database on the new server/cluster is ready to be attached.
There are two ways to do this,
a. use Audit manager console, right click 'Installation' to select 'Management Database'
b. in the Management Database popup windiw, click 'Add' to add new server
c. checkbox 'Use an existing database'. Type new server/cluster name to 'Server name' then 'Database name'
d. when prompted for a scope, a dummy site can be provided.
e. once the new management database added successfully, remove (NOT delete) the old management database.
f. close and reopen Audit manager console
g. right click 'Installation' to select 'Properties' then click 'Synchronize'
a. use Microsoft SQL Server Management Studio, update 'DefaultManagementDatabase', table 'ManagementDatabase', column 'Server', to point to the new SQL server/cluster. This needs to be done on both original and new managementdatabase
b. close and reopen Audit manager console
c. right click 'Installation' to select 'Properties' then click 'Synchronize'
5. Since DirectAudit Manager Console also display Collector and Audit Store Database information, Audit Store DB needs to allow access from Management DB
a. in Audit manager console, expand Audit Stores > DefaultAuditStore > Databases
b. on the right panel, right click on active audit store DB to select 'Properties'
c. go to 'Advanced' tab in popup window, then add 'Allowed incoming Management Databases' with 'SQL Authentication. This is the lower panel input and the account is the 'outgoing' account in management database
d. this needs to be done on all attached audit store DB