Audit des connexions échouées dans SQL Server – Serveur d’impression

Apparemment, le lien avec la conversation simple ne fonctionne plus. Le nouveau lien est dans mon script

Voici le script que j'utilise pour suivre les informations de connexion ayant échoué à l'aide de notifications d'événement (2005 / 2008R2) / * DBA_FailedConnectionTracker: conservez une vue d'ensemble des connexions établies avec cette instance SQLServer. – Cet utilisateur d'implémentation SQLServer Service Brocker avec notifications d'événement * / UTILISEZ YourBonitoring db; si object_id ('dbo.T_DBA_FailedConnectionTracker') est nullcommencer print 'Table [T_DBA_FailedConnectionTracker] Créé'; CREER LA 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) non nul, [tsRegistration] datetime NOT NULL par défaut getdate (), [FailedLoginData] XML ) Créer un index clusterisé clX_DBA_FailedConnectionTracker sur [dbo].[T_DBA_FailedConnectionTracker] ([tsRegistration]) Créer un index X_DBA_FailedConnectionTracker sur [dbo].[T_DBA_FailedConnectionTracker] ([login_name], [program_name]) fin / *source utilisant SSB: http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/ ils ont réorganisé leur site. c'est la nouvelle URL http://www.simple-talk.com/sql/t-sql-programming/logon-triggers/* / – Activer la base de données Service Broker pour DDBAServerPing si c'est le casSI EXISTE (SÉLECTIONNER * FROM sys.databases OÙ [name]= N'DDBAServerPing ' AND is_broker_enabled = 0)Commencer print 'SSB enabled'; ALTER DATABASE DDBAServerPing SET ENABLE_BROKER;FIN – Nous allons accéder à partir de la procédure stockée activée à une vue qui est – situé dans une base de données différente – la vue de gestion dynamique sys.dm_exec_sessions– Le contexte de sécurité de la procédure stockée ne nous permettrait pas de le faire– sauf si nous définissons l'option TRUSTWORTHY sur ON.– Pourquoi et une autre méthode que vous pouvez trouver ici:– http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx SI EXISTE (SÉLECTIONNER * FROM sys.databases OÙ [name]= N'DDBAServerPing ' AND is_trustworthy_on = 0)Commencer print 'digne de confiance commuté sur ON'; ALTER DATABASE DDBAServerPing SET TRUSTWORTHY ON;FIN – Créer une file d'attenteCREATE QUEUE Q_FailedLogon_Tracker_Queue; – Créer un serviceCREATE SERVICE S_FailedLogon_Tracker_Service ON QUEUE Q_FailedLogon_Tracker_Queue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) – Créer un itinéraireCREATE ROUTE R_FailedLogon_Tracker_Route WITH SERVICE_NAME = N'S_FailedLogon_Tracker_Service ' , ADRESSE = N'LOCAL ';aller / * L'utilisateur actuel devient propriétaire de EVENT NOTIFICATION, passez donc à 'sa' * /EXEC AS LOGIN = 'sa';aller– Créer la notification d'événement au niveau du serveur pour l'événement AUDIT_LOGINCREATE NOTIFICATION D'EVENEMENTS N_Failed_Login_Notification SUR SERVEUR POUR AUDIT_LOGIN_FAILED TO SERVICE 'S_FailedLogon_Tracker_Service', 'base de données actuelle'; aller/ * Revenir à l'utilisateur d'origine * /REVENIR;ALLER – Créer la procédure stockée qui gérera les événements– Définissez d'abord les options requises pour utiliser le type de données XML.SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER ON;ALLER CREATE PROCEDURE dbo.spc_DBA_FailedConnectionTrackerCOMMECOMMENCERSET NOCOUNT ON;– Utilisez une boucle sans fin pour recevoir des messagesPENDANT (1 = 1) COMMENCER DECLARE @messageBody VARBINARY (MAX); DECLARE @messageTypeName NVARCHAR (256); ATTENDRE ( RECEVEZ LE TOP (1) @messageTypeName = nom_type_message, @messageBody = message_body FROM Q_FailedLogon_Tracker_Queue ), TIMEOUT 500 – S'il n'y a pas de message, quittez SI @@ ROWCOUNT = 0 COMMENCER PAUSE ; FIN ; – Si le type de message est EventNotification, effectue le travail réel. IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification') COMMENCER DECLARE @XML XML, @host_name varchar (128), @ nom_programme varchar (128), @nt_domain varchar (128), @nt_user_name varchar (128), @nom_installation varchar (128), @original_login_name varchar (128), @client_net_address varchar (48), @DonnéesBaseDeDonnées varchar (128), @ts_logon datetime, @SPID VARCHAR (5); SELECT @ XML = CONVERT (XML, @ messageBody) , @ host_name = '' , @ nom_programme = '' , @ nt_domain = '' , @ nt_user_name = '' , @ login_name = '' , @ original_login_name = '' , @ client_net_address = '' , @ SPID = ''; – Obtenir le SPID et le nom de connexion à l'aide de la méthode value 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) ') ; –Choisissez @ nom_programme = cas où supérieur (@ nom_programme), comme 'DATABASEMAIL -%' – puis sous-chaîne (@ nom_programme, 1, identificateur de caractère ('<', @ nom_programme, 1) – 1) – else @ nom_programme — fin – / * GUID du message geef 'UABEPRD EXECBTS' ainsi que le nom de l'application mee !! enregistrer cet utilisateur une seule fois * /– Mettre à jour dbo.T_DBA_FailedConnectionTracker — ensemble [tsLastUpdate] = cas où [tsLastUpdate] <@ts_logon puis @ts_logon— autre [tsLastUpdate]— fin— Où [host_name] = @host_name– et cas où supérieure ([login_name]) comme 'UABEPRD EXECBTS%' puis @ nom_programme— autre [program_name]– fin = @ nom_programme — — et [program_name] = @ nom_programme— et [nt_domain] = @nt_domain— et [nt_user_name] = @nt_user_name — et [login_name] = @login_name— et [original_login_name] = @original_login_name— et [client_net_address] = ISNULL (@client_net_address, '')— et [Database_Name] = ISNULL (@Données_Database, '')–– si @@ rowcount = 0— commencer INSÉRER DANS [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] ) valeurs (@nom_hôte, @nom_programme, @nom_domaine, @nom_utilisateur_nt, @nom_machine, @nom_login_original, ISNULL (@adresse_noire_client, ''), ISNULL (@nom_base_de_données, ''), @tient_logon, @XML); — fin / * Sélectionnez * de dbo.T_DBA_FailedConnectionTracker commandé par [tsRegistration] desc * / FIN; FIN;FIN; ALLERsi object_id ('dbo.spc_DBA_FailedConnectionTracker') n'est pas nullcommencer – Liez la procédure stockée à la Q_FailedLogon_Tracker_Queue ALTER QUEUE Q_FailedLogon_Tracker_Queue AVEC STATUS = ON , ACTIVATION (STATUS = ON , PROCEDURE_NAME = dbo.spc_DBA_FailedConnectionTracker , MAX_QUEUE_READERS = 4 , EXECUTE AS SELF); fin AUTRECOMMENCER RAISERROR ('Message DBA: file d'attente SSB FailedConnectionTracker NON activée !!!', 1,1) WITH log FIN/ *Les informations publiées sur S_FailedLogon_Tracker_Service, à propos de l'événement AUDIT_LOGIN, ont la structure suivante: AUDIT_LOGIN_FAILED 2009-02-05T13: 21: 12.640 56 La connexion a échoué pour l'utilisateur 'ikke_test'.[CLIENT:[CLIENT:] 1 WS20098002 5456 Composants Microsoft Data Access ikke_test 2009-02-05T13: 21: 12.637 1 0 WS20098002 SQL2005DE 18456 maîtriser 0 16981 ikke_test event_type est évidemment AUDIT_LOGIN. post_time contient l'heure à laquelle l'événement est publié sur le service. spid représente l'ID de la session pour l'événement. text_data contient une liste délimitée par des points-virgules de toutes les options définies. binary_data contient les paramètres de niveau de session, y compris les valeurs null ANSI, le remplissage ANSI, la fermeture du curseur lors de la validation, la concaténation NULL et les identificateurs entre guillemets. id_base_de_données est l'ID de la base de données par défaut ou l'ID de la base de données utilisée par l'instruction USE, si elle existe. nt_user_name représente le nom d'utilisateur Windows. nt_domain_name représente le domaine Windows auquel l'utilisateur appartient. nom_hôte contient le nom de l'ordinateur sur lequel le client est en cours d'exécution. client_process_id est l'ID attribué par l'ordinateur hôte au processus d'exécution de l'application client. nom_application est le nom de l'application cliente. login_name est le nom du login utilisé. heure_début représente l'heure à laquelle l'événement a commencé. event_subclass indique si la connexion est groupée ou non groupée avec les valeurs 1 pour non groupé et 2 pour groupé. L'élément success indique si l'authentification a réussi et a les valeurs 1 pour succès et 0 pour échec. Pour l'événement AUDIT_LOGIN, il contiendra toujours la valeur 1. integer_data représente la taille du paquet réseau. nom_serveur représente le nom de l'instance de SQL Server sur laquelle l'événement s'est produit. nom_base_de_données est le nom de la base de données login_sid contient le numéro d'identification de sécurité (SID) de l'utilisateur connecté. request_id représente l'ID de la requête. event_sequence représente la séquence de l'événement dans la demande. is_system indique si l'événement s'est produit pour un utilisateur ou un processus système. Dans ce cas, il s'agira toujours d'un processus utilisateur.* / / * ALLER – nettoyer seulement si nécessaire / * En cas de problèmes, commencez par abandonner l'abonnement EVENT NOTIFICATION * /– DROP EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER – ALTER QUEUE Q_FailedLogon_Tracker_Queue WITH STATUS = OFF– DROP EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER – DROP ROUTE R_FailedLogon_Tracker_Route – DROP SERVICE S_FailedLogon_Tracker_Service– DROP QUEUE Q_FailedLogon_Tracker_Queue; – DROP PROCEDURE dbo.spc_DBA_FailedConnectionTracker–

Click to rate this post! [Total: 0 Average: 0]