Microsoft SQL Database Audit Logs

You can track database administrative activity via Microsoft SQL Server for log search and custom alerts on Windows machines.

Before You Begin

In order to collect database audit logs, you must enable auditing of the SQL server logs. You can read more about auditing a database here: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine.

Use an account that has access to the Windows Security or Application Log.

To accomplish this, add a service account to the local Event Log Readers group.

To enable auditing of the SQL server database:

  1. Open a command window to configure the audit object access setting.
  2. Run the following command as an administrator: auditpol /set /subcategory:"application generated" /success:enable /failure:enable
  3. Run the following command to grant the generate security audits permission to an account: secpol.msc
  4. Go to the Local Security Policy tool and open Security Settings > Local Policies > User Rights Assignment.
  5. In the results pane, double-click Generate security audits.
  6. On the "Local Security Setting" tab, click Add User or Group.
  7. In the "Select Users, Computers, or Groups" dialog box, enter the name of the account SQL Server is running as and click OK.
  8. Restart SQL Server to enable this setting.
  9. To create a server audit, open SQL Server Management Studio.
  10. In "Object Explorer," expand the Security folder.
  11. Right-click the Audits folder and select New Audit.
  12. Fill in the fields and choose either Windows Application log or Windows Security log as the audit destination.

In order to audit the Windows Security log, you must have access to the Event Log Readers on your local machine.

  1. When you are finished, click OK.
  2. Right click the newly created Audit and select Enable Audit.
  3. To create a server audit specification, go to "Object Explorer" and click the plus sign to expand the "Security" folder.
  4. Right-click the Server Audit Specifications folder and select New Server Audit Specification.
  5. Enter a name, choose the server audit created above, and configure the audit action types you want to log.
    • For example, you could log the following:
  1. When you are finished, click OK.
  2. Right click the newly created Audit Specification and select Enable Audit Specification.

How to Configure This Event Source

  1. From your dashboard, select Data Collection on the left hand menu.
  2. When the Data Collection page appears, click the Setup Event Source dropdown and choose Add Event Source.
  3. From the “Raw Logs” section, click the Database Audit Logs icon. The “Add Event Source” panel appears.
  4. Choose your collector and event source. You can also name your event source if you want.
  5. Choose the timezone that matches the location of your event source logs.
  6. In the "Server" field, enter the IP address or the machine name of the server.
  7. In the "User Domain" field, enter the the domain of your credentials.
  8. Select existing credentials or create a new credential.
  9. In the "Password" field, enter the password for the SQL server.
  10. Click Save.

No Default Alerts

Please note that database audit logs do not have alerts built-in by default. You must create your own alerts.