SQL Server security log - Windows Event viewer

Question

Hi All,

I have installed SQL 2017 on windows server 2016 Standard. I want to enable logs in windows server event viewer for below tasks. please advise what settings need to modified.

1. Account creation (create new SQL login/DB user) should be logged in windows event viewer
2. Administrator privilege granting should be logged in windows event viewer with server datetime
3. User account deletion shoud be logged in windows event viewer
4. sucessfull SQL connection/DB connection should be windows event viewer
5. Failed login attempt to multiple destinations from the same source should be logged in windows event viewer
6. System Audit Policy Changed should be logged in windows event viewer
7. Event viwer Log deletion should be logged in windows event viewer
8. Unable to log events message should be logged in windows event viewer
9. Multiple account or single account lockouts should be logged in windows event viewer

Currently I am able to see only below logs in event viewer

• administrative account multiple failed login attempts
• SQL login/DB login failed attempts

Answer 1

There's a feature in SQL Server designed for auditing: SQL Server Audit. One of the targets is supports is the Windows Security Log. So, create a server audit for the security log and then create a server audit specification for the events you list.

Here's a link to the documentation: https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15