¡Hola, chicos!
En esta publicación, me gustaría mostrarle cómo crear una auditoría de inicio de sesión utilizando los registros de instancia, que es una opción nativa de SQL Server y no requiere la creación de ningún recurso adicional como disparador, eventos extendidos, auditoría, etc.

ya habia publicado el articulo SQL Server – Cómo implementar auditoría y control de inicios de sesión (Logon Trigger) usando un activador y fue una solución muy interesante, pero, como todos los activadores de inicio de sesión, puede plantear algunos riesgos si no se implementa bien e impedir que los usuarios inicien sesión.

También mostré una solución similar en el artículo. SQL Server: cómo evitar ataques de fuerza bruta a su base de datos, pero la atención se centró únicamente en fallos de conexión debido a un usuario inexistente o una contraseña incorrecta.

Cómo habilitar la auditoría de inicio de sesión en SQL Server

Para activar la auditoría de inicio de sesión en SQL Server, primero abra la instancia en "Explorador de objetos", haga clic derecho y seleccione la opción "Propiedades"

Haga clic en la pestaña "Seguridad" y luego en la categoría "Auditoría de inicio de sesión", marque la opción "Inicios de sesión fallidos y exitosos".

Otra forma de activar esto es usando este script T-SQL:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO

Después de realizar este cambio, reinicie el servicio SQL Server a través del Administrador de configuración de SQL Server.

Importante: Si el Administrador de configuración de SQL Server no aparece en el menú de inicio, lea el artículo ¿El Administrador de configuración de SQL Server desapareció? Descubra cómo recuperar el acceso directo.

Cómo leer los datos de auditoría recopilados

Para leer los datos de auditoría recopilados, utilizaremos el procedimiento interno. xp_readerrorlog, que lee los datos en los registros de SQL Server.

Para obtener más información sobre este procedimiento y otros procedimientos internos de SQL Server, lea el artículo. Procedimientos extendidos no documentados de SQL Server.

A continuación se muestra el script que puede utilizar para capturar estos datos de los registros de SQL Server y almacenarlos en una tabla física en su base de datos para mantener el historial.

--------------------------------------------------------------
-- Cria as tabelas temporárias
--------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Arquivos_Log') IS NOT NULL) DROP TABLE #Arquivos_Log
CREATE TABLE #Arquivos_Log ( 
    [idLog] INT, 
    [dtLog] NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AI, 
    [tamanhoLog] INT 
)

IF (OBJECT_ID('tempdb..#Audit_Login') IS NOT NULL) DROP TABLE #Audit_Login
CREATE TABLE #Audit_Login (
    [LogNumber] TINYINT NULL,
    [LogDate] DATETIME NOT NULL, 
    [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, 
    [Username] VARCHAR(200) NULL,
    [AuthenticationType] VARCHAR(20) NULL,
    [Result] VARCHAR(20) NULL,
    [IP] VARCHAR(100) NULL,
    [Reason] VARCHAR(500) NULL,
    [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
)


IF (OBJECT_ID('dbo.Login_Audit') IS NULL)
BEGIN
    
    -- DROP TABLE [dbo].[Login_Audit]
    CREATE TABLE [dbo].[Login_Audit]
    (
        [LogNumber] TINYINT NOT NULL,
        [LogDate] DATETIME NOT NULL,
        [ProcessInfo] NVARCHAR(50) NOT NULL,
        [Username] VARCHAR(200) NOT NULL,
        [AuthenticationType] VARCHAR(20) NULL,
        [Result] VARCHAR(20) NULL,
        [IP] VARCHAR(100) NOT NULL,
        [Reason] VARCHAR(500) NULL
    ) WITH(DATA_COMPRESSION=PAGE)

    CREATE CLUSTERED INDEX SK01_Login_Audit ON dbo.Login_Audit(LogDate) WITH(DATA_COMPRESSION=PAGE)

END


--------------------------------------------------------------
-- Importa os arquivos do ERRORLOG
--------------------------------------------------------------

INSERT INTO #Arquivos_Log
EXEC sys.sp_enumerrorlogs


--------------------------------------------------------------
-- Loop para procurar por tentativas de login
--------------------------------------------------------------

DECLARE
    @Contador INT = 0,
    @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log),
    @Ultima_Hora VARCHAR(19) = FORMAT(ISNULL((SELECT DATEADD(SECOND, 1, MAX([LogDate])) FROM dbo.Login_Audit), '1990-01-01'), 'yyyy-MM-dd HH:mm:ss'),
    @Agora VARCHAR(19) = CONVERT(VARCHAR(19), GETDATE(), 121)
    

WHILE(@Contador < @Total)
BEGIN
    

    -- Pesquisa por falha de login
    INSERT INTO #Audit_Login (LogDate, ProcessInfo, [Text]) 
    EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login failed for user ', NULL, @Ultima_Hora, @Agora


    -- Pesquisa por tentiva de login com sucesso
    INSERT INTO #Audit_Login (LogDate, ProcessInfo, [Text]) 
    EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login succeeded for user ', NULL, @Ultima_Hora, @Agora


    -- Atualiza o número do arquivo de log
    UPDATE #Audit_Login
    SET LogNumber = @Contador
    WHERE LogNumber IS NULL


    SET @Contador += 1
    
END


-------------------------------------------------------
-- Popula as colunas adicionais
-------------------------------------------------------

UPDATE [#Audit_Login]
SET
    [Username] = (CASE 
        WHEN [Text] LIKE '%Login failed%' THEN LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(REPLACE([Text], 'Login failed for user ''', ''), 1, CHARINDEX('. Reason:', REPLACE([Text], 'Login failed for user ''', '')) - 2), CHAR(10), ''), CHAR(13), '')))
        ELSE LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(REPLACE([Text], 'Login succeeded for user ''', ''), 1, CHARINDEX('''', REPLACE([Text], 'Login succeeded for user ''', '')) - 1), CHAR(10), ''), CHAR(13), '')))
    END),

    [IP] = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE((SUBSTRING([Text], CHARINDEX('[CLIENT: ', [Text]) + 9, LEN([Text]))), ']', ''), CHAR(10), ''), CHAR(13), '')))


UPDATE [#Audit_Login]
SET
    [AuthenticationType] = (CASE 
        WHEN [Text] LIKE '%Connection made using Integrated authentication.%' THEN 'Windows' 
        WHEN [Text] LIKE '%Connection made using SQL Server authentication.%' THEN 'SQL Server'
        ELSE (CASE WHEN [Username] NOT LIKE '%@%' AND [Username] NOT LIKE '%\%' THEN 'SQL Server' ELSE 'Windows' END)
    END),

    [Result] = (CASE 
        WHEN [Text] LIKE 'Login failed for user%' THEN 'Fail' 
        WHEN [Text] LIKE 'Login succeeded for user%' THEN 'Sucess' 
    END)


UPDATE [#Audit_Login]
SET
    [Reason] = (CASE 
        WHEN [Text] LIKE '%Login failed%' THEN LTRIM(RTRIM(SUBSTRING([Text], CHARINDEX('. Reason:', [Text]) + 9, LEN([Text]))))
    END)
WHERE
    [Result] = 'Fail'


UPDATE [#Audit_Login]
SET
    [Reason] = SUBSTRING([Reason], 1, CHARINDEX(' [CLIENT', [Reason]) - 1)
WHERE
    [Result] = 'Fail'
     

INSERT INTO dbo.Login_Audit
SELECT 
    [LogNumber],
    [LogDate],
    [ProcessInfo],
    [Username],
    [AuthenticationType],
    [Result],
    [IP],
    [Reason] 
FROM
    [#Audit_Login]

Resultado de la auditoría:

Con estos datos, ahora puede crear varios monitoreos, análisis, monitorear el número de inicios de sesión, verificar si está sufriendo un ataque de fuerza bruta o averiguar si un determinado usuario está iniciando sesión en la base de datos o no.

Espero que te haya gustado este consejo y ¡hasta la próxima!