Audit SQL Server Logins without filling up the Error Log

By:   |   Comments   |   Related: > Auditing and Compliance


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?


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:


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.


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


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 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'
  , 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms