Microsoft SQL Database Audit Logs

You can track database administrative activity via Microsoft SQL Server for log search and basic detection rules (formerly known as 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.

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.

13. When you are finished, click OK.
14. Right click the newly created Audit and select Enable Audit.
15. To create a server audit specification, go to "Object Explorer" and click the plus sign to expand the "Security" folder.
16. Right-click the Server Audit Specifications folder and select New Server Audit Specification.
17. 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:

18. When you are finished, click OK.
19. Right click the newly created Audit Specification and select Enable Audit Specification.

Configure InsightIDR to collect data from the event source

After you complete the prerequisite steps and configure the event source to send data, you must add the event source in InsightIDR.

To configure the new event source in InsightIDR:

1. From the left menu, go to Data Collection and click Setup Event Source > Add Event Source.
2. Click Add Raw Data > Database Audit Logs.
   • Alternatively, you can search for Microsoft SQL Server or filter by the Database Product Type, and then select the Microsoft SQL Server event source tile.
3. Choose your collector. You can also name your event source if you want.
4. Choose the timezone that matches the location of your event source logs.
5. In the Server field, enter the IP address or the machine name of the server.
6. In the User Domain field, enter the the domain of your credentials.
7. Select existing credentials or create a new credential.
8. In the Password field, enter the password for the SQL server.
9. Click Save.