SQL Server - Como criar uma auditoria de logins utilizando os logs da instância

Post Views 1,096 views

This post is part 21 of 21 in the series Security and Auditing

Reading time 4 minutes

Hey guys!
In this post, I would like to show you how to create a login audit using instance logs, which is a native SQL Server option and does not require the creation of any additional resources such as trigger, Extended Events, Audit, etc..

I already published the article SQL Server - How to implement login auditing and control (Logon Trigger) using a trigger and it turned out to be a very interesting solution, but which, like all login triggers, can pose some risks if it is not well implemented and prevent user logins.

I also showed a similar solution in the article SQL Server - How to avoid brute force attacks on your database, but the focus was only connection failures by nonexistent user or incorrect password.

How to enable auditing of logins in SQL Server

To activate auditing of SQL Server logins, first open the instance in the “Object Explorer”, right-click and select the “Properties” option

Click on the “Security” tab and then in the “Login Auditing” category, check the option “Both failed and successful logins”

Another way to enable this is using this T-SQL script:

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

USE [master]

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3

After making this change, restart the SQL Server service through SQL Server Configuration Manager.

Important: In case the SQL Server Configuration Manager is not appearing for you in the start menu, read the article SQL Server Configuration Manager gone? Learn how to retrieve the shortcut.

How to read collected audit data

To read the collected audit data, we will use the internal procedure xp_readerrorlog, which reads data from SQL Server logs.

To learn more about this procedure and other internal SQL Server procedures, read the article The Undocumented SQL Server Extended Procedures.

Below is the script you can use to capture this data from SQL Server logs and store it in a physical table in your database, to keep history.

--------------------------------------------------------------
-- 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]

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

--------------------------------------------------------------

-- Create the temporary tables

--------------------------------------------------------------

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

--------------------------------------------------------------

-- Import the ERRORLOG files

--------------------------------------------------------------

INSERT INTO #Arquivos_Log

EXEC sys.sp_enumerrorlogs

--------------------------------------------------------------

-- loop to look for login attempts

--------------------------------------------------------------

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

-- Login failure search

INSERT INTO #Audit_Login (LogDate, ProcessInfo, [Text])

EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login failed for user ', NULL, @Ultima_Hora, @Agora

-- Search for successful login attempt

INSERT INTO #Audit_Login (LogDate, ProcessInfo, [Text])

EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login succeeded for user ', NULL, @Ultima_Hora, @Agora

-- Update log file number

UPDATE #Audit_Login

SET LogNumber = @Contador

WHERE LogNumber IS NULL

SET @Contador += 1

END

-------------------------------------------------------

-- Populate additional columns

-------------------------------------------------------

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:

With this data, you can now create various monitoring, analyses, follow the number of logins, check if you are having a brute force attack or know if a certain user is logging in or not in the database.

I hope you enjoyed this tip and until next time!

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server - How to create a login audit using instance logs

How to enable auditing of logins in SQL Server

How to read collected audit data

You may also like...

Deixe uma respostaCancelar resposta

How to enable auditing of logins in SQL Server

How to read collected audit data

You may also like...

SQL Server – The activated proc ‘[dbo].[sp_syspolicy_events_reader]’ running on queue ‘msdb.dbo.syspolicy_event_queue’

Consultando o rastreamento de objetos dos Correios pelo SQL Server

SQL Server – Como calcular a distância entre dois pontos utilizando a API do Google (CEP, endereço ou latitute e longitude)

Deixe uma respostaCancelar resposta