Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 22, 2008

Microsoft Windows PowerShell and SQL Server 2005 WMI Providers - Part 2 - Page 2

By DatabaseJournal.com Staff

by Yan Pan

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.

  • ALTER_LOGIN: Captures changes to properties of SQL Server logins and Windows logins.
  • CREATE_LOGIN: Captures new account creations.
  • DROP_LOGIN: Captures account deletions.

The MonitorLogins.ps1 script shown here monitors login changes.

$query = "SELECT * FROM DDL_LOGIN_EVENTS"
$sqlnamespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
$selections= "__CLASS", "SID", "ObjectName", "ObjectType", "PostTime", 
	"ComputerName", `
"DefaultLanguage", "DefaultDatabase", "SPID", "LoginName", "SQLInstance", 
	"LoginType"

Get-WMIEvent $query $sqlnamespace $selections

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.

Let's run this query on the default instance.

CREATE LOGIN sqluser WITH PASSWORD='Welcome123' 
ALTER LOGIN sqluser WITH PASSWORD='Er34jkOio'
DROP LOGIN sqluser

As you can see, the query creates a SQL Server login sqluser, changes its password, and then drops the login. Our script MonitorLogins.ps1 captures all three login events.

PS > C:\ MonitorLogins.ps1


__CLASS         	:	CREATE_LOGIN
SID             	:	{1, 170, 39, 34...}
ObjectName      	:	Sqluser
ObjectType      	:	LOGIN
PostTime        	:	20071104214028.000507+000
ComputerName    	:	POWERPC
DefaultLanguage 	:	us_english
DefaultDatabase 	:	Master
SPID            	:	51
LoginName       	:	PowerPC\Yan
SQLInstance     	:	MSSQLSERVER
LoginType       	:	SQL Login
		
__CLASS         	:	ALTER_LOGIN
SID             	:	{1, 170, 39, 34...}
ObjectName      	:	Sqluser
ObjectType      	:	LOGIN
PostTime        	:	20071104214028.000557+000
ComputerName    	:	POWERPC
DefaultLanguage 	:	us_english
DefaultDatabase 	:	Master
SPID            	:	51
LoginName       	:	PowerPC\Yan
SQLInstance     	:	MSSQLSERVER
LoginType       	:	SQL Login
		
__CLASS         	:	DROP_LOGIN
SID             	:	{1, 170, 39, 34...}
ObjectName      	:	Sqluser
ObjectType      	:	LOGIN
PostTime        	:	20071104214028.000850+000
ComputerName    	:	POWERPC
DefaultLanguage 	:	us_english
DefaultDatabase 	:	Master
SPID            	:	51
LoginName       	:	PowerPC\Yan
SQLInstance     	:	MSSQLSERVER
LoginType       	:	SQL 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.

$query = "SELECT * FROM AUDIT_LOGIN_FAILED"
$sqlnamespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
$selections= "HostName","NTUserName","SQLInstance","Success","IsSystem","RequestID", `
"DatabaseID","DatabaseName","ComputerName","SessionLoginName","SPID","NTDomainName", `
"LoginName","StartTime","ApplicationName","EventSequence","PostTime","ClientProcessID", `
"Error","TextData"

Get-WMIEvent $query $sqlnamespace $selections

Let's run a test. Open SQL Server Management Studio. Try to login as the sqluser we dropped in the last section. You should get a pop-up message like this

Look at the output from our script.

PS > C:\ MonitorFailedLoginAttempts.ps1


HostName         	:	POWERPC
NTUserName       	:	
SQLInstance      	:	MSSQLSERVER
Success          	:	0
IsSystem         	:	0
RequestID        	:	0
DatabaseID       	:	1
DatabaseName     	:	Master
ComputerName     	:	POWERPC
SessionLoginName 	:	Sqluser
SPID             	:	53
NTDomainName     	:	
LoginName        	:	Sqluser
StartTime        	:	20071104220123.000600+000
ApplicationName  	:	Microsoft SQL Server Management Studio
EventSequence    	:	1744
PostTime         	:	20071104220123.000667+000
ClientProcessID  	:	5808
Error            	:	18452
TextData         	:	Login failed for user 'sqluser'. The user is not associated
			with a trusted SQL Server connection. [CLIENT: <local machin
			e>]

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.

$query = "SELECT * FROM DDL_SERVER_LEVEL_EVENTS `
WHERE __CLASS = 'CREATE_DATABASE' or __CLASS = 'ALTER_DATABASE' `
or __CLASS = 'DROP_DATABASE'"
$sqlnamespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
$selections= "SQLInstance","PostTime","ComputerName","SPID","LoginName", `
"TSQLCommand","DatabaseName"

Get-WMIEvent $query $sqlnamespace $selections

Let's run a test. Execute this query against the default instance.

CREATE DATABASE testDB
ALTER DATABASE testDB modify file (name=testDB, size=10MB)
DROP DATABASE testDB

This script creates a testDB database, changes the size of the data file of the database, and then drops the database. The MonitorDatabases.ps1 script captures all three events.

PS > C:\MonitorDatabases.ps1


SQLInstance  	:	MSSQLSERVER
PostTime     	:	20071106215551.000750+000
ComputerName 	:	POWERPC
SPID         	:	52
LoginName    	:	PowerPC\Yan
TSQLCommand  	:	<TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
			ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"></Set
			Options><CommandText>CREATE DATABASE testDB&#x0D;&#x0A;</Command
			Text></TSQLCommand>
DatabaseName 	:	testDB
		
SQLInstance  	:	MSSQLSERVER
PostTime     	:	20071106215552.000000+000
ComputerName 	:	POWERPC
SPID         	:	52
LoginName    	:	PowerPC\Yan
TSQLCommand  	:	<TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
			ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"></Set
			Options><CommandText>ALTER DATABASE testDB modify file (name=tes
			tDB, size=10MB)&#x0D;&#x0A;</CommandText></TSQLCommand>
DatabaseName 	:	 testDB
		
SQLInstance  	:	MSSQLSERVER
PostTime     	:	20071106215552.000013+000
ComputerName 	:	POWERPC
SPID         	:	52
LoginName    	:	PowerPC\Yan
TSQLCommand  	:	<TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
			ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"></Set
			Options><CommandText>DROP DATABASE testDB&#x0D;&#x0A;</CommandTe
			xt></TSQLCommand>
DatabaseName 	:	testDB

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.

  • ALTER_ PROCEDURE: Captures changes to properties of stored procedures.
  • CREATE_ PROCEDURE: Captures creations of new stored procedures.
  • DROP_ PROCEDURE: Captures deletions of existing stored procedures.

The MonitorStoredProcs.ps1 script shown here monitors stored procedure changes.

$query = "SELECT * FROM DDL_PROCEDURE_EVENTS WHERE DatabaseName='AdventureWorks'"
$sqlnamespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
$selections = "SchemaName","TSQLCommand","PostTime","SQLInstance","ObjectName", `
"ObjectType","DatabaseName","ComputerName","SPID","LoginName","UserName"
 
Get-WMIEvent $query $sqlnamespace $selections

Let's run a test. Execute this query in the AdventureWorks database on the default instance.

CREATE PROCEDURE getBlockedProcessesDetails
AS 
SELECT session_id, blocking_session_id 
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GO
 
ALTER PROCEDURE getBlockedProcessesDetails
AS
SELECT a.session_id, a.command as blocked_session_command, 
a.blocking_session_id, b.command as blocking_session_command
FROM sys.dm_exec_requests a JOIN sys.dm_exec_requests b
ON a.blocking_session_id = b.session_id
WHERE a.blocking_session_id > 0
GO
 
DROP PROCEDURE getBlockedProcessesDetails

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 MonitorStoredProcs.ps1 script captures all three events.

PS > C:\MonitorStoredProcs.ps1
 
 
SchemaName   	:	Dbo
TSQLCommand  	:	<TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
	 	 	ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"></Set
 		 	Options><CommandText>CREATE PROCEDURE getBlockedProcessesDetails
 	 		&#x0D;&#x0A;AS &#x0D;&#x0A;SELECT session_id, blocking_session_i
	 	 	d &#x0D;&#x0A;FROM sys.dm_exec_requests&#x0D;&#x0A;WHERE blockin
 		 	g_session_id > 0&#x0D;&#x0A;</CommandText></TSQLCommand>
PostTime     	:	 20071107211501.000790+000
SQLInstance  	:	 MSSQLSERVER
ObjectName   	:	 getBlockedProcessesDetails
ObjectType   	:	 PROCEDURE
DatabaseName 	:	AdventureWorks
ComputerName 	:	POWERPC
SPID         	:	52
LoginName    	:	PowerPC\Yan
UserName     	:	Dbo
 	 	 
SchemaName   	:	Dbo
TSQLCommand  	:	<TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
 	 		ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"></Set
	 	 	Options><CommandText>&#x0D;&#x0A;ALTER PROCEDURE getBlockedProce
 		 	ssesDetails&#x0D;&#x0A;AS&#x0D;&#x0A;SELECT a.session_id, a.comm
 	 		and as blocked_session_command, &#x0D;&#x0A;a.blocking_session_i
	 	 	d, b.command as blocking_session_command&#x0D;&#x0A;FROM sys.dm_
 		 	exec_requests a JOIN sys.dm_exec_requests b&#x0D;&#x0A;ON a.bloc
 	 		king_session_id = b.session_id&#x0D;&#x0A;WHERE a.blocking_sessi
	 	 	on_id > 0&#x0D;&#x0A;</CommandText></TSQLCommand>
PostTime     	:	20071107211502.000173+000
SQLInstance  	:	MSSQLSERVER
ObjectName   	:	getBlockedProcessesDetails
ObjectType   	:	PROCEDURE
DatabaseName 	:	AdventureWorks
ComputerName 	:	POWERPC
SPID         	:	52
LoginName    	:	PowerPC\Yan
UserName     	:	Dbo
 	 	 
SchemaName   	:	Dbo
TSQLCommand  	:	<TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
	 	 	ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"></Set
 		 	Options><CommandText>DROP PROCEDURE getBlockedProcessesDetails&#
 	 		x0D;&#x0A;&#x0D;&#x0A;&#x0D;&#x0A;</CommandText></TSQLCommand>
PostTime     	:	20071107211502.000450+000
SQLInstance  	:	MSSQLSERVER
ObjectName   	:	getBlockedProcessesDetails
ObjectType   	:	PROCEDURE
DatabaseName 	:	AdventureWorks
ComputerName 	:	POWERPC
SPID         	:	52
LoginName    	:	PowerPC\Yan
UserName     	:	Dbo

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date