How To Configure SQL Server Audit Events To The Security Log

Published: 18 May 2023
on channel: MS SQL DBA Tech Support

5,205

Microsoft reference Link
https://learn.microsoft.com/en-us/sql...

How to configure SQL audit logs in to windows security
1.Add SQL Server instance service account to below
Press Win + R on your keyboard to open the Run dialog box, type "secpol.msc" (without quotes), and press Enter. This will open the Local Security Policy console.
Local PoliciesUser Rights AssignmentManage auditing and security logadd user or group.
Local PoliciesUser Rights Assignment Generate security auditsadd user or group.
Note:gpupdate /force to make changes effective from CMD

2.Take RDP where SQL Server is installed and Regedit and move to below and provide full permission to Instance service account.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security
Note:Restart SQL Server to make changes effective.
3.Configure the audit object access setting in Windows using auditpol[Optional]
open Command Prompt with administrator permissions and execute the following statement:
auditpol /set /subcategory:"application generated" /success:enable /failure:enable

Note: Restart SQL Server to make changes effective.

4.Create and any sql server security audit event and test it.

============================
SELECT event_time,action_id, statement, database_name, server_principal_name
FROM fn_get_audit_file( 'D:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT)-- where action_id != 'LGIS'
============================

Watch video How To Configure SQL Server Audit Events To The Security Log online without registration, duration hours minute second in high quality. This video was added by user MS SQL DBA Tech Support 18 May 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 5,205 once and liked it 25 people.

53,569

1.7K