PMR INSTALLATION GUIDE

What is PMR?

BeyondTrust Endpoint Privilege Management Reporting (PMR) enables organizations to monitor and report on activity from Windows and macOS desktops and servers.

This documentation is intended for reporting implementations with the Endpoint Privilege Management GPO platform.

Reporting is also available on other BeyondTrust platforms:

• Endpoint Privilege Management Cloud for Windows and Mac
• BeyondTrust Endpoint Privilege Management ePO Edition
• BeyondTrust BeyondInsight platform

How is it useful?

Reports provide visibility to the audit data and are implemented as custom reports in Microsoft SQL Server Reporting Services 2014 or later.

Microsoft SQL Server Reporting Services is typically hosted independently from the audit events SQL Server database instance, except for small implementations and evaluation scenarios where it may share the audit database server host.

Configuration options

There are two options for deploying the solution.

Option 1 - single box solution

Use a single box solution, which is suitable for evaluating the product, or for SME installations.

In this deployment scenario, one server provides all functions.

• The server must be running Windows Server 2016 or later.
• SQL Server 2017 or later must be installed on the server.

For an evaluation:

• A Windows Client, such as Windows 10, is supported.
• SQL Server Express is supported.

Select the Reporting Services feature in the feature selections page of the Microsoft SQL Server installer. To install Reporting Services, use Native Mode.

Option 2 - enterprise scaled out deployment

Use a scaled out deployment, which is recommended for larger production environments.

In this deployment scenario the Event Collectors, Database and Reporting Server are installed on dedicated servers.

SQL Server database

The database is a repository for the data collected from the clients.

• The minimum version required is SQL Server 2017.
• Clustered databases are supported.
• When you install SQL Server, you must select a case insensitive collation. We recommend you select Latin1_General_CI_AS.
• EPM installations require Azure SQL Server which is also supported.
• Windows Integrated Authentication must be used for Event Parser connections.
• SSRS connections can use either Windows Integrated Authentication or SQL Server Authentication.
• TCP/IP connections must be enabled on the SQL Server to allow the Event Collector service to submit events.
• Microsoft SQL Server CE is not supported.

The database is created during the installation of the Endpoint Privilege Management Reporting database component. By default, the database is named BeyondTrustReporting. The installation provides the option to provide a custom database name.

Support for SQL Always On availability group

The Endpoint Privilege Management Reporting database is updated to allow it to run within a SQL Always On availability group. This update 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 add the database to an Always On availability group, the SQL recovery model for the database needs to be set to Full.

❗️

When using Full recovery model, ensure that best practice is followed for backing up the Endpoint Privilege Management Reporting 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 being backed up.

Install the Endpoint Privilege Management Reporting database on the primary replica server, then add to the availability group, where it is then replicated to the secondary replica. There is no need to install the Endpoint Privilege Management Reporting database directly on the secondary replica server.

Additionally, when using the Endpoint Privilege Management Reporting 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 starting up again after failover. The Service Broker is currently the default job when installing the Endpoint Privilege Management Reporting database.

To switch to the SQL Agent job go through the following steps after installing the Endpoint Privilege Management Reporting database:

1. Execute the Create_ER_Database_Agent.sql script (located in the SQL folder of the PrivilegeManagementReporting zip file) against the Endpoint Privilege Management Reporting database on the primary replica. This will remove the Service Broker job and create the SQL 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 step 3. See the following link for details: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server
3. Execute the Create_ER_Database_Agent.sql script against the Endpoint Privilege Management Reporting database on the secondary replica.
   Provided the Create_ER_Database_Agent.sql script is run on the primary replica first, it won’t attempt to make any changes to the database on the secondary replica. The removal of the Service Broker job will have been replicated across from the primary to the secondary replica, so this part of the script won’t run.
   Running this script only creates the SQL agent job on the secondary replica. This job will run on the secondary but will not execute the CopyFromStaging stored procedure unless failover occurs, and this becomes the primary replica.
4. You can now remove read-only access to the secondary replica (set Readable secondary to No).

When configuring BeyondTrust Reporting to point to the Endpoint Privilege Management Reporting database in the Always On availability group, you must use the availability group listener address instead of the primary replica server address. The listener forwards any calls to 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 will not work with Always On.

Event Collector (Server)

The Endpoint Privilege Management Event Parser is a service that detects and submits new Endpoint Privilege Management events to the database. Typically, the Event Parser is installed on a dedicated Windows Event Collector Server, and by default scans the ForwardedEvents Log for new events.

The Event Parser service can be configured to scan the Application Event Log if required, by editing the following Registry value:

HKEY_LOCAL_MACHINE\\Software\\Avecto\\Privilege Guard Event Parser  
REG_SZ "EventLog"

The Event Collector host should be built on Windows Server 2016 or later.

Ideally, the server is dedicated to this role.

You may configure multiple Event Collector servers that feed into a single database.

The Event Parser services are the only components which establish direct connections to the events database. This keeps the number of concurrent connections to a minimum.

Report Server

• SQL Server Reporting Services (SSRS) 2017 or later is required.
• The server must be dedicated to this role.
• The events database and SSRS can be hosted on the same SQL Server instance.
• We recommend that the SSRS instance be separate from the database instance to prevent performance issues on the database.

The SSRS reports are installed and preconfigured during the installation of the Endpoint Privilege Management Reporting Pack component. By default, the SSRS instance is named ReportServer. You can provide a custom name during the SQL Server installation.

Client configuration

Windows Event Forwarding is the technology used to gather events from the clients running Endpoint Privilege Management Reporting.

• Event forwarding must be configured for all computers running the Endpoint Privilege Management Reporting Client that need to forward events.
• The minimum OS level required on each client is Windows 10.
• Events can be forwarded to any of the supported Windows Server OS versions (Windows Server 2016 or later).
• Each client requires Windows Remote Management (WRM) 1.1 or later installed.

For more information for installation and configuration details on Windows Event Forwarding, see the Event Centralization Guide.

Install the database

• Install the Endpoint Privilege Management Reporting Database before the Event Parser. As part of the install, set the database connection details, and the installer creates the Endpoint Privilege Management database if it doesn’t already exist.
• The Endpoint Privilege Management Reporting Database installer creates a database and database permissions through embedded SQL scripts. If your database administration team does not allow creation of databases or database permissions by installers, contact BeyondTrust Technical Support for assistance with an alternative approach.

Preinstallation tasks

Accounts

We recommend creating the following accounts before starting the installation.

Accounts required for installation

If you are using a single server, as in Deployment Option 1, then you may want to run the Endpoint Privilege Management Event Parser services as the SYSTEM account. In this scenario, you can use the Database installer to configure database access for the SYSTEM account.

If Windows Authentication is selected for the SQL connection, then the account of the installing user MUST have Alter Any Login and Create Any Database permissions on the SQL Server instance for the Reporting Services instance User to be created. If you receive an error 15247, verify these permissions are granted.

Prerequisites

To install the Privilege Management Reporting database, the MS OLE DB v19 SQL Database Driver must be installed. The driver has a dependency on both the X86 and X64 versions of the Microsoft Visual C++ 2015-2022 Redistributable v14.34 (and later). Both components must also be installed.

The Privilege Management Reporting database EXE installer checks if the correct versions of the MS OLE DB driver and VC++ redistributable are already installed. If not, the components are automatically installed by the Privilege Management Policy Editor EXE installer.

If using the MSI to install the Privilege Management Reporting database, the prerequisites need to be manually installed if they don’t already exist on the machine where the installer is being run.

Visit the following websites to install these components separately.

• Download Microsoft OLE DB Driver for SQL Server
• Microsoft Visual C++ Redistributable latest supported downloads

The installation of the Microsoft Visual C++ Redistributables can require a reboot. Plan the installation accordingly.

• If using the Privilege Management Reporting database EXE to install these components: If a reboot is required, there will be one request to reboot at the end of the installation.
• If installing the X86 and X64 versions of the Microsoft Visual C++ Redistributables separately: If a reboot is required, there may be a separate reboot request at the end of each of the installations.

Install procedure

To install the Endpoint Privilege Management Reporting database, run the installation package with an account that has Database Creator privileges.

1. Run the installation package and click Next . The License Agreement dialog box is displayed.
2. After reading the license agreement, select I accept the terms in the license agreement and click Next . The Database server dialog box displays.
3. Enter the name of the database catalog for Endpoint Privilege Management audit data. You can choose to use the current Windows user for the Database Creator user or enter credentials for a SQL account. Click Next .
   We recommend you leave caching enabled. For more information, see Manage the Endpoint Privilege Management database cache.
4. The Configure Report Data Caching dialog box displays. Report data caching is on by default. Click Next.
5. Select Privilege Management Reporting for BeyondInsight installation only if you are integrating with BeyondInsight. Database user accounts required for the integration are created with SQL Server authentication. Click Next. The Configure Event Parser Database User dialog box displays.
6. You must configure an Event Parser user to ensure the appropriate permissions are added for the database. You can choose to use the current Windows user for the Event Parser user or create a SQL Server account. Click Next to continue.
7. The Configure Reporting Services Database User dialog box displays. You must configure a Report Reader user to ensure the appropriate permissions are added for the database. You can choose to use the current Windows user for the Report Reader user or create a SQL account. Click Next to continue.
8. The Configure Data Admin Database User dialog box displays. You must configure a Data Admin user to ensure the appropriate permissions are added for the database. You can choose to use the current Windows user for the Data Admin user or create a SQL account. Click Next .
9. The Ready to Install the Program dialog box displays. Click Install, and then click Finish.

Install the event parser

Preinstallation tasks

Before starting the Event Parser installation, we recommend that the following accounts be created. The installation steps in subsequent sections of this guide refer to these accounts.

If you are using a single server, as in Deployment Option 1, then you may want to run the Endpoint Privilege Management Event Collector service as the SYSTEM account. In this case, you can specify the SYSTEM account as part of the installation.

The SQL Server configuration must have TCP/IP communications enabled to allow the Event Parser Service to submit events to the database.

Accounts required for installation

Accounts required for runtime

Prerequisites

To install the Privilege Management Event Parser, the MS OLE DB v19 SQL Database Driver must be installed. The driver has a dependency on both the X86 and X64 versions of the Microsoft Visual C++ 2015-2022 Redistributable v14.34 (and later). Both components must also be installed.

The Privilege Management Event Parser EXE installer checks if the correct versions of the MS OLE DB driver and VC++ redistributable are already installed. If not, the components are automatically installed by the Privilege Management Policy Editor EXE installer.

If using the MSI to install the Privilege Management Event Parser, the prerequisites need to be manually installed if they don’t already exist on the machine where the installer is being run.

Visit the following websites to install these components separately.

• Download Microsoft OLE DB Driver for SQL Server
• Microsoft Visual C++ Redistributable latest supported downloads

Install procedure

The installation of the Microsoft Visual C++ Redistributables can require a reboot. Plan the installation accordingly.

• If using the Privilege Management Event Parser EXE to install these components: If a reboot is required, there will be one request to reboot at the end of the installation.
• If installing the X86 and X64 versions of the Microsoft Visual C++ Redistributables separately: If a reboot is required, there may be a separate reboot request at the end of each of the installations.

To install Endpoint Privilege Management Event Parser, run the installation package with an account that has Installer privileges:

Systems must be 64-bit. Run PrivilegeManagementEventParser_x64.exe

1. Run the installation package.
2. Click Next . The License Agreement dialog box displays.
3. After reading the license agreement, select I accept the terms in the license agreement and click Next . The Destination Folder dialog box displays.
4. To change the default installation directory click Change and select a different installation directory.
5. Click Next . The Database Server dialog box displays.
6. Enter the details of the database server.
7. Click Next . The Event Parser Service dialog box displays.
8. Select the EventParser account for the Event Parser Service. Click the Browse button to select the account if desired.
   This account is added to the Event Log Readers group on the Event Collector server. It is also granted the Log on as a service right on the Event Collector server.
9. Click Next . The Ready to Install the Program dialog box displays.
10. Click Install to complete the installation. The Install Shield Wizard completed dialog box displays.

Install the reporting pack

Install the Reporting Pack on the SQL Server Reporting Services instance (or the single server if using a single box solution).

Preinstallation tasks

Before starting the installation of the Reporting Pack components, we recommend that the following accounts be created. The installation steps in subsequent sections of this guide refer to these accounts.

Accounts required for installation

Create the ReportWriter account

To add a System Administrator role to the Reporting Services site:

1. Browse to the SQL Server Reporting Services Report Manager URL. The URL is located in the Reporting Services Configuration Manager, under Report Manager URL.
2. Click on Site Settings, and then select Security.
3. Click Add group or user, and enter the DOMAIN\Username of an authorized account.
4. Check the System Administrator box.
5. Click OK.

Run the reporting pack installer

To install the Endpoint Privilege Management Reporting Pack:

1. Run the PrivilegeManagementReportingPack.exe installation package as a user with the Report Writer permissions.
2. Click Next . The License Agreement dialog box displays.
3. After reading the license agreement, select I accept the terms in the license agreement and click Next .
4. Enter your name and the name of your organization and click Next . The Database Server dialog box displays.
5. Enter the report server URL (the reports fail to upload if you enter an incorrect URL). Enter the database to use by the SQL Server Reporting Services instance.
6. If you are unsure of the correct Report Server URL to use, you can find it in the Reporting Services Configuration Manager under Web Service URL.
7. Click Next . The Reporting Services Authentication dialog box displays.
8. Enter the ReportReader account as the account used to connect to the data source.
   The ReportReader account is used by the Reporting Services to connect to the database instance when generating dashboards and reports. The account must be the same account that was entered during the Endpoint Privilege Management Database Installer.
   If Credentials stored locally in the report server is not selected, then any users authorized to access Endpoint Privilege Management Reporting must have their account credentials added to the SsrsRole database role.
9. Click Next . The Reporting Services Admin Authentication dialog box displays.
   This feature is optional and may not be desirable in environments that need tight control over purging of audit data. The purpose of this report is to allow the purging (and subsequent exclusion) of applications from populating the database with unwanted data.
10. Use the DataAdmin account for this purpose.
11. Click Next . The Ready to Install the Program dialog box displays.
12. Click Install to complete the installation.

Configure security on the report server

SQL Server reporting services

If you choose credentials supplied by the user running the report or Windows Integrated Security for Reporting Services Authentication, then each user or group of users who are permitted to view reports must be granted Browse permissions in SQL Server Reporting Services (SSRS).

1. Browse to the SSRS Report Manager, using the ReportWriter account (you can locate the correct URL in the Reporting Services Configuration Manager, under Report Manager URL).
2. Click Manage Folder to view the security of the top level, and then click Add group or user to grant access to a user or group.
3. Enter a group or user name, select the Browser role, and click OK.
4. If the Data Administration reports were installed, security on the subfolder must restrict access to the SSRS System administrator and users authorized to purge data.

View dashboards and reports

View dashboards and reports after all components are installed, and the security is configured. The starting point for the reports is ErpSummary located in the BeyondTrust Endpoint Privilege Management Reports folder.

To find the correct URL, go to Reporting Services Configuration Manager > Web Service URL as shown here. By default, the web service URL is https:///ReportServer.

After you navigate to the URL, click the BeyondTrust Endpoint Privilege Management Web directory, and then click the ERP summary report.

After you navigate to the ERP Summary report, save the address to your browser’s Favorites list.

Dashboard reports

Reports are available from the root directory if selected during the Reporting Pack installation:

https:///ReportServer/

1. Navigate to the root directory in the internet browser address bar and click BeyondTrust Endpoint Privilege Management.
2. Click Admin, and then click ErpEventsAdmin.
3. The Database Administration dashboard displays.

There are summary reports available for key items common throughout the dashboards. Click the Information logo (i) to view a summary report.

• Application Summary Report: Detailed statistical overview of a unique application.
• Event Summary Report: Detailed event log style summary of an event instance.
• User Summary Report: Detailed statistical overview of a user account.
• Host Summary Report: Detailed statistical overview of a host computer.
• Workstyle Summary Report: Detailed statistical overview of an Endpoint Privilege Management Workstyle.

You can drill down to more details on many charts and tables in the dashboards.

Upgrade

This guide assumes there is a working installation of Privilege Management Reporting v21.2 or later installed.

If your version is earlier than v21.2, we recommend the following:

1. Run the v21.2 database installer to update the install to a supported version. Keep in mind that the 21.2 installer has a dependency on the Microsoft SQL Server Native Client.
2. Run the latest database installer to upgrade to the latest version. For more information on requirements for the latest database installer, see Prerequisites.

The installers for the Endpoint Privilege Management Database and the Event Parser must be used to manage the upgrade for on-premises databases.

To upgrade the Endpoint Privilege Management database and event parser:

1. Stop the BeyondTrust Endpoint Privilege Management Event Parser service. Check that all events are finished processing.
   Query the following tables first to check that they are empty:
   • dbo.Staging
   • dbo.Staging_ServiceStart
   • dbo.Staging_ServiceStop
   • dbo.Staging_UserLogon
     Subsequently, query the following tables:
   • dbo.StagingTemp
   • dbo.StagingTemp_ServiceStart
   • dbo.StagingTemp_ServiceStop
   • dbo.StagingTemp_UserLogon
     All remaining events are processed after the tables are empty.
2. Stop the CopyFromStaging step from running by either disabling the SQL Agent Job or the Service Broker Queue depending on which mechanism is being used.

Verify CopyFromStaging is running

To check if the CopyFromStaging process is running, execute the following query:

SELECT BitValue, StringValue FROM Config WHERE ConfigId = 'CopyFromStagingLocked'

The installer pauses on the upgrade script if the process is running.

Right-click the SQL Server Agent Job to Disable it.

Disable SQL Agent Job

To disable a SQL Agent Job, right-click the job and select Disable from the expanded menu.

Right-click the System Queue to Disable Queue.

Disable Service Broker Queue

To disable the Service Broker Queue, right-click the queue and select Disable Queue from the expanded menu.

1. Stop the SQL Server service.
2. Uninstall the Endpoint Privilege Management Reporting Pack.
3. Restart the SQL Server service.
4. Load SQL Server Reporting Configuration Manager and connect to the database. Navigate to the Reporting link and use the dropdown to delete the top level folder.
5. Run the Endpoint Privilege Management Database installer to upgrade the database. Ensure you point the installer to the existing Database server and Endpoint Privilege Management database name when prompted.

If you installed Endpoint Privilege Management Reporting from version 5.1 or later, the default name for the database is BeyondTrustReporting. If you installed a previous version, the default name is AvectoPrivilegeGuard. Alternatively, you may have chosen a different database name.

If you see an error message that states "Please stop CopyFromStaging from running before upgrading the database", then ensure no new events are processing by querying the above tables and try again.

6. Run the Endpoint Privilege Management Reporting pack to upgrade the reports. Ensure you point the installer to the existing Database server and Endpoint Privilege Management database name when prompted.
7. Upgrade the BeyondTrust Endpoint Privilege Management Event Parser. Ensure you point the installer to the existing Database server and Endpoint Privilege Management database name when prompted.
   This upgrade path can be applied to both standalone Endpoint Privilege Management configurations and to configurations deployed to multiple machines.

When you install Endpoint Privilege Management Reporting, the Reporting Pack, the Database, and Event Parser installers should be the same version. However, you can use a different version of the Endpoint Privilege Management client and the EPM with Endpoint Privilege Management Reporting. The Endpoint Privilege Management client generates the data that populates the reporting database. If any new features are added to the reporting pack, the pack is only populated if the Endpoint Privilege Management client is on a version that supports the data generation.

Manual upgrade

To upgrade an Endpoint Privilege Management database using SQL scripts:

1. The SQL scripts are provided as part of the Endpoint Privilege Management installers, located in the Endpoint Privilege Management Reporting release folder, which can be found in the BeyondTrust portal. Alternatively, you can contact BeyondTrust Technical Support.
   There is a README file provided in this directory to assist you.
2. Run the following SQL query to return the version of the database.

   select \* from DatabaseVersion
   

3. Execute the upgrade script where the name is the next version number and carry on applying these until the desired version is reached.
   For example, if your current database version is 4.3.16 and you want to upgrade to version 5.0.0, run the following scripts in order:
   Script_4.5.0_Updates.sql
   Script_5.0.0_Updates.sql
   Check the SQL log for any errors and contact BeyondTrust Technical Support if necessary.
4. Run and execute the following SQL query against the reporting database to return the versions in the InstallShield table:

   SELECT \_ FROM [dbo].[InstallShield]
   

5. Open the InstallShield query file. This is available in the SQL folder and is an Endpoint Privilege Management Reporting artifact.
6. Copy the relevant INSERT lines from this query file that are not included in the database table.
   For example, if the upgrade is from 5.1.1 to 5.4, you need to copy these lines:

   INSERT [dbo].[InstallShield] \([ISSchema]) VALUES (N'5.3.0          ')  
   INSERT [dbo].[InstallShield] \([ISSchema]) VALUES (N'5.4.0          ')
   

7. Copy these into a query against the Reporting Database and execute it.
8. View the InstallShield table by running the query below. These values are added.
   TextText

   SELECT * FROM [dbo].[InstallShield]
   

Event parser

Event parser SQL connection

The connection between the event parser and the database is established using the MS OLE DB SQL Database Driver.

• The connection is secured using Windows Authentication.
• The event parser runs as a Windows service using user credentials that have access to insert data to the Endpoint Privilege Management Reporting database.
• The connection is established when the first event is processed, and remains open thereafter. If the connection breaks while executing commands, the parser tries to recreate the connection. Data will not be lost due to an occasional loss of connection.

Data transmission

The Event Parser service processes audit events in the shortest time possible, using a batching approach.

The number of events processed in each batch is not configurable in the current release.

The Event Parser subscribes to the event log and is notified of new events.

When the Event Parser is notified new data is available, all events available are processed in batches of 100.

Audit data is inserted to the Endpoint Privilege Management Reporting database using bulk SQL insert to optimize performance.

The Endpoint Privilege Management Reporting SQL database is designed to eliminate duplicate audit data, so there is no need to roll back partial failures; transactional inserts are not used.

If the data insert fails, the Event Parser continues to retry; it does not skip over events.

For example, if the Event Parser Service’s account password expires, the Event Parser fails to establish or reconnect to the database and gets stuck, retrying the same insert until the condition is rectified. This is by design, to ensure no data is lost.

If the failure persists for an extended period, the Windows Event Log may begin to roll over, causing the oldest audit events to be removed. Be sure to maximize the event log size, and monitor growth rate to ensure audit data is retained as long as necessary.

Monitor and recovery

To diagnose failures in the Event Parser service look in the Windows Application event log on the Windows Event Collector host.

The Event Parser service raises events if errors occur, such as failure to connect to the database. These events typically contain information required to diagnose the problem. If this is insufficient, debug logging can be enabled. The debugging logs are designed for advanced diagnostics by BeyondTrust staff.

Please open a support case.

Reprocess data

If data needs to be reprocessed (for example, the database is deleted and recreated), the Event Parser can reparse the entire event log. This is always safe to do, as the database is fully resilient to duplicate data being added; duplicate data is discarded.

Be aware that reprocessing all the events creates a lot of database activity in a short period of time. It is best to plan this during periods of low activity in your environment.

To do this:

1. Stop the Endpoint Privilege Management Event Parser service.
2. Delete the registry key:

   1. HKEY_USERS\<Event Parser User SID>\\Software\\Avecto\\Privilege Guard Event Parser
   

   Start the Endpoint Privilege Management Event Parser service.