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 Endpoint Privilege Management > Endpoint 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.

• You must use the Microsoft JDBC driver for the SQL connection. The default jTDS driver does not work with SQL Always On.
• The SQL recovery model for the database must be set to Full.

 

When using the full recovery model, ensure that best practice is followed to back up the PMR database transaction log. Frequently running CopyFromStaging causes the transaction log to quickly use up disk space.

• Install the PMR database on the primary replica server, and then add the database to the availability group. The database is then replicated to the secondary replica.
• Use the SQL Agent job (PGInsertData) to run the CopyFromStaging stored procedure, not the Service Broker job. The Service Broker can be unreliable restarting after failover. The Service Broker is currently the default job when installing the PMR database.
• Users are only created on the primary replica. You must create users on the secondary replica and synchronize the SIDs between the replicas. In a failover scenario, PMR loses the connection to the database if the accounts are not created on the secondary replica.

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 Endpoint 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, 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, see Configure U-Series Appliance.