/
Audit Policies

Audit Policies

Owned by Andrew Madge
Last updated: Dec 03, 2024 by Svetlana Sheptiy

The primary function of the Snare Enterprise Agent for MSSQL is to monitor and filter events from MS SQL trace logs. This is accomplished via audit policies (formerly known as objectives). Audit Policies monitor a list of specified MS SQL events from selected databases and propagate the information according to the Destination Configuration.  Each of the audit policies provides a high level of control over which events are selected and reported. Events are selected from a group of high level requirements, and further refined using selected filters. Once an event has been collected, it may be included or excluded based upon the audit policies filter. All audit policies operate independently of each other, so what is included or excluded in one audit policy will have no effect on any other audit policy.

No default audit policies are defined when the Snare Enterprise Agent for MSSQL is installed, however once audit policies are configured, the audit policies are displayed in summary.


Starting SnareMSSQL v5.6.0, an 'Extended Event' audit policy can be added. Due to the addition of this feature, the 'trace' audit policy is deprecated as Microsoft has already deprecated trace; starting from SQL Server 2012. In SnareMSSQL, an Extended Event audit policy can be added for all the versions of the SQL Server starting from SQL Server 2012. It is strongly recommended to use 'Extended Event' as Microsoft can remove the trace functionality in any upcoming version of SQL Server.

To create a new audit policy click Add Extended Event, or to view/edit an existing audit policy select Modify and to delete the audit policy click Delete. To check the current members of any groups specified in the User Search Term click Check Groups. A list of all group and sub-group members will be displayed.

When configuring Extended Events to be sent to Snare Central, the Destination needs to be configured with SNARE v2 format. It is recommended to use Snare Central v8.4.0 or newer. 

Following input form will be loaded when 'Add Extended Event' is pressed. This form shows all the available Extended Events that can be captured for the SQL Server instance(s) installed on this machine. An extended event can be selected either or from all of the available options, i.e. Group Extended Events, Channel Extended Events and/or Category Extended Events. Filter search input box can be used to search for a specific Extended Event or to only show those Extended Events that matches the input filter. Channel and Category level Extended Events are fetched from the SQL Serer installed on this machine. See Appendix C for Group Level Extended Events.

Some of the 'Extended Events' are extremely frequent and generate lot of events. The selection of such frequent Extended Events is not recommended for production environment. These frequent Extended Events should only be used for troubleshooting and only for short period of time; as these extended events can cause performance issues if run for longer period of time. These frequent Extended Events are as following:

  • sqlserver.lock_acquired
  • sqlserver.lock_escalation
  • sqlserver.lock_released
  • sqlserver.lock_cancel
  • sqlserver.query_pre_execution_showplan
  • sqlserver.query_post_execution_showplan
  • sqlserver.inaccurate_cardinality_estimate
  • sqlserver.query_post_compilation_showplan
  • sqlsni.sni_allocate_id
  • sqlsni.sni_deallocate_id
  • sqlsni.sni_enter
  • sqlsni.sni_error
  • sqlsni.sni_leave
  • sqlsni.sni_packet
  • sqlsni.sni_packet_data
  • sqlsni.sni_trace
  • sqlsni.sni_update_id
  • sqlserver.transaction_log

If any of these frequent Extended Event is selected in an audit policy then a warning message will be shown on Audit Policy page; as shown in the screenshot in the beginning of this page.

For most customers the Group Extended events will be the primary event log collection used for forensic and audit compliance needs. The other Channel and Category Extended events are to help assist with debugging the SQL environment or collect additional performance data that are beyond normal forensic log data collection.  

The "depreciated" or legacy audit event collection method option can be added using the following trace method form. This will generate the legacy numerical audit event ids.

The following parameters of the audit policy may be set when creating an audit policy:

  • Make the audit policy Persistent. Select this checkbox to to create a system specific audit policy and to decrease the load during silent installation.   The audit policy is then stored separately in the registry under 'PersistentObj' registry key, and NOT exported using silent installation wizard (-x option).  NOTE: the audit policies are not deleted from the registry by selecting the Allow persistent audit policies during an uninstall.
  • Identify the MS SQL instance to be monitored. Specifies the MS SQL instance to be monitored. For local default installations of MSSQL, leave this field as "(local)". For named instances use the notation ".\InstanceName", where "InstanceName" is the name of the instance to be monitored. The default is (localhost) which includes all databases and instances on the system.
  • Identify the database to connect to. Specifies the database to initially connect to within the chosen instance. The default is the master database to connect to for all databases on the system.
  • Database authentication method. By default the agent will connect to MS SQL using the current service account credentials, this is the recommended approach as its more secure. If this is not desired, then selecting Specify username / Specify password will allow the administrator to choose which user SnareMSSQL connects to MS SQL with. This can be an Active Directory account or a built in SQL Server account. All accounts must be granted the SYSADMIN SQL Server role to allow access to the relevant trace audit functions for that SQL instance.
  • Specify username for authentication to the database. Each audit policy in a stand alone deployment scenario provides settings to customize its connection to the database. Specifying the username (and password) allows the administrator to input the details which SnareMSSQL will use when connecting to MS SQL. The chosen user must be granted the necessary rights to perform "SP_TRACE_CREATE" upon the chosen database. At a minimum, these rights include the "Alter Trace" permission. This is included in the SYSADMIN role SQL Server configuration by default. For more information on the required rights to perform "SP_TRACE_CREATE", consult your MS SQL Server documentation.
  • Specify password for authentication to the database. See above. If using this method it is recommended to use a strong complex password. 
  • Specify Database Filter. If "Include" is selected (the default), then only events relating to databases listed in the Database Filter Search Term will be monitored. If "Exclude" is selected, then only events relating to databases NOT listed in the "Database Filter Search Term" will be monitored.

The Database Filter Search Term allows the user to specifically identify which databases (comma separated) should be monitored. Search terms may not contain any wildcards, instead exact database names should be listed.  For example, to monitor events from the "Finance" and "Inventory" databases:
Database Filter Search Term: Finance,Inventory

  • Identify the high level event. Allows the administrator to choose one or more predefined sets of events, based upon the chosen group. These groups allow easy selection of some of the most common security audit policies. Details on which trace event IDs are used to generate the following audit policies can be found in Appendix C - Audit Policies and security event IDs. Events included:
  • Logon or Logoff
  • Account administration
  • Database Object Access
  • Query Tracking
  • Use of user rights. Select from:
  • Data Definition, e.g. CREATE, ALTER permissions
  • Privileges
  • Data Manipulation => e.g. INSERT, UPDATE permissions
  • Including SELECT (this option is available only when Data Manipulation is selected).
  • Others
  • Exception Reporting
  • Any events (allows finer control by specifying event IDs in the Event ID search term).

  • Event ID Search Term. Used with Any events to select the specific events to monitor. Each event contains a unique number known as the Event ID. It is this number which is used to define which events will be monitored. Multiple values are to be comma separated.

    ExamplesEvent ID search term
    To select an individual event to monitor, specify the Event ID, e.g. Select only the login event (Event ID 14)14
    To select a range of events to monitor, specify the first and last Event ID within square bracket, e.g. Select all events from 14 to 20 (inclusive)[14-20]
    To select all available events, use a star*
    Select only the login event (Event ID 14), the log off event (Event ID 15) and the failed login event (Event ID 20)14,15,20
    Select the events 14, 15 and 20, all the events from 80 to 90 (inclusive) and all the events from 100 to 200 (inclusive) 14,15,20,[80-90],[100-200]
    Events may also be removed from the list by prefixing a term with a minus, e.g. Select all events, except for 14 and 15*,-14,-15
    Search terms are read left to right. This causes the right-most terms to take precedence.Select all events from 1 to 19 (inclusive) and from 31 to 100 (inclusive)[1-100],-[20-30]
    Select all events from 1 to 100 (inclusive). Note that the first term, "-[20-30]", is overridden by the second term, "[1-100]".-[20-30], [1-100]

    If adding any of the following complete set of events in Event ID Search Term, it will enable the respective check box under Identity the High Level Event and Use of user rights:

    • 14,15,20 Logon or Logoff
    • 104,105,106,107,108,109,110,111 Account Administration
    • 128,129,130,131,134,135,152,153,164,173,175,176,177,178,180 Database Object Access
    • 40 Query Tracking
    • 118 Data Definition
    • 102,103,112,113,132,133,170,171,172 Privileges
    • 114 Data Manipulation
    • 33,67 Exception Reporting
    The terms for the high level event groups listed in Appendix C Audit Policies and security event IDs can also be used directly in the Event ID Search Term, e.g. Select all events from the Account Admin and Transaction high level groups

Event ID Search Term: [account-admin],[transaction]

For a complete breakdown of all available event IDs, see the Microsoft Developer Network documentation at http://msdn2.microsoft.com/en-us/library/ms186265.aspx

  • Client Identifying Data Scrubbing. Select for the agent to adjust any event messages enclosed by double quotes and single quotes with hashes (#) for that audit policy. This allows sensitive data to be hidden.

Example: This is a test event about user "admin" and table "security" with id of 'bruce'.

If the checkbox is ticked, then the above message will become the following before sent to a remote server:

This is a test event about user "#####" and table "########" with id of '#####.

Example: Latest event will hide the data in quotes and replace it with hashes as shown below:





  • Select the General Match Type. This determines how the General Search Term filter will be applied. If "Include" is selected (the default), then any event failing to match the search term is discarded by the agent. If "Exclude" is selected, then any event matching the search term is discarded by the agent.
  • General Search Term. This field allows the user to further refine a search based on the event record payload. Search terms may contain wild cards such as "*", which matches any number of characters, or "?", which matches any single character. Search terms are not case sensitive.

Example: Select all events which contain the text "SELECT"
General Search Term: *SELECT*

Multiple search terms can be specified by separating them with commas.
Example: Select all events which contain the text "SELECT" or the text "IsShutDown"
General Search Term: *SELECT,IsShutDown*

  • Select the User Match Type. This determines how the User Search Term filter will be applied. If "Include" is selected (the default), then any event failing to match the search term (eg username of miero) is discarded by the agent. If "Exclude" is selected, then any event matching the search term is discarded by the agent.  Each MSSQL event is associated with a user account and the following options facilitate to filter the events on the basis of user accounts:
    • Apply user filter directly to the trace  Events that will pass the filter will be either included or excluded in the trace file, this will reduce the size of the trace files so the system will generate less activity. 
    • Do NOT audit the Service Account  Apply the filter on the service account running the agent and do not audit the service account to reduce the audit log noise.
  • User Search Term. This field is a comma separated list of user names or Active Directory groups used to filter events from this audit policy. User-related search terms may contain wild cards such as:
    • * which matches any number of characters
    • ? which matches any single character
    • ^ the caret is used to exclude the username

    • Windows Active Directory or local group-related search terms need to be enclosed in square brackets [ ], curly/set brackets are for SQL Server { }.

      Examples

      Match all users
      User Search Term: *

      Match all user names containing either "smith" or "john"
      User Search Term: *smith*,*john*

      Match only the users "Paul", "John" and "Alice"
      User Search Term: Paul,John,Alice

      Group-related search terms need to be enclosed in square brackets and can optionally contain a flat or DNS domain name. If no domain is specified, the local machine's domain membership will be used. To enumerate the members of any AD groups, the service account credentials are used. Both user and group related search terms are not case sensitive. Notice the square brackets for AD groups.

      Match the "sqlaccess" AD group
      User Search Term: [sqlaccess]

      Match the AD group "sqlaccess" in the domain INTERSECT (flat name)
      User Search Term: [INTERSECT\sqlaccess]

      Match the AD group "sqlaccess" in the child domain ACCOUNTING (DNS name)
      User Search Term: [sqlaccess@accounting.intersect.local]

      Exclude the user accounts in the serveradmin AD group that start with svc_
      User Search Term: [serveradmin:^svc_*]

      Exclude the SQL Server accounts in the sysadmin SQL role
      User Search Term: {sysadmin:^*_*}
      Notice the curly/set brackets are for SQL Server.


      Example

      Exclude all service accounts from the audit logs starting with svc and a one way trust

      User Search Term: [sysadmin:^svc_*]

      The domain may be required to be added to ensure it is excluded after the caret, e.g.  [sysadmin:^mydomain\svc_*]

      The group details of the sysadmin role in SQL Server contained the following users:

      • sa
      • svc_sqlserver
      • mydomain\adminsqlgroup
      • altdom\adminsqlgroup

      A one way trust is in place from the altdom domain to the mydomain, i.e. the altdom domain does not trust the mydomain but the mydomain trusts the altdom domain.  In this case the altdom domain is not query able from the MSSQL Agent and will fail to determine the contents of the altdom\adminsqlgroup. The filter will be applied to all enumerated user accounts and an error displayed for any group that can not be enumerated. If your environment has accounts from other untrusted domains and you wish filtering to be applied to include or exclude them, then the accounts from the other domain will have to be explicitly defined in the local sysadmin SQL role so the agent can detect them and filtering can be applied correctly.

  • Select the Alert Level.  A criticality level may be assigned to enable the Snare user to designate audit events to their most pressing business security objectives, and to quickly identify the level of importance via the criticality options in the drop down list.  The Latest Events page will highlight the event in the selected Snare criticality color assigned to your audit policy. User can choose the criticality level depending on the destination the event is being sent. There are options to assign criticality for each destination based on the format Snare, Syslog, CEF or LEEF. Each of these criticalities is then assigned to the event. While sending to the destination, specific criticality is assigned to the final event string depending on the destination type ie Snare, Syslog, CEF or LEEF.
    • Snare - Critical, Priority, Warning, Information, Clear
    • Syslog - Emergency, Alert, Critical, Error, Warning, Notice, Info, Debug
    • CEF - 0 - 10, 0 is least severe and 10 is most severe.
    • LEEF - 1 - 10, 1 is least severe and 10 is most severe.

To save and set changes to these audit policy settings, and to ensure the MSSQL Agent has received the new configuration perform the following:

  1. Click on Change Configuration to save any changes.
  2. Click on the Apply Configuration & Restart Service menu item.


Service User Permissions

When an audit policy is created, SnareMSSQL tried to connect to specified SQL Server instance and tries to create a Trace or Extended Event Session as per the type of audit policy. The SnareMSSQL service account needs specific permissions to successfully create and then run an audit policy. The following table highlights these permissions.

EntityPermissionTrace / Extended Event
serverCONNECT SQLBoth
serverVIEW ANY DATABASEBoth
serverVIEW ANY DEFINITIONBoth
serverVIEW SERVER STATEBoth
serverALTER TRACETrace Audit Policy
serverALTER ANY EVENT SESSIONExtended Event Audit Policy

IMPORTANT

  • If User Search Term is set to Group-related search term(s) then SnareMSSQL service account may require additional Windows and SQL Server permissions. For example, if Group-related search term is set to Active Directory group  [serveradmin:^svc_*] or SQL Server user group {sysadmin:^*_*} then SnareMSSQL service account user must be at least serveradmin in AD or sysadmin in SQL Server respectively. This is due to the fact that SnareMSSQL needs to search the serveradmin users for AD and sysadmin users in SQL Server. If User Search Term is set to
  •  If User Search Term is set to '*' then SnareMSSQL will be able to capture the trace/extended events of all the users that have lower or equal privileges as of SnareMSSQL service account user. For example, let's say SnareMSSQL user is User1 and a logon event occurred for User2. If User2 has higher privileges than User1 then SnareMSSQL will not be able to capture the logon event for User2. This is due to security architecture of Windows AD and SQL Server that a user only can audit the other use with lesser or equal privileges. For example, a guest user cannot perform the actions that can be performed by a sysadmin user. The following diagram shows this process of checking permission when replaying traces/extended evet session:

Source: https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/permissions-required-to-run-sql-server-profiler?view=sql-server-2017