Fala pessoal!
Neste post, eu gostaria de mostrar para vocês como criar uma auditoria de logins utilizando os logs da instância, que é uma opção nativa do SQL Server e não exige a criação de nenhum recurso adicional como trigger, Extended Events, Auditoria, etc..

Eu já havia publicado o artigo SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon) utilizando uma trigger e ficou uma solução bem interessante, mas que, como toda trigger de logon, pode oferecer alguns riscos caso não seja bem implementada e impedir os logins dos usuários.

Também mostrei uma solução parecida no artigo SQL Server – Como evitar ataques de força bruta no seu banco de dados, mas o foco era apenas falhas de conexão por usuário inexistente ou senha incorreta.

Como ativar a auditoria de logins no SQL Server

Para ativar a auditoria de logins no SQL Server, primeiro você abra a instância no “Object Explorer”, clique com o botão direito e selecione a opção “Properties”

Clique na aba “Security” e depois na categoria “Login Auditing”, marque a opção “Both failed and successful logins”

Outra forma de ativar isso é utilizando esse 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

Após realizar essa alteração, reinicie o serviço do SQL Server pelo SQL Server Configuration Manager.

Importante: Caso o SQL Server Configuration Manager não esteja aparecendo para você no menu iniciar, leia o artigo SQL Server Configuration Manager sumiu? Saiba como recuperar o atalho.

Como ler os dados de auditoria coletados

Para ler os dados de auditoria coletados, vamos utilizar a procedure interna xp_readerrorlog, que faz a leitura dos dados que estão nos logs do SQL Server.

Para saber mais sobre essa procedure e outras procedures internas do SQL Server, leia o artigo As procedures estendidas não documentadas do SQL Server.

Segue abaixo o script que você pode utilizar para capturar esses dados dos logs do SQL Server e armazenar em uma tabela física do seu banco de dados, para manter histórico.

--------------------------------------------------------------
-- 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 da auditoria:

Com esses dados, você já pode criar vários monitoramentos, análises, acompanhar a quantidade de logins, verificar se está tendo algum ataque de força bruta ou saber se determinado usuário está logando ou não no banco de dados.

Espero que tenham gostado dessa dica e até a próxima!