Assumptions


  • Running SQL Server 2016 or higher

  • The Armor Agent is already installed and configured for Windows Logs

  • Working knowledge of SQL server administration

    • The user has access to the Security Log

    • User has privileged access to enable security logging

Procedure


  1. Following the Microsoft documentation, linked below, modify permissions to write SQL Server Audits to the Security log

    1. https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/write-sql-server-audit-events-to-the-security-log?view=sql-server-ver15

  2. Create a Server audit object, using the following steps

    1. In the left hand panel, right click on security, and select new → audit

    2. Set the audit name to be something identifiable for you

    3. Change the audit destination to be Security Log

    4. Leave the rest of the options as the default, and then press ok

    5. Right click on the new audit object and select Enable Audit

  3. Create a new Server audit specifications object using the following steps

    1. In the left hand panel, right click on security, and select new → Server audit specification

    2. Name the audit specification object something identifiable for you

    3. In the Audit field, select the audit that you just created

    4. In the Audit Action Type field, select the following options, each on a different line

      1. AUDIT_CHANGE_GROUP

      2. FAILED_LOGIN_GROUP

      3. SERVER_STATE_CHANGE_GROUP

      4. SERVER_OPERATION_GROUP

    5. Press OK to save the Server audit specifications

    6. Right click on the new Audit specification object and select Enable server audit specification

  4. For each database that will be monitored, create a new Database audit specification object, and apply the settings to it as follows

    1. In the left hand panel, navigate to the database that will be monitored, right click security, and select new → Database audit specification

    2. Name the audit specification object something identifiable for you

    3. In the Audit field, select the audit that was just created

    4. In the Audit Action Type field, select the following options, each on a different line

      1. DATABASE_OBJECT_ACCESS_GROUP

      2. DATABASE_ROLE_MEMBER_CHANGE_GROUP

      3. DATABASE_LOGOUT_GROUP

      4. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

      5. DATABASE_PERMISSION_CHANGE_GROUP

      6. DATABASE_PRINCIPLE_CHANGE_GROUP

      7. DATABASE_OBJECT_CHANGE_GROUP

      8. SCHEMA_OBJECT_CHANGE_GROUP

    5. Press OK to save the database audit specification

    6. Right click on the new audit specification object and select Enable database audit specificaiton


MSSQL Rules


RuleDefinition
ClassificationClassify SQL Database devices for ease of identification
Audit ChangedAlerts if the SQL audit object is changed or disabled
SQL Server StoppedAlerts if the SQL Server stops on the host
User Granted Access to DatabaseAlerts if a new user is granted access to a database
User Removed Access to DatabaseAlerts if a user is removed from a database
Truncate or DropAlerts if a truncate or drop action is performed
SQL Server Failed Login AttemptAlerts if there is a failed authentication attempt to a database