SQL Server - Como evitar ataques de força bruta no seu banco de dados

Post Views 4,930 views

Neste artigo

Esse post é a parte 13 de 21 da série Security and Auditing

Reading time 15 minutes

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údo

Ataque de força bruta é a técnica mais simples e demorada para invadir sistemas e bancos de dados. Ela consiste em utilizar bases de dados de senhas para testar cada uma dessas senhas ou realizar uma verificação sistemática de todas as possíveis chaves e senhas até que uma delas consiga logar com sucesso no destino.

Esse tipo de ataque pode ser usado quando não é possível tomar vantagem de outras fraquezas em um sistema de criptografia (se existir) que tornariam a tarefa mais fácil, já que o tempo necessário para testar todas as senhas possíveis pode passar de alguns segundos (3 caracteres) para milhares de anos, de acordo com a quantidade de caracteres da senha e complexidade dos caracteres utilizados.

Ataque de força bruta no SQL Server

Clique para exibir este conteúdo

Por armazenar praticamente todos os dados de clientes e da empresa como um todo, os bancos de dados são potencialmente, um dos alvos mais buscados por invasores que tentam roubar informações ou simplesmente obter acesso privilegiado à esse banco para outro fim qualquer. Um exemplo clássico disso é tentar acessar o banco com um usuário com poderes administrativos para conseguir parar o serviço do SQL Server e assim executar um comando malicioso, como o ransomware WannaCry, por exemplo, que exige que os bancos não estejam em uso para conseguir criptografar os dados.

Para 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:

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

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údo

Agora que mostrei como ativar a auditoria de falhas de conexão e também onde e como podemos consultas essas informações, vamos começar a trabalhar com esses dados para atingir nosso objetivo: Como identificar ataque de força bruta no SQL Server.

Em 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:

EXEC master.dbo.xp_readerrorlog 0, 1, N'Login failed'

1	EXEC master.dbo.xp_readerrorlog 0, 1, N'Login failed'

Result:

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:

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

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

Result:

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):

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

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

-- 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..#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,

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

)

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

-- Import the ERRORLOG files

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

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.'

-- Update log file number

UPDATE #Login_Failed

SET LogNumber = @Contador

WHERE LogNumber IS NULL

SET @Contador += 1

END

SELECT * FROM #Login_Failed

Result:

Com esses dados, já podemos começar a identificar quais usuários e origens dos ataques aos usuários:

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

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

Result:

Important: Um ponto interessante que podemos levar em consideração, é criar uma lista de exceções com IP’s que não podem ser bloqueados, mesmo em casos onde tenham muitas conexões com falha, como por exemplo, o IP fixo da própria empresa, IP do servidor de aplicação, etc.

Como monitorar possíveis invasões por força bruta

Clique para exibir este conteúdo

Como esse assunto é de extrema importância, não dá pra contar com a sorte de você lembrar de ficar acompanhando esse tipo de evento no seu ambiente. Caso ele comece a sofrer um possível ataque, você deve agir o quanto antes e nada melhor que um alerta para te deixar ciente quando esse tipo de comportamento começar a sugir nas instâncias que você administra.

Para 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).

Caso você precise de mais informações sobre como ativar e configurar esse recurso, sugiro que leia o meu artigo SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail).

Código do monitoramento:

-- 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',
            @recipients = 'email@gmail.com',
            @subject = @Assunto,
            @body = @Mensagem,
            @body_format = 'html'


    END


END

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

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

-- 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..#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,

[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)

)

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

-- Import the ERRORLOG files

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

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

-- Update log file number

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',

@recipients = '[email protected]',

@subject = @Assunto,

@body = @Mensagem,

@body_format = 'html'

END

Result:

Como evitar possíveis invasões por força bruta

Clique para exibir este conteúdo

Chegamos agora na parte final deste artigo, onde vou abordar sobre possíveis soluções para este problema tão comum, que é o ataque de força bruta. A alternativa que é possível ser implementada no SQL Server, é criar uma lista de IPs para bloquear e executar essa lista através de algum script externo, como PowerShell ou mesmo o agendador de tarefas do Windows, para criar regras no Firewall do Windows para bloquear esses IP’s quando eles atingirem uma determinada quantidade de tentativas de conexão sem sucesso.

Para 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:

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',
                @recipients = 'email@gmail.com',
                @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

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]

IF (OBJECT_ID('dbo.stpVerifica_Falha_Conexao') IS NULL) EXEC('CREATE PROCEDURE dbo.stpVerifica_Falha_Conexao AS SELECT 1')

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

)

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

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

-- 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..#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,

[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

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

-- Import the ERRORLOG files

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

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

-- Update log file number

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',

@recipients = '[email protected]',

@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

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.

Rodrigo Storti de Oliveira disse:

January 11, 2024 às 12:01

Bom dia.

Ótimo artigo. Parabéns.
Consigo de alguma maneira identificar o HOST e AppName de falha na tentativa de login ?

Responder
Leonardo Vicentini Fiorot disse:

February 11, 2019 às 16:42

MUITO TOP!
Sempre fiquei preocupado com isso.

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server - How to avoid brute force attacks on your database

O que é ataque de força bruta (Brute force attack)

Ataque de força bruta no SQL Server

Como identificar ataque de força bruta no SQL Server

Como monitorar possíveis invasões por força bruta

Como evitar possíveis invasões por força bruta

Segurança no SQL Server – Assunto SÉRIO!

O que fazer então para resolver isso?

You may also like...

2 Responses

Deixe uma respostaCancelar resposta

O que é ataque de força bruta (Brute force attack)

Ataque de força bruta no SQL Server

Como identificar ataque de força bruta no SQL Server

Como monitorar possíveis invasões por força bruta

Como evitar possíveis invasões por força bruta

Segurança no SQL Server – Assunto SÉRIO!

O que fazer então para resolver isso?

You may also like...

SQL Server e Power BI: Como carregar dados de Stored Procedure no SQL Server com DirectQuery

SQL Server – Utilizando a SP WhoIsActive para identificar Locks, Blocks, Queries Lentas, Queries em Execução e muito mais

2º Encontro do Chapter SQL Server ES – 10/06/2017

2 Responses

Deixe uma respostaCancelar resposta