How To Configure SQL Server Audit Events To The Security Log
Вставка
- Опубліковано 19 вер 2024
- Microsoft reference Link
learn.microsof...
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 PoliciesUser Rights AssignmentManage auditing and security logadd user or group.
Local PoliciesUser Rights Assignment Generate security auditsadd 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'
============================
Thank you so much as per my request you have made the video on audit logs.
No worries
can you provide how to enable the all database audits
Can you elaborate more on your question
Hi Friend
What is your recommend "Audit Action Type " we should use it ?
For the security purpose.
Its Depends upon your requirement like what kind of events that you want to track.... you need to select related audit action type events.
Hi,
Could you please make video on SQL server licensing?
Thank You
sure
How to find the audit log using query I have search the view history it's still loading
All the Job history information are stored in sysjobhistory table in msdb
database.
Bellow Query might useful
=================================
USE msdb
GO
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
ORDER BY j.name, h.run_date, h.run_time
GO
=================================
Thanks & Regards
MS SQL DBA TECH SUPPORT