SQL Server - How to Avoid Brute Force Attacks on Your Database

Views: 4.168 views

In this article

This post is the 13 part of 20 in the series. Security and Audit

Reading Time: 15 minutes

Hey guys!
In this article today I will demonstrate how brute force attacks occur on SQL Server and how to try to defend against such attacks.

What is brute force attack

Click to view this content.

Brute force attack is the simplest and most time-consuming technique to break into systems and databases. It consists of using password databases to test each of these passwords or systematically checking all possible keys and passwords until one can successfully log in to the destination.

This type of attack can be used when it is not possible to take advantage of other weaknesses in an encryption system (if any) that would make the task easier, as the time required to test all possible passwords can be more than a few seconds (3 characters) for thousands of years, depending on the number of characters in the password and the complexity of the characters used.

SQL Server brute force attack

Click to view this content.

By storing virtually all customer and company data as a whole, databases are potentially one of the most sought after targets by attackers trying to steal information or simply gain privileged access to that bank for some other purpose. A classic example of this is trying to access the bank with a user with administrative powers to be able to stop the SQL Server service and thus execute a malicious command such as the WannaCry ransomware, which requires banks not to be in use to be able to encrypt. the data.

To be able to identify if your SQL Server instance is experiencing attacks, the first thing you have to do is enable connection failure auditing (already enabled by default), as shown in the print below:

This can also be changed using T-SQL commands:

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

Once you have made sure that login failures are already being audited, any unsuccessful connection attempts due to the incorrect password or user that do not exist will be logged into SQL Server ERRORLOG, which you can access as follows:

And then view the logs:

In addition, you can also use the Extended procedures sp_readerrorlog and xp_readerrorlog to be able to view SQL Server log contents using command line.

To learn more about how these two procedures work, I suggest you study the code I will show later in this article and read the excellent article as well. ERRORLOG - The Basics, which explains all about SQL Server ERRORLOG.

How to identify brute force attack in SQL Server

Click to view this content.

Now that I've shown how to enable connection failure auditing and also where and how we can query this information, let's start working with this data to achieve our goal: How to identify brute force attack in SQL Server.

For a basic use, let's use xp_readerrorlog to read the current log file (0) and filter out login failures:

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

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

Result:

Let's now execute this command again, but storing the return of this SP in a temporary table and identifying the IP and user of this message:

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:

If you want to analyze not only the last log file but all of it, you will need sp_enumerrorlogs to list the existing logs and thus loop through the files. I took the opportunity to further refine the search and bring only failed attempts with incorrect password and nonexistent login (there are other types, such as user without permission in the default database):

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

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

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

[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

Result:

With this data, we can now begin to identify which users and sources of user attacks:

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: An interesting point that we can take into account is to create an exception list with IP's that cannot be blocked, even in cases where there are many failed connections, such as the company's own fixed IP, application server IP, etc.

How to monitor for possible brute force invasions

Click to view this content.

As this subject is extremely important, you can not count on the luck of you remember to be following this type of event in your environment. If he begins to suffer a possible attack, you should act as soon as possible and nothing better than a warning to make you aware when this kind of behavior begins to suggest in the instances you administer.

To achieve this goal, I will use SQL Server Database Mail, which will allow us to send emails through the database when there are a certain number of connection failures and the Stored Procedure stpExports_Table_HTML_Output, which allows you to store in a variable the contents of a table in the database in the form of an HTML table, ideal for emailing.

The script below will fetch last-minute login failure events and email these logs (if they exceed the limit set for not sending too much email and turning SPAM).

If you need more information on how to enable and configure this feature, I suggest you read my article. SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).

Tracking Code:

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

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

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

[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 protected]',

@subject = @Assunto,

@body = @Mensagem,

@body_format = 'html'

END

Result:

How to Avoid Possible Brute Force Invasions

Click to view this content.

We have now reached the end of this article, where I will discuss possible solutions to this very common problem, which is brute force attack. The alternative that can be implemented in SQL Server, is to create an IP list to block and execute that list through some external script, such as PowerShell or even the Windows task scheduler, to create rules in the Windows Firewall to block these IP's when they reach a certain number of unsuccessful connection attempts.

To export the list of IP's to a file on the server, we can use several methods such as xp_cmdshell, OLE Automation or SQLCLR, but I will choose xp_cmdshell in this example, as it is the simplest to implement. Once the file has been exported in bat format with the commands to be executed, just schedule the execution of this script in the Windows task scheduler and configure the execution to use a user with Administrator permission.

Another viable alternative is to let SQL Server itself execute the commands to add the IP's to the Firewall instead of exporting a bat file with these commands. For this to be possible, the user who starts the SQL Server service must be a local Administrator of the machine, which turns out to be not so recommended from a security point of view.

An important point to be demonstrated again is about the list of exceptions, to prevent important IP's from being blocked during any test or temporary error.

To finish this theme, I will make available the complete script that:

identify the logs
categorize by IP and user
stores a history of retries
fetch connection failure occurrences since last procedure execution
implemented exception list
send alert email
generates a .bat file on the disk with commands to block the IP's in Windows Firewall with more than N connection failures

Script source code:

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

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

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

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

IP blocking script generated (Run as Administrator):

Security in SQL Server - SERIOUS MATTER!

After demonstrating some ways to mitigate the brute force invasion problem by identifying and blocking the IP's that are trying to break into the Windows Firewall environment, we need to be serious about security. Unfortunately it is very common to see totally sloppy environments in the database security where application users have sysadmin permission, users who access the database as sysadmin, users with elevated privileges and weak passwords, xp_cmdshell and dynamic query being common practices in environments and many other very worrying scenarios.

Regarding the scenario of this post, which is the brute force attack, the vast majority of cases occur because the database is published to the Internet, making it very easy for potential attackers to enter and the possibility of someone trying to attack your instance. In many cases, this occurs when the bank server is the same as the application server, which is not a good practice for either the bank or the application.

The bank ends up disputing server resources (CPU, Memory, Disk, Network) with the application and has yet to be visible and exposed to the Internet, without the possibility of creating Whitelist (only the addresses in the list have access to the server), since This would prevent users from accessing the application. Not to mention that if the application server is long, database data can be accessed as well, which is a catastrophic scenario.

Another point that should be raised, too, is that even when the brute force attack is performed on the environment, even if it is unsuccessful it still manages to damage the company because as it keeps trying the thousands of passwords to break into the bank, This process ends up consuming a lot of server resources unnecessarily.

Also, lots of ransomware, with WannaCry (which devastated the internet last year) are usually preceded by brute force attacks to gain access to the database so that the attacker can take the databases offline so that Ransonware can then encrypt the data (mdf) and log (ldf) files.

What to do then to solve this?

Well, the FIRST step for this is to make the bank invisible to the Internet. If you have to separate the bank from the application and set up 1 server for each one, do it then, creating a VPN to make the communication between the 2 servers (if they are on different networks, like Azure). If the company is unable to afford a server for each one and the application is accessed over the Internet, through several IP's and there is no possibility of making a whitelist to limit the IP's that will access the server, the way is to implement the solution I made available above and hope that the worst doesn't happen, since the script in this article will block only trying to access the bank, but there are N other types of attacks to access the server, such as RDP, SSH attacks, etc.

The second step is disable and rename user SA and any other standard SQL Server users, as these users are the main targets for brute force attacks, since the attacker already knows the login and only needs the password.

The third step is to avoid using SQL Server authentication logins as much as the bank that has to control and manage these passwords. In addition, the hash of these passwords is stored in the database and can be easily broken and validated without generating any exception in the SQL Server log, as I demonstrated in the article. SQL Server - How to identify fragile passwords, empty or equal to username. Use Windows AD authentication wherever possible, eliminating the need to enter passwords, and SQL Server has no hash or password information.

The FOURTH step is to implement the monitoring of this article and always keep an eye on this report, blocking in the Windows (or Azure) Firewall the IP's that have many connection failures. This work must be CONSTANT and not just in a timely manner. He left the database available for internet, so now he can handle it. If possible, use this list of IP's to block these addresses at the organization level, in the general firewall of the network and not just in the bank server. You can also take lists of Range of IPs by Country and try to release only the IPs of Brazil (most comprehensive Whitelist).

Step FIVE is to periodically review the password for SQL Server logins in your environment. It is very important that these users passwords be changed at least 1X PER YEAR. Also, these users' passwords must be large and complex (I always use passwords with 50 characters or more, letters, numbers, and symbols). In addition, it is important to have an annual (at least) access review policy. sysadmin is for DBA only and db_owner should not even be used (save very rare exceptions).

Well guys, that was the article about Brute Force Attack on SQL Server. I hope you enjoyed this Security line, which is an area that I should invest a lot of time studying and writing articles this year and it is the theme of my talk on MVPConf Latam 2019.

A big hug and see you in the next post.

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to Avoid Brute Force Attacks on Your Database

What is brute force attack

SQL Server brute force attack

How to identify brute force attack in SQL Server

How to monitor for possible brute force invasions

How to Avoid Possible Brute Force Invasions

Security in SQL Server - SERIOUS MATTER!

What to do then to solve this?

You may also like ...

1 Response

Leave a Comment Cancel reply

What is brute force attack

SQL Server brute force attack

How to identify brute force attack in SQL Server

How to monitor for possible brute force invasions

How to Avoid Possible Brute Force Invasions

Security in SQL Server - SERIOUS MATTER!

What to do then to solve this?

You may also like ...

SQL Server - How to use auditing to map actual required permissions on a user

SQL Server - How to calculate the difference in years (age) between two dates using T-SQL or CLR

SQL Server - Charindex: A Different Function to Break Split Strings

1 Response

Leave a Comment Cancel reply