- Auditoria no SQL Server (Server Audit)
- Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
- Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
- SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
- Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
- Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
- SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
- SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
- SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
- SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
- SQL Server – Trigger para prevenir e impedir alterações em tabelas
- SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
- SQL Server – Como evitar ataques de força bruta no seu banco de dados
- SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
- SQL Server – Como saber a data do último login de um usuário
- SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
- SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
- SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
- SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
- SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
- SQL Server – Como criar uma auditoria de logins utilizando os logs da instância
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:
|
1 2 3 4 |
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.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
-------------------------------------------------------------- -- 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!
Portuguese 
English