4

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?

  • Please look at View a SQL Server Audit Log. – SqlWorldWide Sep 20 '17 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 '17 at 13:42
  • Does my answer below give you what you need? – SqlWorldWide Sep 20 '17 at 13:59
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://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)
| improve this 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: enter image description here

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

| improve this answer | |
  • 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 '17 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 '17 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 '17 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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