Audit trail implementation

You can use the audit trail feature to examine audit trail records in the PI AF database. Once enabled, audit trail records are created using SQL Server Agent jobs. You can view the audit trail with PI System Explorer.

Requirements for cloud database platforms

Audit trail is supported in the following cloud database platforms:

• Microsoft Azure SQL Database

• Microsoft Azure Managed Instance

• Amazon RDS for SQL Server

If using Azure SQL Database, assign the db_owner database role to the account used to access the PI AF SQL database.

If using Amazon RDS for SQL Server, EXECUTE permission on msdb.dbo.rds_cdc_enable_db and msdb.dbo.rds_cdc_disable_db is required to enable and disable audit trail.

Requirements for SQL Server

The audit trail feature uses SQL Server Change Data Capture (CDC) to generate an audit trail. CDC is supported in the following versions of SQL Server:

¹Development system only.

²Only supported in 2016 and later. For more details on CDC support, see the knowledge base article: PI AF error: Could not find stored procedure sys.sp_cdc_parse_captured_column_list.

The audit trail feature has the following additional requirements:

• The SQL Server Agent must be running before you enable the audit trail feature.

• You must be a member of the sysadmin role on the SQL Server that contains the PI AF SQL database.

Enable audit trail

The AF Diagnostics (AF Diag) utility is used to enable or disable the audit trail feature. The EnableAuditTrail (/AT) parameter is used to enable audit trail feature in SQL Server or one of the cloud managed database platforms.

Code example

afdiag /AT

Disable audit trail

To disable the audit trail feature and delete all audit trail records permanently, you must have sysadmin privileges on the SQL Server. To disable the audit trail feature, you use the AFDiag utility and the DeleteAuditTrail (/ATD) parameter. For example:

afdiag /ATD

Once you use this parameter, the audit trail is not recoverable.

Additional instructions for Amazon RDS

If you are using audit trail with Amazon RDS for SQL Server, you will also need to grant execute permissions on the msdb database before enabling or disabling audit trail.

Code example

Enable audit trail: GRANT EXECUTE ON PROCEDURE msdb.dbo.rds_cdc_enable_db TO user_name

Disable audit trail: GRANT EXECUTE ON PROCEDURE msdb.dbo.rds_cdc_disable_db TO user_name

Previous versions

The audit trail feature that was released in PI AF 2014 or later is supported by this installation and is upgraded when the PI AF SQL scripts are executed. At that time, the audit trail records are upgraded to the current format. If for some reason a failure occurs during the upgrade, you can use the AFDiag UpgradeAuditTrail (/ATU) parameter to fix the audit trail tables and records.

Versions of the audit trail feature prior to PI AF 2014 have a different format and are no longer updated with new change records after an upgrade of the PI AF SQL database. Existing tables and data remain intact, but new records cannot be added.

Audit trail support in SQL Server availability groups

Audit trail is supported in SQL Server availability groups. For instructions on how to enable audit trail on secondary machines in a SQL Server high availability environment, please see the installation topic Use PI AF Audit Trail in a SQL Server availability group.

Audit trail support in PI AF collectives

The audit trail feature is not supported on secondary members of PI AF collectives. If the audit trail feature is currently enabled on a server that you wish to add as a secondary server to a PI AF collective, you must run the AFDiag utility and disable the feature. Audit trail data is only stored on the primary member of a PI AF collective and is not replicated to any secondary member of a PI AF collective.

Note: When you are designing SQL Server backup procedures for your PI AF data, please be aware that if the primary member of a PI AF collective becomes unavailable, the audit trail data will also be unavailable. If the primary member of a PI AF collective cannot be recovered from a backup, the audit trail data cannot be recovered either.

Re-enabling audit trail

If you add an existing PI AF server with audit trail enabled to a collective as a secondary member, but later remove that server from the collective, you need to re-enable the audit trail feature on that particular server. Otherwise, auditing remains disabled.