View SQL Server 2016 Login Audits

Question

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

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

Is there a way to ignore service accounts?

Answer 1

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.

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://learn.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)

Answer 2

If you're using the option found in the Server Properties -> Security window (screenshot below), you can view the logs captured by this in the SQL Server ERRORLOGs, which are found under the Management -> SQL Server Logs folder.

If you want to filter your service accounts out of this, you'll ned to setup a SQL Server Audit as you are unable to customize the basic Security auditing to anything other than None, Failed, Successful, or both Failed and Successful. If you are interested in setting up an Audit, be sure your instance is patched to SP1 CU1 or later if you're not using Enterprise edition as this feature was exclusive to Enterprise prior to this patch. Here's a blog post by Eralper Yilmaz that runs through how to set one of these up.

The Option:

The Log File Viewer Window and some examples of what you'll see: