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:
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.
Next Steps
