Microsoft SQL Database Audit Logs

You can track database administrative activity through Microsoft SQL Server for log search and basic detection rules (formerly known as custom alerts) on Windows machines.

Before You Begin

Complete these requirements before you start:

• Review Microsoft SQL Server Audit documentation  to understand how auditing works.
• Confirm you have administrator privileges on the SQL server.
• Ensure the service account has access to the Windows Security or Application log. To grant access, add the service account to the Event Log Readers group.

Add the service account to the Event Log Readers group

SIEM (InsightIDR) uses the service account to read the Windows Security and Application logs. Add the account to the local Event Log Readers group on the SQL server to grant this access.

To add the service account to the local Event Log Readers group:

1. Open Computer Management by pressing Win + R, entering compmgmt.msc, and then pressing Enter.
2. From Computer Management, select System Tools > Local Users and Groups > Groups.
3. Double-click Event Log Readers.
4. From the Event Log Readers Properties dialog, select Add.
5. In the Enter the object names to select field, enter the service account name in the format DOMAIN\ServiceAccountName.
6. Select Check Names to confirm the account name resolves.
7. Select OK twice.

Enable auditing on the SQL Server database

To enable auditing, configure the Windows audit policy, create a server audit, and then create a server audit specification.

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.

Testing

After enabling SQL Server database auditing, follow these steps to verify that WMI and DCOM are functioning properly.

Test WMI Rights

1. On the SQL server, run wmimgmt.
2. Open root and highlight cimv2.
3. Click Security and confirm the rights:
   • Execute Methods
   • Enable Account
   • Remote Enable
4. If a WMI browsing tool is available from the Collector, you should now be able to connect and browse cimv2.

Test DCOM

1. On the SQL server, run dcomcnfg.exe.
2. Under Component Service, open Computers.
3. Right click My Computer > Properties > COM Security tab.
4. Under Launch and Activation Permissions, click Edit Limits.
5. Confirm Remote Launch and Remote Activation are enabled.

Configure SIEM (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 SIEM (InsightIDR).

To configure the new event source in SIEM (InsightIDR):

1. Click Data Connectors in the left menu, and navigate to SIEM > Data Collectors. 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.