- 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 galera!
Nesse artigo de hoje vou demonstrar como ocorrem ataques de força bruta ao SQL Server e como tentar se defender desse tipo de ataque.
O que é ataque de força bruta (Brute force attack)
Clique para exibir este conteúdoAtaque de força bruta no SQL Server
Clique para exibir este conteúdoPara que seja possível identificar se a sua instância SQL Server está sofrendo ataques, a primeira coisa que você deve fazer é ativar a auditoria de falhas de conexão (já vem ativado por padrão), como demonstrado no print abaixo:
Isso também pode ser alterado utilizando comandos T-SQL:
|
1 2 3 4 5 6 |
EXEC sys.xp_instance_regwrite @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', @value_name = 'AuditLevel', @type = 'REG_DWORD', @value = 2 -- 0 = Nenhum / 1 = Apenas sucesso / 2 = Apenas falha / 3 = Sucesso e Falha |
Uma vez que você se certificou que falhas de logins já estão sendo auditadas, qualquer tentativa de conexão que seja feita sem sucesso, devido à senha incorreta ou usuário que não existe, será logada no ERRORLOG do SQL Server, que você pode acessar assim:
E depois visualizar os registros:
Além disso, você também pode utilizar as Extended Procedures sp_readerrorlog e xp_readerrorlog para conseguir visualizar o conteúdo do log do SQL Server utilizando linha de comando.
Para saber mais sobre o funcionamento dessas duas procedures, sugiro que estude o código que vou mostrar ainda nesse artigo e leia também o excelente artigo ERRORLOG – O básico, que explica tudo sobre o ERRORLOG do SQL Server.
Como identificar ataque de força bruta no SQL Server
Clique para exibir este conteúdoEm uma utilização básica, vamos utilizar a xp_readerrorlog para ler o arquivo de log atual (0) e filtrar no log as falhas de login:
|
1 |
EXEC master.dbo.xp_readerrorlog 0, 1, N'Login failed' |
Resultado:
Vamos agora executar novamente esse comando, mas armazenando o retorno essa SP numa tabela temporária e identificando o IP e o usuário dessa mensagem:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
IF (OBJECT_ID('tempdb..#Login_Failed') IS NOT NULL) DROP TABLE #Login_Failed CREATE TABLE #Login_Failed ( [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI, [Username] AS 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), ''))), [IP] AS LTRIM(RTRIM(REPLACE(REPLACE(REPLACE((SUBSTRING([Text], CHARINDEX('[CLIENT: ', [Text]) + 9, LEN([Text]))), ']', ''), CHAR(10), ''), CHAR(13), ''))) ) INSERT INTO #Login_Failed (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog 0, 1, N'Login failed' SELECT * FROM #Login_Failed |
Resultado:
Caso você queira analisar não somente o último arquivo de log, mas sim todos eles, você precisará da sp_enumerrorlogs, para listar os logs existentes e assim montar o seu loop entre os arquivos. Aproveitei para refinar mais a buscar e trazer apenas tentativas com falha por senha incorreta e login inexistente (existem outros tipos, como usuário sem permissão no database padrão):
|
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 |
-------------------------------------------------------------- -- 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..#Login_Failed') IS NOT NULL) DROP TABLE #Login_Failed CREATE TABLE #Login_Failed ( [LogNumber] TINYINT, [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI, [Username] AS 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), ''))), [IP] AS LTRIM(RTRIM(REPLACE(REPLACE(REPLACE((SUBSTRING([Text], CHARINDEX('[CLIENT: ', [Text]) + 9, LEN([Text]))), ']', ''), CHAR(10), ''), CHAR(13), ''))) ) -------------------------------------------------------------- -- Importa os arquivos do ERRORLOG -------------------------------------------------------------- INSERT INTO #Arquivos_Log EXEC sys.sp_enumerrorlogs -------------------------------------------------------------- -- Loop para procurar por falhas de login nos arquivos -------------------------------------------------------------- DECLARE @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log) WHILE(@Contador < @Total) BEGIN -- Pesquisa por senha incorreta INSERT INTO #Login_Failed (LogDate, ProcessInfo, [Text]) EXEC master.dbo.sp_readerrorlog @Contador, 1, N'Password did not match that for the login provided' -- Pesquisa por tentar conectar com usuário que não existe INSERT INTO #Login_Failed (LogDate, ProcessInfo, [Text]) EXEC master.dbo.sp_readerrorlog @Contador, 1, N'Could not find a login matching the name provided.' -- Atualiza o número do arquivo de log UPDATE #Login_Failed SET LogNumber = @Contador WHERE LogNumber IS NULL SET @Contador += 1 END SELECT * FROM #Login_Failed |
Resultado:
Com esses dados, já podemos começar a identificar quais usuários e origens dos ataques aos usuários:
|
1 2 3 4 5 6 7 8 9 |
SELECT [IP], COUNT(*) AS Quantidade FROM #Login_Failed GROUP BY [IP] ORDER BY 2 DESC SELECT [Username], COUNT(*) AS Quantidade FROM #Login_Failed GROUP BY [Username] ORDER BY 2 DESC |
Resultado:
Como monitorar possíveis invasões por força bruta
Clique para exibir este conteúdoPara atingir esse objetivo, vou utilizar o Database Mail do SQL Server, que vai nos permitir enviar e-mails pelo banco de dados quando houverem um determinado número de falhas de conexão e a Stored Procedure stpExporta_Tabela_HTML_Output, que permite armazenar em uma variável, o conteúdo de uma tabela no banco de dados no formato de uma tabela HTML, ideal para enviar em e-mail.
O script abaixo vai buscar os eventos de falha de login da última hora e enviar esses registros por e-mail (caso ultrapassem o limite definido para não enviar muito e-mail e virar SPAM).
Código do monitoramento:
|
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 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 |
-- Configurações DECLARE @Qt_Tentativas_Para_Alertar INT = 10, @Fl_Envia_Email BIT = 1 -------------------------------------------------------------- -- 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..#Login_Failed') IS NOT NULL) DROP TABLE #Login_Failed CREATE TABLE #Login_Failed ( [LogNumber] TINYINT, [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI, [Username] AS 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), ''))), [IP] AS LTRIM(RTRIM(REPLACE(REPLACE(REPLACE((SUBSTRING([Text], CHARINDEX('[CLIENT: ', [Text]) + 9, LEN([Text]))), ']', ''), CHAR(10), ''), CHAR(13), ''))) ) IF (OBJECT_ID('tempdb..##Tentativas_Conexao') IS NOT NULL) DROP TABLE ##Tentativas_Conexao CREATE TABLE ##Tentativas_Conexao ( [LogNumber] TINYINT, [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50), [Text] NVARCHAR(MAX), [Username] NVARCHAR(256), [IP] NVARCHAR(50) ) IF (OBJECT_ID('tempdb..##Tentativas_Conexao_Por_IP') IS NOT NULL) DROP TABLE ##Tentativas_Conexao_Por_IP CREATE TABLE ##Tentativas_Conexao_Por_IP ( [IP] NVARCHAR(256), Qt_Tentativas INT ) IF (OBJECT_ID('tempdb..##Tentativas_Conexao_Por_Usuario') IS NOT NULL) DROP TABLE ##Tentativas_Conexao_Por_Usuario CREATE TABLE ##Tentativas_Conexao_Por_Usuario ( [Username] NVARCHAR(256), Qt_Tentativas INT ) IF (OBJECT_ID('tempdb..##Lista_IPs_Bloquear') IS NOT NULL) DROP TABLE ##Lista_IPs_Bloquear CREATE TABLE ##Lista_IPs_Bloquear ( [Lista_IPs] VARCHAR(MAX) ) -------------------------------------------------------------- -- Importa os arquivos do ERRORLOG -------------------------------------------------------------- INSERT INTO #Arquivos_Log EXEC sys.sp_enumerrorlogs -------------------------------------------------------------- -- Loop para procurar por falhas de login nos arquivos -------------------------------------------------------------- DECLARE @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log), @Ultima_Hora VARCHAR(19) = FORMAT(DATEADD(HOUR, -1, GETDATE()), 'yyyy-MM-dd HH:mm:00'), @Agora VARCHAR(19) = CONVERT(VARCHAR(19), GETDATE(), 121) WHILE(@Contador < @Total) BEGIN -- Pesquisa por senha incorreta INSERT INTO #Login_Failed (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Password did not match that for the login provided', NULL, @Ultima_Hora, @Agora -- Pesquisa por tentar conectar com usuário que não existe INSERT INTO #Login_Failed (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Could not find a login matching the name provided.', NULL, @Ultima_Hora, @Agora -- Atualiza o número do arquivo de log UPDATE #Login_Failed SET LogNumber = @Contador WHERE LogNumber IS NULL SET @Contador += 1 END -------------------------------------------------------------- -- Salva as tentativas realizadas, já excluindo a lista de exceções -------------------------------------------------------------- INSERT INTO ##Tentativas_Conexao SELECT A.* FROM #Login_Failed A WHERE A.[IP] NOT LIKE '%local machine%' ORDER BY A.LogDate INSERT INTO ##Tentativas_Conexao_Por_IP SELECT [IP], COUNT(*) AS Quantidade FROM ##Tentativas_Conexao GROUP BY [IP] ORDER BY 2 DESC INSERT INTO ##Tentativas_Conexao_Por_Usuario SELECT [Username], COUNT(*) AS Quantidade FROM ##Tentativas_Conexao GROUP BY [Username] ORDER BY 2 DESC INSERT INTO ##Lista_IPs_Bloquear SELECT STUFF(( SELECT ',' + [IP] FROM ##Tentativas_Conexao_Por_IP ORDER BY [IP] FOR XML PATH('') ), 1, 1, '') AS listaIps IF ((SELECT COUNT(*) FROM ##Tentativas_Conexao) > 0) BEGIN IF (@Fl_Envia_Email = 1 AND (SELECT COUNT(*) FROM ##Tentativas_Conexao) > @Qt_Tentativas_Para_Alertar) BEGIN DECLARE @Assunto VARCHAR(200) = '[' + @@SERVERNAME + '] - Tentativas de conexão sem sucesso', @Mensagem VARCHAR(MAX) = 'Olá,<br/>Seguem logs de tentativas de conexão sem sucesso na instância ' + @@SERVERNAME + ':', @HTML VARCHAR(MAX) -------------------------------------------------------------- -- Gera o código HTML para enviar por e-mail -- https://dirceuresende.com/blog/como-exportar-dados-de-uma-tabela-do-sql-server-para-html/ -------------------------------------------------------------- EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Tentativas_Conexao', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Histórico do Log</h2>' + @HTML EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Tentativas_Conexao_Por_IP', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Acessos por IP</h2>' + @HTML EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Tentativas_Conexao_Por_Usuario', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Acessos por Usuário</h2>' + @HTML EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Lista_IPs_Bloquear', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Lista de IPs para Bloquear</h2>' + @HTML -------------------------------------------------------------- -- Envia o e-mail -- https://dirceuresende.com/blog/como-habilitar-enviar-monitorar-emails-pelo-sql-server-sp_send_dbmail/ -------------------------------------------------------------- EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile DirceuResende', @subject = @Assunto, @body = @Mensagem, @body_format = 'html' END END |
Resultado:
Como evitar possíveis invasões por força bruta
Clique para exibir este conteúdoPara exportar a lista de IP’s para um arquivo no servidor, podemos utilizar diversos método como xp_cmdshell, OLE Automation ou SQLCLR, mas vou optar por xp_cmdshell nesse exemplo, por ser o mais simples de implementar. Uma vez que o arquivo foi exportado no formato bat já com o comandos a serem executados, basta agendar a execução desse script no agendador de tarefas do Windows e configurar a execução para utilizar um usuário com permissão de Administrador.
Outra alternativa viável é deixar que o próprio SQL Server já execute os comandos para adicionar os IP’s no Firewall ao invés de exportar um arquivo bat com esses comandos. Para que isso seja possível, o usuário que inicia o serviço do SQL Server deve ser Administrador local da máquina, o que acaba não sendo algo tão recomendável do ponto de vista de segurança.
Um ponto importante a ser demonstrado de novo é sobre a lista de exceções, para evitar que IP’s importantes sejam bloqueados durante algum teste ou erro temporário.
Para finalizar esse tema, vou disponibilizar o script completo que:
- identifica os logs
- categoriza por IP e usuário
- armazena um histórico das tentativas
- busca as ocorrências de falha de conexão desde a última execução da Procedure
- lista de exeções implementada
- envia e-mail de alerta
- gera um arquivo .bat no disco com os comandos para bloquear no Windows Firewall os IP’s com mais de N falhas de conexão
Código-fonte do script:
|
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 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 |
USE [dirceuresende] GO IF (OBJECT_ID('dbo.stpVerifica_Falha_Conexao') IS NULL) EXEC('CREATE PROCEDURE dbo.stpVerifica_Falha_Conexao AS SELECT 1') GO ALTER PROCEDURE dbo.stpVerifica_Falha_Conexao ( @Fl_Envia_Email BIT = 1, @Qt_Tentativas_Para_Alertar INT = 100, @Fl_Gera_Arquivo_Firewall BIT = 1, @Qt_Tentativas_para_Bloquear INT = 5 ) AS BEGIN SET NOCOUNT ON -- DECLARE @Qt_Tentativas_Para_Alertar INT = 100, @Fl_Envia_Email BIT = 1, @Fl_Gera_Arquivo_Firewall BIT = 1, @Qt_Tentativas_para_Bloquear INT = 5 -------------------------------------------------------------- -- 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..#Login_Failed') IS NOT NULL) DROP TABLE #Login_Failed CREATE TABLE #Login_Failed ( [LogNumber] TINYINT, [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI, [Username] AS 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), ''))), [IP] AS LTRIM(RTRIM(REPLACE(REPLACE(REPLACE((SUBSTRING([Text], CHARINDEX('[CLIENT: ', [Text]) + 9, LEN([Text]))), ']', ''), CHAR(10), ''), CHAR(13), ''))) ) IF (OBJECT_ID('tempdb..##Tentativas_Conexao') IS NOT NULL) DROP TABLE ##Tentativas_Conexao CREATE TABLE ##Tentativas_Conexao ( [LogNumber] TINYINT, [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50), [Text] NVARCHAR(MAX), [Username] NVARCHAR(256), [IP] NVARCHAR(50) ) IF (OBJECT_ID('tempdb..##Tentativas_Conexao_Por_IP') IS NOT NULL) DROP TABLE ##Tentativas_Conexao_Por_IP CREATE TABLE ##Tentativas_Conexao_Por_IP ( [IP] NVARCHAR(256), Qt_Tentativas INT ) IF (OBJECT_ID('tempdb..##Tentativas_Conexao_Por_Usuario') IS NOT NULL) DROP TABLE ##Tentativas_Conexao_Por_Usuario CREATE TABLE ##Tentativas_Conexao_Por_Usuario ( [Username] NVARCHAR(256), Qt_Tentativas INT ) IF (OBJECT_ID('tempdb..##Lista_IPs_Bloquear') IS NOT NULL) DROP TABLE ##Lista_IPs_Bloquear CREATE TABLE ##Lista_IPs_Bloquear ( [Lista_IPs] VARCHAR(MAX) ) IF (OBJECT_ID('tempdb..#Bloquear_IP') IS NOT NULL) DROP TABLE #Bloquear_IP CREATE TABLE #Bloquear_IP ( Contador INT IDENTITY(1,1) NOT NULL, [IP] NVARCHAR(256), Qt_Tentativas INT ) -------------------------------------------------------------- -- Lista com IP's permitidos que não podem ser bloqueados -------------------------------------------------------------- IF (OBJECT_ID('dbo.Excecoes') IS NULL) BEGIN -- DROP TABLE dbo.Excecoes CREATE TABLE dbo.Excecoes ( [IP] VARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL UNIQUE ) WITH(DATA_COMPRESSION=PAGE) INSERT INTO dbo.Excecoes VALUES ('192.168.31.108'), ('127.0.0.1') END -------------------------------------------------------------- -- Histórico das tentativas de conexão -------------------------------------------------------------- IF (OBJECT_ID('dbo.Tentativas_Conexao') IS NULL) BEGIN -- TRUNCATE TABLE dbo.Tentativas_Conexao CREATE TABLE dbo.Tentativas_Conexao ( [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Username] NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CI_AI, [IP] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI ) WITH(DATA_COMPRESSION=PAGE) CREATE CLUSTERED INDEX SK01_Tentativas_Conexao ON dbo.Tentativas_Conexao(LogDate) WITH(DATA_COMPRESSION=PAGE, FILLFACTOR=100) END -------------------------------------------------------------- -- Importa os arquivos do ERRORLOG -------------------------------------------------------------- INSERT INTO #Arquivos_Log EXEC sys.sp_enumerrorlogs -------------------------------------------------------------- -- Loop para procurar por falhas de login nos arquivos -------------------------------------------------------------- DECLARE @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log), @Ultima_Coleta VARCHAR(19) = CONVERT(VARCHAR(19), ISNULL(DATEADD(SECOND, 1, (SELECT MAX(LogDate) FROM dbo.Tentativas_Conexao)), '1900-01-01'), 121), @Agora VARCHAR(19) = CONVERT(VARCHAR(19), GETDATE(), 121), @IP VARCHAR(20), @Query VARCHAR(4000) WHILE(@Contador < @Total) BEGIN -- Pesquisa por senha incorreta INSERT INTO #Login_Failed (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Password did not match that for the login provided', NULL, @Ultima_Coleta, @Agora -- Pesquisa por tentar conectar com usuário que não existe INSERT INTO #Login_Failed (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Could not find a login matching the name provided.', NULL, @Ultima_Coleta, @Agora -- Atualiza o número do arquivo de log UPDATE #Login_Failed SET LogNumber = @Contador WHERE LogNumber IS NULL SET @Contador += 1 END -------------------------------------------------------------- -- Salva as tentativas realizadas, já excluindo a lista de exceções -------------------------------------------------------------- INSERT INTO ##Tentativas_Conexao SELECT A.* FROM #Login_Failed A LEFT JOIN dbo.Excecoes B ON B.[IP] = A.[IP] COLLATE SQL_Latin1_General_CP1_CI_AI WHERE (B.[IP] IS NULL AND A.[IP] NOT LIKE '%local machine%') ORDER BY A.LogDate INSERT INTO ##Tentativas_Conexao_Por_IP SELECT [IP], COUNT(*) AS Quantidade FROM ##Tentativas_Conexao GROUP BY [IP] ORDER BY 2 DESC INSERT INTO ##Tentativas_Conexao_Por_Usuario SELECT [Username], COUNT(*) AS Quantidade FROM ##Tentativas_Conexao GROUP BY [Username] ORDER BY 2 DESC INSERT INTO #Bloquear_IP SELECT A.[IP], COUNT(*) AS Quantidade FROM ##Tentativas_Conexao A LEFT JOIN dbo.Tentativas_Conexao B ON B.[IP] = A.[IP] COLLATE SQL_Latin1_General_CP1_CI_AI WHERE B.[IP] IS NULL GROUP BY A.[IP] HAVING COUNT(*) >= @Qt_Tentativas_para_Bloquear ORDER BY 2 DESC INSERT INTO ##Lista_IPs_Bloquear SELECT STUFF(( SELECT ',' + [IP] FROM #Bloquear_IP ORDER BY [IP] FOR XML PATH('') ), 1, 1, '') AS listaIps -------------------------------------------------------------- -- Armazena o histórico -------------------------------------------------------------- INSERT INTO dbo.Tentativas_Conexao ( LogDate, ProcessInfo, Username, [IP] ) SELECT LogDate, (CASE WHEN [Text] LIKE '%password%' THEN 'Password failed' WHEN [Text] LIKE '%Could not find a login matching the name provided%' THEN 'Login does not exists' END) AS ProcessInfo, Username, [IP] FROM ##Tentativas_Conexao IF ((SELECT COUNT(*) FROM ##Tentativas_Conexao) > 0) BEGIN IF (@Fl_Envia_Email = 1 AND (SELECT COUNT(*) FROM ##Tentativas_Conexao) > @Qt_Tentativas_Para_Alertar) BEGIN DECLARE @Assunto VARCHAR(200) = '[' + @@SERVERNAME + '] - Tentativas de conexão sem sucesso', @Mensagem VARCHAR(MAX) = 'Olá,<br/>Seguem logs de tentativas de conexão sem sucesso na instância ' + @@SERVERNAME + ':', @HTML VARCHAR(MAX) -------------------------------------------------------------- -- Gera o código HTML para enviar por e-mail -- https://dirceuresende.com/blog/como-exportar-dados-de-uma-tabela-do-sql-server-para-html/ -------------------------------------------------------------- EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Tentativas_Conexao', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Histórico do Log</h2>' + @HTML EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Tentativas_Conexao_Por_IP', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Acessos por IP</h2>' + @HTML EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Tentativas_Conexao_Por_Usuario', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Acessos por Usuário</h2>' + @HTML EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Lista_IPs_Bloquear', -- varchar(max) @Ds_Saida = @HTML OUT -- varchar(max) SET @Mensagem += '<br/><br/><h2>Lista de IPs para Bloquear</h2>' + @HTML -------------------------------------------------------------- -- Envia o e-mail -- https://dirceuresende.com/blog/como-habilitar-enviar-monitorar-emails-pelo-sql-server-sp_send_dbmail/ -------------------------------------------------------------- EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile DirceuResende', @subject = @Assunto, @body = @Mensagem, @body_format = 'html' END -------------------------------------------------------------- -- Bloqueia os IP's no Firewall do Windows -- https://dirceuresende.com/blog/como-instalar-e-configurar-o-microsoft-sql-server-2016-no-windows-server-2016/ -------------------------------------------------------------- IF (@Fl_Gera_Arquivo_Firewall = 1) BEGIN DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell') IF (@Fl_Xp_CmdShell_Ativado = 0) BEGIN EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE; END SET @Contador = 1 SET @Total = (SELECT COUNT(*) FROM #Bloquear_IP) -- Apaga o arquivo EXEC master.dbo.xp_cmdshell 'type nul > "C:\Temporario\Firewall.bat"' WHILE(@Contador <= @Total) BEGIN SELECT TOP(1) @IP = [IP] FROM #Bloquear_IP WHERE Contador = @Contador SET @Query = 'ECHO netsh advfirewall firewall add rule name="SQL Server - IP Block - ' + @IP + '" dir=in interface=any action=block remoteip=' + @IP + '/32 >> "C:\Temporario\Firewall.bat' EXEC master.dbo.xp_cmdshell @Query --PRINT @Query SET @Contador += 1 END IF (@Fl_Xp_CmdShell_Ativado = 0) BEGIN EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE; EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; END END END END |
Script de bloqueio de IP’s gerado (Executar como Administrador):
Segurança no SQL Server – Assunto SÉRIO!
Após demonstrar algumas formas de atenuar o problema de invasão por força bruta identificando e bloqueando os IP’s que estão tentando invadir o ambiente no Windows Firewall, precisamos falar sério sobre Segurança. Infelizmente é muito comum ver ambientes totalmente desleixados no quesito segurança do banco de dados, onde usuários de aplicação possuem permissão de sysadmin, usuários que acessam o banco também como sysadmin, usuários com privilégios elevados e senhas frágeis, xp_cmdshell e query dinâmica sendo práticas comuns nos ambientes e muitos outros cenários bem preocupantes.
Sobre o cenário desse post, que é o ataque de força bruta, a grande maioria dos casos ocorre pelo fato do banco de dados estar publicado para Internet, facilitando bastante a entrada de possíveis invasores e a possibilidade de alguém tentar atacar a sua instância. Em muitos casos, isso ocorre quando o servidor do banco é o mesmo da aplicação, o que não é uma boa prática nem para o banco nem para a aplicação.
O banco acaba disputando recursos do servidor (CPU, Memória, Disco, Rede) com a aplicação e ainda tem que ficar visível e exposto para Internet, sem a possibilidade da criação de Whitelist (somente os endereços da lista possuem acesso ao servidor), já que isso iria impedir que os usuários acessem a aplicação. Isso sem falar que se o servidor da aplicação for compretido, os dados do banco podem ser acessados também, o que é um cenário catastrófico.
Um outro ponto que deve ser levantado também, é que mesmo quando o ataque de força bruta é realizado no ambiente, mesmo que ele não tenha sucesso ele ainda consegue prejudicar a empresa, pois enquanto ele fica tentando as milhares de senhas para invadir o banco, esse processo acaba consumindo muitos recursos do servidor de forma desnecessária.
Além disso, muitos ransomwares, com o WannaCry (o que devastou a internet ano passado) geralmente são precedidos por ataques de força bruta para conseguir acesso ao banco de dados de modo que o atacante consiga colocar os bancos offline para que o Ransonware depois consiga criptografar os arquivos de dados (mdf) e log (ldf).
O que fazer então para resolver isso?
Bom, o PRIMEIRO passo para isso é deixar o banco invisível para a Internet. Se tiver que separar o banco da aplicação e montar 1 servidor para cada um, que o faça então, criando uma VPN para realizar a comunição entre os 2 servidores (caso estejam em redes diferentes, como o Azure). Se a empresa não tiver condições de bancar um servidor para cada um e a aplicação for acessada pela Internet, por vários IP’s e não existe possibilidade de fazer uma whitelist para limitar os IP’s que vão acessar o servidor, o jeito é implementar a solução que disponibilizei acima e torcer para não acontecer o pior, já que o script desse artigo vai bloquear apenas tentar de acesso ao banco, mas existem N outros tipos de ataques para acessar o servidor, como ataques RDP, SSH, etc..
O SEGUNDO passo é desativar e renomear o usuário SA e qualquer outro usuário padrão do SQL Server, já que esses usuários são os principais alvos de ataques por força bruta, uma vez que o invasor já sabe o login e precisa apenas da senha.
O TERCEIRO passo é evitar ao máximo a utilização de logins com autenticação SQL Server, pois o banco que tem que controlar e gerenciar essas senhas. Além disso, o hash dessas senhas fica armazenado no banco e pode ser facilmente quebrado e validado sem gerar nem exceção no log do SQL Server, conforme demonstrei no artigo SQL Server – Como identificar senhas frágeis, vazias ou iguais ao nome do usuário. Utilize sempre que possível, autenticação do Windows AD, eliminando a necessidade de digitar senhas e o SQL Server não tem informações do hash e nem da senha.
O QUARTO passo é implementar o monitoramento desse artigo e ficar sempre de olho nesse relatório, bloqueando no Firewall do Windows (ou do Azure) os IP’s que estão com muitas falhas de conexão. Esse trabalho deve ser CONSTANTE e não só de forma pontual. Deixou o banco de dados disponível para internet, então agora aguenta. Se possível, utilizar essa lista de IP’s para bloquear esses endereços a nível de organização, no Firewall geral da rede e não apenas no servidor de banco. Você também pode pegar listas de Range de IPs por País e tentar liberar apenas os IPs do Brasil (Whitelist mais abrangente).
O QUINTO passo é revisar periodicamente a senha dos logins SQL Server do seu ambiente. É muito importante que as senhas desses usuários seja alterada PELO MENOS 1X POR ANO. Além disso, as senhas desses usuários devem ser grandes e complexas (eu utilizo sempre senhas com 50 caracteres ou mais, sendo letras, números e símbolos). Além disso, é importante ter uma política anual (pelo menos) de revisão de acessos. sysadmin é só pra DBA e db_owner nem deveria ser usado (salve raríssimas exceções).
Bom pessoal, esse foi o artigo sobre Ataque de força bruta no SQL Server. Espero que vocês tenham gostado dessa linha de Segurança, que é uma área que devo investir bastante tempo estudando e escrevendo artigos esse ano e é o tema da minha palestra no MVPConf Latam 2019.
Um grande abraço e até o próximo post.
Portuguese 
English
Bom dia.
Ótimo artigo. Parabéns.
Consigo de alguma maneira identificar o HOST e AppName de falha na tentativa de login ?
MUITO TOP!
Sempre fiquei preocupado com isso.