Auditing Failed Logins in SQL Server: Looking Beyond Post-event Analysis

Introduction

Auditing login attempts in SQL Server is an important part of overall data security strategy. SQL Server administrators need to be fully aware of who is logging into their SQL Server environment and who is making an unsuccessful attempt. By analyzing login attempts, they can build the complete picture out of the sequence of events that led to compliance failure or unauthorized access.

Though many would say analyzing only failed login attempts might leave loopholes in your SQL Server auditing approach, enabling both successful and failed login would create a huge amount of logs to flip through. If you are not using any special log management system/auditing system, analyzing this much of data would take a lot of time and resources which might not be acceptable for many small and mid-size organizations.

In this tip, we would see how to enable failed login attempts auditing through SQL Server Management Studio. To enable auditing failed logins in SQL Server:

• Right-click on the SQL Server.
• Select properties option from the pop-up menu.
• Server Properties window opens up.
• Click on the Security page in Select a Page pane on the left side of the screen.
• Go to login auditing section. It has four options under it:
  1. None
  2. Failed Logins only
  3. Successful Login only
  4. Both failed and successful logins
• Select Failed Login Only radio button.
• Click on OK to save the settings.
• Restart the SQL Server service for the changes to take effect.

Now that the auditing is enabled, you can view the audit logs in the Log File Viewer. To view logs in Log File Viewer:

• Go to Object Explorer.
• Expand the Security node.
• Expand the Audits node.
• Right-click on audit.
• Click View Audit Logs.
• In the Log File Viewer.
• Select Audit Collection in the left pane.
• Select the log which you want to view and the entire log is displayed in the right pane.
• You can filter the event logs to get to a particular log.

As you can see, by using these settings, we only get a way to analyze events in hindsight after they have occurred. Such take on auditing might not be the best approach to deal with critical servers which hold important data. To deal with such critical environments, you need to take a more pro-active approach where you get instant notification when an unauthorized access takes place, so that damage can be undone before any serious issue takes place.

To overcome the hurdle, you can create a real-time alert using the WMI event AUDIT_LOGIN_FAILED that would notify the administrator in the case of critical events. To enable email alerts:

• In the Management Studio, right-click on the SQL Server Agent.
• Select Properties from the popup menu.
• In the SQL Server Agent Properties window, select Alert System page in the left pane.
• On this page, select Enable Mail Profile.
• Select body of email in notification message.
• Select Token replacement.

Then, you can go on to write the necessary scripts that would generate emails with full information about the event such as User name, reason of the login failure, client system, etc.