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.
...
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.
| Info |
|---|
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 inut input form will be loaded when 'Add Extended Event' is presedpressed. 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 Extnded Extended Events are fetched from the SQL Serer installed on this machine. See Appendix C for Group Level Extended Events.
...
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 option to add the trace opens the following form" 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:
...
- 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.
Examples Event 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
...
- 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 { }.
Info title 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,AliceGroup-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.
Info title 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 queryable 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.
...
- 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:
