Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

KB-9440: Enable SQL database mirroring for DirectAudit database

Centrify DirectAudit ,  

8 November,17 at 09:43 PM

Question: If customer wants to enable SQL database mirroring for DirectAudit databases, what needs to be done?

Answer:
For how to set up SQL DB mirroring in general, please refer to this Microsoft link: https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/setting-up-database-mirroring-sql-server
Also, watch out for the followings in the setup of the SQL servers involved:
1. It is better to run the SQL server instances using an AD account, instead of default machine service account.
2. Make sure that TCP/IP is enabled for the SQL servers. Also, make sure that the IP address of the host is enabled.
3. Create an AD service account that can be used by database mirroring to login to all the SQL servers involved in the mirroring. 
4. By default, the recovery model of DirectAudit databases is SIMPLE. Please make sure to change it to FULL before setting up the mirror database.

Set up the DirectAudit Management database
===================================
Centrify recommends the following steps to set up and enable the DirectAudit Management database for database mirroring:
1. Create the Management database in the principal server.
2. Backup the Management database and restore it to the mirror server by following the steps recommended by Microsoft.
3. Enable database mirroring of the Management database.
4. Setup SQL connection string to allow automatic connection to mirror server:

a. Run SQL Server Management Studio (SSMS)
b. Select "New Query"
c. Select to use the Management Database as current database
d. Find out the Id for th
e current Management database, and update the column AdditonalConnectionParameters

        SELECT * FROM [dbo].[ManagementDatabase]
        UPDATE [dbo].[ManagementDatabase] 
             SET AdditionalConnectionParameters=N'Failover Partner=<mirror database server instance>'  
             WHERE [Id] = <ID_of_ManagementDatabase>

        where <mirror database server instance> is the name of the mirror database server instance
                   <ID_of_ManagementDatabase> is the value of the ID column for the Management database

Set up the DirectAudit AuditStore database
=================================
Centrify recommends the following steps to set up and enable the DirectAudit AuditStore database for mirroring:
1. Create the AuditStore database in the principal server.
2. Backup the AuditStore database in the principal server and restore it to the mirror server by following the steps recommended by Microsoft.
3. Enable database mirroring of the AuditStore database.
4. Setup SQL connection string to allow automatic connection to mirror server:

a. Run SSMS
b. Select "New Query"
c. Select to use the Management Database as current database
d. Find out the Id for the current AuditStore database, and update the column AdditonalConnectionParameters

 SELECT * FROM [dbo].[AuditStoreDatabase]
        UPDATE [dbo].[AuditStoreDatabase] 
             SET AdditionalConnectionParameters=N'Failover Partner=<mirror database server instance>'  
             WHERE [Id] = <ID_of_AuditStoreDatabase>

        where <mirror database server instance> is the name of the mirror database server instance
                   <ID_of_AuditStoreDatabase> is the value of the ID column for the AuditStore database


Publish new database connection information to Active Directory
=================================================
After you make the changes to the database connector strings in the previous setup steps, you need to publish such information to Active Directory so that they can be recognized by DirectAudit.
1. Start Audit Manager
2. Right click on the DirectAudit Installation
3. Select 'Properties'
4. Select 'Publication' panel
5. Click on 'Synchronize' button

Additional setup steps required in mirror database instance
=============================================
After the mirror databases are setup, you need to follow these steps:
1. Run SSMS
2. Enable CLR

a. Right click on mirror database SQL instance
b. Select 'Facets'
c. Select 'Server Configuration' under 'Facets'
d. Select 'ClrIntegrationEnabled', change it to 'True'
e. Click OK

3. Create login for collector accounts. 

a. Right click on 'Security'->'Logins' under the mirror database SQL instance
b. Select 'New Login...'
c. Enter the machine credential in 'Login name' (for example: mydomain\collector1$) for each collector.

4. Create login for all DirectAudit administrators and auditors. 

a. Right click on 'Security'->'Logins' under the mirror database SQL instance
b. Select 'New Login...'
c. Enter the Active Directory user name in 'Login name' for each DirectAudit administrator and auditor.

Additional steps after the first failover to mirror database
===========================================
The TRUSTWORTHY attribute and database owner are not preserved when the database is setup in the mirror instance. You need to do the followings in the mirror database:
1. Failover both the Management database and AuditStore database to the mirror SQL instance.
2. Run SSMS and connect to the mirror SQL instance.
3. Click on 'New Query'
4. Set default database to 'master'
5. Execute the following SQL statements:

ALTER AUTHORIZATION ON database::<Management_database> TO [sa]
ALTER AUTHORIZATION ON database::<AuditStore_database> TO [sa]
ALTER DATABASE <Management_database> SET TRUSTWORTHY ON
ALTER DATABASE <AuditStore_database> SET TRUSTWORTHY ON

where <Management_database> is the name of the Management database
and <AuditStore_database> is the name of the AuditStore database

6. Failover both databases back to original principal SQL instance.

Adding a new collector after mirroring is setup
===================================
To avoid any account creation issue when the failover partner becomes the principal server, Centrify recommends you to create an Active Directory group to represent all collectors, and make sure this Active Directory group has SQL logins to both primary and mirror SQL Server instances. You just need to add the new collector this Active Directory group.

If you choose not to use the Active Directory group workaround, Centrify recommends you to create a database login account for the new collector in both the principal and mirror SQL Server instances BEFORE configuring the new collector.

Adding a new DirectAudit administrator and/or auditor
=========================================
To avoid any account creation issue when the failover partner becomes the principal server, Centrify recommends you to create two Active Directory groups (one for administrators, and another for auditors), and make sure these two Active Directory groups has SQL logins to both primary and mirror SQL Server instances. You just need to add the new administrator/auditor to the appropriate Active Directory group(s).

If you choose not to use the Active Directory group workaround, Centrify recommends you to create a database login account for the new administrator/auditor in both the principal and mirror SQL Server instances BEFORE making the change in Auditor Manager.

Database rotation
==============
Since the set up for database mirroring can be tedious, Centrify recommends the customer to pre-create multiple AuditStore databases and set up mirroring for all such databases first. Once this is done, there is no need for additional setup using SSMS during database rotation.

Audit Manager cannot connect to DirectAudit installation
===========================================
Audit Manager is a Microsoft Management Console (MMC) plugin which caches connection setup information. If you use Audit Manager to access an installation before the new SQL connection string (that uses the failover partner information) is published, it is possible that you cannot connect to the DirectAudit installation after database failover. You can just right click on the installation name and click 'Close', and the connect again.

Audit Analyzer cannot connect to DirectAudit installation
===========================================
Audit Analyzer is a Microsoft Management Console (MMC) plugin which caches connection setup information. If you use Audit Analyzer to access an installation before the new SQL connection string (that uses the failover partner information) is published, it is possible that you cannot connect to the DirectAudit installation after database failover. You need to click on 'File' -> 'Options' -> 'Delete Files' to clear the cache, and connect again.

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.