Audit SQL Server Logins without filling up the Error Log
By: K. Brian Kelley | Updated: 2017-10-27 | Comments | Related: More > 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:
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:
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.
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:
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:
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.
- Learn how to read events in the Application log quickly to find what youíre looking for.
- Understand the traditional way of auditing SQL Server logins.
Last Updated: 2017-10-27
About the author
View all my tips