Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Audit SQL Server Logins without filling up the Error Log


By:   |   Read Comments   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I need to audit for logins to my SQL Server. However, I don't want to have to dig through all the login events in my SQL Server error log when trying to find other messages. Is there a way to audit logins without spamming my SQL Server log?

Solution

Yes, this is possible in Enterprise edition of SQL Server 2008R2 and in all editions of SQL Server starting in SQL Server 2012. We can do the auditing using the aptly named Audit object. SQL Server 2012 and above allows use of the Audit object at the server level, which is where logins occur. First, let's set up where the events captured will be written. This is the "Audit" itself. There are several options where the Audit can write:

  • Application Log
  • Security Log (though this requires additional setup)
  • File System (writes to a folder)

For simplicity, let's write to the Application log like so:

CREATE SERVER AUDIT TrackLogins
TO APPLICATION_LOG;
GO

Now we must define our specification, which tells SQL Server which events we're interested in. Note the WITH clause, which will begin capturing events as soon as we enable the Audit itself.

CREATE SERVER AUDIT SPECIFICATION TrackAllLogins
FOR SERVER AUDIT TrackLogins
  ADD (FAILED_LOGIN_GROUP),
  ADD (SUCCESSFUL_LOGIN_GROUP),
  ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);
GO  

Finally, we then must enable our Audit for it to begin to capture the events we are interested in.

ALTER SERVER AUDIT TrackLogins
WITH (STATE = ON);
GO 

Verifying the Audit and Audit Specifications are Enabled

Because we're talking about auditing here, you'll want to run a script to verify that everything is properly enabled.

SELECT 
    S.name AS 'Audit Name'  
  , CASE S.is_state_enabled 
      WHEN 1 THEN 'Y'
      WHEN 0 THEN 'N' END AS 'Audit Enabled'
  , S.type_desc AS 'Write Location'
  , SA.name AS 'Audit Specification Name'
  , CASE SA.is_state_enabled
      WHEN 1 THEN 'Y'
         WHEN 0 THEN 'N' END AS 'Audit Specification Enabled'
  , SAD.audit_action_name
  , SAD.audited_result
FROM sys.server_audit_specification_details AS SAD
  JOIN sys.server_audit_specifications AS SA
    ON SAD.server_specification_id = SA.server_specification_id
  JOIN sys.server_audits AS S
    ON SA.audit_guid = S.audit_guid
WHERE SAD.audit_action_id IN ('CNAU', 'LGFL', 'LGSD'); 

You should see a result showing the audit and audit specification are enabled and that you're auditing the proper events:

Results of query verifying audit options are present and enabled

Viewing the Events for Your Audit

Test by logging in successfully a few times as well as intentionally generating a few failed logins. If you had configured to audit to a file location, you would be able to see the audit events most easily in SSMS by right-clicking on the Audit and choosing View Audit Logs.

Viewing Audit Entries if writing to a file location

However, since weíre using the Application event log for our destination, youíll have to look there, such as with Event Viewer. One of the reasons I configured this example to hit against the Application event log is because there are plenty of tools which already monitor said log for security reasons. As a result, if we write to the application log, we can leverage those tools to capture the information, track it, and report on it. Also, if the tool is a Security Information and Event Management (SIEM) product, it can also do correlation of suspicious activity. Therefore, thereís good reason to use the Application event log.

Weíre looking for events from SQL Server. If itís a default instance, the source will be MSSQLSERVER. For a named instance, it should be MSSQL$<Instance Name>. Whether the event is a login success or failure, the event ID will be 33205 (and itís the event ID to filter on if you just want to see these types of events). Hereís an example of a successful login:

Audit event in Application Log

Note that weíre seeing the login and the login time, which is the key information we want to track. There is also a field, succeeded, that isnít captured in the screen shot. However, thatís we can tell whether the login attempt succeeded or failed.

If youíre looking to port this information into another product, likely the import will need to understand the XML. Hereís what it looks like:

XML version of the event

Itís not the cleanest XML hierarchy, as Iíd want the data replaced by tags for the fields, but itís consistent.

What about the Security Event Log?

In actuality, the Security event log is a better choice than the Application event log. It has tighter controls with respect to access and most security products already read from it by default. Youíd still have to ďtrainĒ a tool to interpret the event that SQL Server will enter, but once thatís done youíll get good data.

So why donít I show the solution using the Security event log? Quite simply, because thereís additional work that has to be done so that SQL Server can gain the ability to write to the Security event log. Thatís beyond the scope of this tip. However, you can find the instructions on how to configure such access in SQL Serverís own documentation, Books Online.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools