Nice solution for everyone using sql 7.0 or sql2000. As from sql2005, IMO it is better to use SQLServer events. It is as easy as this and it gives you more info ! /*
DBA_FailedConnectionTracker: Maintain an overview of which connections are made to this SQLServer instance
-- This implementation user SQLServer Service Brocker with Event Notifications
*/
USE DDBAaudit;
if object_id('dbo.T_DBA_FailedConnectionTracker') is null begin print 'Table [T_DBA_FailedConnectionTracker] Created'; CREATE TABLE [dbo].[T_DBA_FailedConnectionTracker]( [host_name] [varchar](128) NOT NULL, [program_name] [varchar](128) NOT NULL, [nt_domain] [varchar](128) NOT NULL, [nt_user_name] [varchar](128) NOT NULL, [login_name] [varchar](128) NOT NULL, [original_login_name] [varchar](128) NOT NULL, [client_net_address] [varchar](48) NOT NULL, [Database_Name] [varchar](128) not null, [tsRegistration] datetime NOT NULL default getdate(), [FailedLoginData] XML ) ; Create clustered index clX_DBA_FailedConnectionTracker on [dbo].[T_DBA_FailedConnectionTracker] ([tsRegistration]); Create index X_DBA_FailedConnectionTracker on [dbo].[T_DBA_FailedConnectionTracker] ([login_name], [program_name]); end
/* source using SSB : http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/ */
-- Enable Service Broker for DDBAaudit database if it's the case IF EXISTS( SELECT * FROM sys.databases WHERE [name]=N'DDBAaudit' AND is_broker_enabled = 0 ) Begin print 'SSB enabled'; ALTER DATABASE DDBAaudit SET ENABLE_BROKER; END
-- We will access from the activated stored procedure a view that is -- located in a different database -- the sys.dm_exec_sessions dynamic management view -- The security context of the stored procedure would not allow us to do so -- unless we set the the TRUSTWORTHY option to ON. -- Why and another method you can find here: -- http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
IF EXISTS( SELECT * FROM sys.databases WHERE [name]=N'DDBAaudit' AND is_trustworthy_on = 0 ) Begin print 'trustworthy switched to ON'; ALTER DATABASE DDBAaudit SET TRUSTWORTHY ON; END
-- Create a queue CREATE QUEUE Q_FailedLogon_Triggers_Queue;
-- Create a service CREATE SERVICE S_FailedLogon_Triggers_Service ON QUEUE Q_FailedLogon_Triggers_Queue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
-- Create a route CREATE ROUTE R_FailedLogon_Triggers_Route WITH SERVICE_NAME = N'S_FailedLogon_Triggers_Service' , ADDRESS = N'LOCAL'; go
/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */ EXEC AS LOGIN = 'sa'; go -- Create the event notification at the server level for the AUDIT_LOGIN event CREATE EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER FOR AUDIT_LOGIN_FAILED TO SERVICE 'S_FailedLogon_Triggers_Service', 'current database';
go /* Switch back to original user */ REVERT; GO
-- Create the stored procedure that will handle the events -- First set the options required to work with the XML data type SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO
CREATE PROCEDURE dbo.spc_DBA_FailedConnectionTracker AS BEGIN SET NOCOUNT ON; -- Use an endless loop to receive messages WHILE (1 = 1) BEGIN DECLARE @messageBody VARBINARY(MAX); DECLARE @messageTypeName NVARCHAR(256); WAITFOR ( RECEIVE TOP(1) @messageTypeName = message_type_name, @messageBody = message_body FROM Q_FailedLogon_Triggers_Queue ), TIMEOUT 500 -- If there is no message, exit IF @@ROWCOUNT = 0 BEGIN BREAK ; END ; -- If the message type is EventNotification do the actual work IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification') BEGIN DECLARE @XML XML, @host_name varchar(128) , @program_name varchar(128) , @nt_domain varchar(128) , @nt_user_name varchar(128) , @login_name varchar(128) , @original_login_name varchar(128) , @client_net_address varchar(48) , @Database_Name varchar(128) , @ts_logon datetime, @SPID VARCHAR(5); SELECT @XML=CONVERT(XML,@messageBody) ,@host_name = '' ,@program_name = '' ,@nt_domain = '' ,@nt_user_name = '' ,@login_name = '' ,@original_login_name = '' ,@client_net_address ='' ,@SPID =''; -- Get the SPID and the Login name using the value method SELECT @SPID = @XML.value('(/EVENT_INSTANCE/SPID)[1]', 'VARCHAR(5)') , @ts_logon = @XML.value('(/EVENT_INSTANCE/StartTime)[1]', 'NVARCHAR(128)') , @host_name = @XML.value('(/EVENT_INSTANCE/HostName)[1]', 'NVARCHAR(128)') , @program_name = @XML.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'NVARCHAR(128)') , @nt_domain = @XML.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'NVARCHAR(128)') , @nt_user_name = @XML.value('(/EVENT_INSTANCE/NTUserName)[1]', 'NVARCHAR(128)') , @original_login_name = @XML.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'NVARCHAR(128)') , @login_name = @XML.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)') , @Database_Name = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)') ;
INSERT INTO [dbo].[T_DBA_FailedConnectionTracker] ([host_name], [program_name], [nt_domain], [nt_user_name], [login_name], [original_login_name], [client_net_address], [Database_Name], [tsRegistration], [FailedLoginData] ) values ( @host_name, @program_name, @nt_domain, @nt_user_name, @login_name, @original_login_name, ISNULL(@client_net_address,''), ISNULL(@Database_Name,''), @ts_logon, @XML);
/* Select * from dbo.T_DBA_FailedConnectionTracker order by [tsRegistration] desc */ END; END; END;
GO if object_id('dbo.spc_DBA_FailedConnectionTracker') is not null begin -- Link the stored procedure to the Q_FailedLogon_Triggers_Queue ALTER QUEUE Q_FailedLogon_Triggers_Queue WITH STATUS=ON , ACTIVATION ( STATUS=ON , PROCEDURE_NAME = dbo.spc_DBA_FailedConnectionTracker , MAX_QUEUE_READERS = 1 , EXECUTE AS SELF) ;
end ELSE BEGIN RAISERROR ('DBA Message: SSB Queue FailedConnectionTracker NOT Activated !!! ', 1,1 ) WITH log
END /* The information posted to the S_FailedLogon_Triggers_Service, about the AUDIT_LOGIN event, has the following structure:
<EVENT_INSTANCE> <EventType>AUDIT_LOGIN_FAILED</EventType> <PostTime>2009-02-05T13:21:12.640</PostTime> <SPID>56</SPID> <TextData>Login failed for user 'ikke_test'. [CLIENT: <local machine>]</TextData> <DatabaseID>1</DatabaseID> <NTUserName /> <NTDomainName /> <HostName>WSAA098002</HostName> <ClientProcessID>5456</ClientProcessID> <ApplicationName>Microsoft Data Access Components</ApplicationName> <LoginName>ikke_test</LoginName> <StartTime>2009-02-05T13:21:12.637</StartTime> <EventSubClass>1</EventSubClass> <Success>0</Success> <ServerName>Mytestserver\SQL2005DE</ServerName> <Error>18456</Error> <DatabaseName>master</DatabaseName> <RequestID>0</RequestID> <EventSequence>16981</EventSequence> <IsSystem /> <SessionLoginName>ikke_test</SessionLoginName> </EVENT_INSTANCE>
event_type is obviously AUDIT_LOGIN. post_time contains the time when the event is posted to the service. spid represents the ID of the session for the event. text_data contains a semicolon-delimited list of all set options. binary_data contains the session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers. database_id is the ID of the default database or the ID of the database used by the USE database statement if any. nt_user_name represents the Windows user name. nt_domain_name represents the Windows domain to which the user belongs. host_name contains the name of the computer on which the client is running. client_process_id is the ID assigned by the host computer to the process where the client application is running. application_name is the name of the client application. login_name is the name of the login used. start_time represents the time that the event started. event_subclass indicates if the connection is pooled or non-pooled with values 1 for non-pooled and 2 for pooled. success element indicates if the authentication succeeded and has the values 1 for success and 0 for failure. For the AUDIT_LOGIN event it will always contain value 1. integer_data represents the network packet size. server_name represents the name of the instance of SQL Server on which the event occurred. database_name is the name of the database login_sid contains the security identification number (SID) of the logged-in user. request_id represents the ID of the request. event_sequence represents the sequence of the event within the request. is_system indicates if the event occurred for a user or a system process. In this case it would always be a user process. */
|