5

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?

3
  • Please look at View a SQL Server Audit Log. Sep 20, 2017 at 13:27
  • Ok I see that but it doesnt tell me users who failed/successfully connected to a DB? Its only for users Logging into the Server itself?
    – NULL.Dude
    Sep 20, 2017 at 13:42
  • Does my answer below give you what you need? Sep 20, 2017 at 13:59

2 Answers 2

5

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://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)
2
3

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: enter image description here

The Log File Viewer Window and some examples of what you'll see: enter image description here

3
  • Ok great but it doesnt show me what database I am connecting to? just says this user connected using windows auth.
    – NULL.Dude
    Sep 20, 2017 at 13:47
  • @Geo.Dude just expand the window... state 38 = failed to open database. the name of the database is on the trailing part of the message like 'Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'foo'. [CLIENT: <local machine>]'
    – Paolo
    Sep 20, 2017 at 15:25
  • I dont see the database name: Message Login succeeded for user 'Domain\username'. Connection made using Windows authentication. [CLIENT: IPADDRESS]
    – NULL.Dude
    Sep 20, 2017 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.