SQL Server – Viewing Login Audits in SQL Server 2016

auditsql server

I have Login Audits for both failed and successful. How do i view the actual logs? Where are they located?

https://docs.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio

Is there a way to ignore service accounts?

Best Answer

I would change the setting in properties to None and set up a security audit where you have better control on what you want to audit, for how long you want to retain the result and exclude account you do not want to audit.

enter image description here

Create a Server Audit Specification for Failed login only. You can add other audit action type as you need. 

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Audit_Spec_FaileLogin]
FOR SERVER AUDIT [FailedLogin]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

This script will create a new login fail audit. Replace filepath, maxsize, max_files. To exclude service account replace the value where I have ServiceAccount in the script.

USE [master]
GO


CREATE SERVER AUDIT [FailedLogin]
TO FILE 
(   FILEPATH = N'C:\'
    ,MAXSIZE = 64 MB
    ,MAX_FILES = 50
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE

)
WHERE ([server_principal_name]<>'ServiceAccount')
ALTER SERVER AUDIT [FailedLogin] WITH (STATE = ON)
GO

Use this process as documented in below link to read the audit file.

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

Two more resource to learn about security audit.

  1. SQL Server Security Audit Basics by Feodor Georgiev
  2. SQL Server Audit (Database Engine)
Related Question