Permissions and requirements for SQL auditing

Applies to

ApexSQL Audit

Summary

This article provides information about SQL auditing application installation prerequisites including required permissions and software requirements.

ApexSQL Audit requires Windows administrator permissions for installation in order to properly setup all of its components and its access to the Central Repository Database. To ensure proper installation, it is recommended to initiate it by running the ApexSQLAudit.exe with the Run as admin command from the context menu

ApexSQL Audit GUI does not require any special permissions on the local machine where the central instance is running, as it communicates with the installed service which has received appropriate permissions during the installation.

The above-described permissions are required by the user during the installation or use. The following actual permissions are set and used by the product:

• For both central and distributed instances:
  • The account used for starting the ApexSQL Audit central and distributed processes must be a member of the sysadmin server role on the SQL Server instance audited
  • The processes require read and delete access to the SQL auditing session folder where trace files are being generated
  • The monitored SQL Server instance requires the write access to the SQL auditing session folder where trace files are being generated.
• Remote GUI:
  • Open port to the machine where Central instance is installed
  • Folder Read/write permissions where exported reports are saved
  • Windows account running the remote GUI must be added as ApexSQL Audit user in application options

Software requirements

Depending on the ApexSQL Audit component to be installed, the following software is required to be installed prior to installation:

• The central ApexSQL Audit instance
  • Microsoft SQL Server 2008 or greater
  • Microsoft .NET Framework 4.7.2
• A distributed ApexSQL Audit instance
  • Microsoft SQL Server 2005 or greater
  • Microsoft.NET Framework 4.7.2
• ApexSQL Audit remote GUI requires:
  • Microsoft .NET Framework 4.7.2

Hardware requirements

Both minimum and recommended hardware requirements for this SQL auditing software are the same as described in the Hardware and Software Requirements for Installing SQL Server. Additionally, we recommend using multi-core processors as ApexSQL Audit heavily relies on parallel job processes to provide fast processing of audited data.

There are additional communication and HDD space requirements:

• Both central and distributed ApexSQL Audit instances use port 64561 by default. This is user-configurable, however, the auxiliary port 60408, used for initialization of remote components, must also be opened from the central instance towards distributed instance
• HDD space:
  • ApexSQL Audit central instance requires 1GB of free HDD space for central repository database, while another 3-5GB is a recommended minimum for both central and remote instances in order to store temporary trace files (10MB each) generated by SQL Server or storing packages (10MB each) gathered on the central instance until they are parsed and removed by ApexSQL Audit instance. Note that both traces and stored packages are automatically deleted once processed:
    • Note: required space covers common situations when SQL Server generates a large number of trace files before they are handled or in case there is a delay in package transfer to the central instance. In case the ApexSQL Audit service is stopped by the administrator, SQL Server immediately stops creating trace files which prevents HDD overload
  • The central ApexSQL Audit instance requires 1GB for the central repository alone, and an additional 1GB at minimum per connected distributed instance for storing packages into a central instance local folder. These are deleted once the data in them is successfully stored in the central repository database; this operation is immediate and it’s delayed only in rare cases of overload when packages can accumulate until SQL Server is free to store them into the repository. This also covers the situation when quick maintenance of SQL Server which hosts a central repository database is required (e.g. restart)
    • Note that the central repository database will be automatically archived once it reaches 20GB (default setting). Additionally, all disk values above are recommended for regular/small scale auditing jobs, and auditing output should always be considered for each specific environment based on the auditing requirements, SQL Server traffic, and more

The information on how to account permissions influence the connectivity between components, and instructions on how to resolve them, can be found in the article Resolving SQL auditing connection issues