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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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?
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.
Two more resource to learn about security audit.
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 Log File Viewer Window and some examples of what you'll see: