Configure Advanced SQL and Event Collector Settings for PMR in BI Integration

The below sections detail how to configure optional advanced SQL and event collector settings for your PMR in BI integration.

SQL Connection Options (Including SSL Configuration)

The SQL Connection Options field, available in the Privilege Management > Privilege Management Reporting Database Configuration form in BI, allows custom parameters to be appended to the SQL connection string. These can be used to configure functionality such as SSL encryption for the PMR database connection.

If the full connection string is provided in this field, these connection details are used instead of the Server and Database Name fields in the form.

By default the jTDS driver is used for connectivity to the PMR database. The jTDS connection string can be added to the SQL Connection Options field using the following format:

jdbc:jtds:<server_type>://<server>
[:<port>][/<database>]
[;<property>=<value>[;...]]

There are many optional parameters that can be appended to the jTDS connection string using the property=value; format. For example, to require that SSL is used for the connection using the jTDS driver, append the following to the jTDS connection string in the SQL Connection Options field:

ssl=require

For environments with external BI event collector worker nodes, if using SSL, we recommend using the Microsoft JDBC driver rather than the jTDS driver, because some issues have been found with the jTDS driver over external connections when using SSL.

To use the Microsoft driver, provide the connection string in the SQL Connection Options field in the following format:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value

 

Do not include the user and password custom parameters in the SQL connection string, because these are populated from the Report Reader SQL User and Report Reader SQL Password fields.

SQL Always On Availability Group Support

The PMR database supports running within a SQL Always On availability group. This prevents the CopyFromStaging scheduled job from running on the secondary replica in the availability group, so that it only ever runs on the primary replica.

To use BeyondTrust Reporting in an Always On availability group, you must use the Microsoft JDBC driver for the SQL connection. The default jTDS driver does not work with Always On.

To add the PMR database to an Always On availability group, the SQL recovery model for the database must be set to Full.

 

When using the full recovery model, ensure that best practice is followed for backing up the PMR database transaction log to prevent disk space from filling up. The regular execution of the CopyFromStaging job can cause the transaction log to quickly fill up disk space if the transaction log is not regularly backed up.

Install the PMR database on the primary replica server, and then add it to the availability group. It is then replicated to the secondary replica. There is no need to install the PMR database directly on the secondary replica server.

Additionally, when using the PMR database in an Always On availability group, use the SQL Agent job (PGInsertData) to run the CopyFromStaging stored procedure, not the Service Broker job. The Service Broker has been found to be unreliable restarting after failover. The Service Broker is currently the default job when installing the PMR database.

Follow the steps below to switch to using the SQL Agent job to run CopyFromStaging.

CopyFromStaging SQL Server Agent Configuration

To switch to the SQL Server Agent job after installing the PMR database, take the following steps:

1. Execute the Create_ER_Database_Agent.sql script against the PMR database on the primary replica. This removes the Service Broker queue and creates the SQL Server Agent job on the primary node.
2. Configure read-only access to the secondary replica of the Always On availability group by setting Readable secondary to Yes. This is required for the next step.
3. Execute the Create_ER_Database_Agent.sql script against the PMR database on the secondary replica.

Provided the script has been run on the primary replica first, it does not attempt to make any changes to the database on the secondary replica, as the removal of the Service Broker queue has already been replicated across from the primary to the secondary. Running this script only creates the SQL Server Agent job on the secondary replica. This job runs on the secondary but does not execute the CopyFromStaging stored procedure unless failover occurs, and this becomes the primary replica.

4. Remove the read-only access to the secondary replica (set Readable secondary to No).
5. In the Privilege Management Reporting Database Configuration form in BI, set the Server field to point to the PMR database in the Always On availability group, using the availability group listener address instead of the primary replica server address. The listener forwards any calls to the primary replica.

For more information on configuring read-only access to the secondary replica of the Always On availability group, please see Configure read-only access to a secondary replica of an Always On availability group.

Install and Configure External Event Collector Worker Nodes

1. Ensure the BI event collector worker node is installed and configured.
2. Ensure all steps detailed in the above sections for installing and configuring PMR in BI have been followed.
3. Verify that PMR is displaying reports in BI and that it is receiving events from an endpoint that is configured to point to the BI event collector on the BI management server. This is to verify that the end-to-end process is working and that events can flow from the endpoint to the BI event collector on the BI management server, then to the PMR event vollector, and finally to the PMR database.
4. Ensure the PMR database connection setting configured in the BI console is using the DNS hostname or IP address for the PMR database server, and not localhost or 127.0.0.1. Otherwise, the external event collectors are not able to communicate with the PMR database.
5. Run the BeyondInsight.EPM.EventCollector.Services MSI on each event collector worker node.

This must be installed in its default location for the PMR in BI integration to work.

6. Run the BeyondTrust EventCollector MSI on each external event collector worker node. The event collector service starts automatically as part of the upgrade.
7. Configure an endpoint to point to an external event collector node and raise events. Confirm they can be seen in the PMR reports.

For more information on BI Event Collectors, please see Configure U-Series Appliance.