Featured Database Articles

Monitoring login changes and failed login attempts

As a DBA working at a financial company, I often face questions from BU on security auditing. They usually concern about unauthorized server access or malicious security exploitation. To help them meet the auditing requirements, I can create SQL Server traces using extended procedures, such as sp_trace_create and sp_trace_setevent, to monitor login events in the background. However, I am going to show you another approach to monitor login changes and failed login attempts with the WMI Provider for Server Events, which is easier and cleaner.

DDL_LOGIN_EVENTS is the DDL event class for login events. It has three child classes.

In this script, we added a general property __CLASS to identify if a login is altered, created or dropped. This is necessary because the three child classes don't return any class-specific properties to identify the type of action, except that DROP_LOGIN returns the TSQLCommand executed to drop the login.

The script prints out the type of events that happened, the login that was changed, and the login that issued the change. From the information, we can track down unplanned or malicious changes.

In addition to login changes, we also want to audit failed login attempts. The trace event class AUDIT_LOGIN_FAILED is for this purpose. The MonitorFailedLoginAttempts.ps1 script shown here captures failed login attempts.

The TextData property showed you the same message as in the pop-up window. The ComputerName property showed you which workstation the login was attempted from. By capturing all the failed login attempts, we can track down malicious security exploitation.

Monitoring database changes

On the server level, we need to audit database events to ensure no accidental database deletions. In a shared database environment, we also need to monitor additions of new databases in order to manage backups and disk space effectively.

Database events are included in the DDL_SERVER_LEVEL_EVENTS class. Three child classes of this class associated with database changes are listed below.

ALTER_DATABASE: Captures changes to properties of databases.

CREATE_DATABASE: Captures new database creations.

DROP_DATABASE: Captures database deletions.

There are other child classes under the DDL_SERVER_LEVEL_EVENTS class. To retrieve only events from the above three child classes, we need to use the identifier __CLASS to filter the events from the DDL_SERVER_LEVEL_EVENTS class. The sample script MonitorDatabases.ps1 is shown here.

The DatabaseName property showed the database that was being changed. The TSQLCommand property showed the actual T-SQL statement that was run against the server. You can also see the details of the sessions that made the changes.

Monitoring database objects

In a development team where each developer can make their own changes to database schema, a developer might make unplanned changes and thus overwrite the work done by another developer. To ensure the change processes transparent and manageable, we should be able to track down planned and unplanned changes to minimize the risk of improper changes causing database outage. This problem magnifies itself in dealing with stored procedures since SQL Server does not keep track of the last time a stored procedure was modified and the login that made the modifications.

The DDL_PROCEDURE_EVENTS class is the event class for stored procedure events. It has three child classes.

The query first creates a stored proc called getBlockedProcessesDetails. This stored proc gets a list of blocked and blocking sessions. Then the stored proc is modified to include the T-SQL command from each sesssion. Finally, the stored proc is dropped.

The ObjectName property showed which stored procedure was changed. The TSQLCommand property showed the actual T-SQL statement that was run against the database to change the stored procedure. The LoginName property showed the login that made the changes.

Conclusion

We have illustrated above the power of Windows PowerShell in conjunction with the WMI Provider for Server Events. SQL Server 2005 WMI providers have changed and improved a lot from SQL Server 2000. The providers can be very useful in your daily work as a DBA. The scripts from this series can be easily expanded to deal with more difficult tasks.

Yan Pan is certified in Microsoft database certifications, including MCTS in SQL Server 2005 and MCDBA. She has more than 5 years of experience in SQL Server administration and Analysis Server. Currently, Yan is the primary SQL Server DBA in one of the top finance companies on Wall Street.