阻止无法在 SQL Server 中登录的 IP

Question

我在一台暴露在互联网上的服务器上安装了SQL Server，由于技术原因，我无法使用VPN，几个客户端和服务器连接以访问一些数据。

我知道这是非常危险的。我唯一想到的降低风险的方法是让SQL Server监听1433(26173(以外的其他端口，尽管我知道有人可以扫描端口并找到它

所以我正试图找到一种方法来审核失败的登录并阻止远程ip，有这样的解决方案吗？

Answer 1

我创建了一个小软件来帮助处理失败的sql登录尝试，所以当它到达大量失败登录时，我可以通过电子邮件或短信收到通知，并自动阻止ip。它在VB.net中，但你可以很容易地转换为c#。这是应用程序的一部分，我使用它来对服务器执行多项维护任务。

只需在VB.NET窗体中创建一个项目并添加一个窗体。您需要一个列名称为：xText、xIp、xSameIp、xAction的DataGridView1。在数据网格中，将AllowUsersToAddRows设置为false。创建一个名为lblSQLLOGS_Saved的标签，这样，如果SQL日志中没有更改，应用程序就不会读取所有信息。

此应用程序逐行显示失败登录尝试的文本和ip，以及应执行的操作。它可以简化，但我喜欢在网格上看到正在发生的事情。我有一个定时器，每30秒滴答作响。记住在您的SQL中激活-属性-安全-登录审核-(至少(仅失败登录。这是例行程序：

Imports System.IO
Sub CheckSqlLogs()
Dim sSqlLogPathFile As String = "C:Program FilesMicrosoft SQL ServerMSSQL15.---CHANGE TO YOU REAL INSTANCE NAME----MSSQLLogERRORLOG"

'-- To avoid processing Logs that have no changes
Dim FDate As Date = IO.File.GetLastWriteTime(sSqlLogPathFile)
If FDate.ToString = lblSQLLOGS_Saved.Text Then Exit Sub ' log have no changes > exit
lblSQLLOGS_Saved.Text = FDate.ToString
' Read SQL LOG
Dim fs As New FileStream(sSqlLogPathFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
Dim sr As New StreamReader(fs)
Dim sLogText As String = sr.ReadToEnd
sr.Close()
fs.Close()
' Start Processing
DataGridView1.Rows.Clear
Dim bOneOrMoreIPStoBlock As Boolean = False
Dim iCountSameIP, k As Integer
Dim sLine, sIP As String
Dim iNewLoginFailed As Integer
Dim iLine As Integer = 0
Dim iStart As Integer = 1
Do
iNewLoginFailed = InStr(iStart, sLogText, "Login failed for user")
If iNewLoginFailed > 0 Then
sLine = Mid(sLogText, iNewLoginFailed, InStr(iNewLoginFailed, sLogText, "]") - iNewLoginFailed + 1)
sIP = Mid(sLine.Replace("]", ""), InStr(sLine, "[CLIENT: ") + Len("[CLIENT: "), 100)
iStart = iNewLoginFailed
DataGridView1.Rows.Add()
DataGridView1.Rows(iLine).Cells("xText").Value = sLine
DataGridView1.Rows(iLine).Cells("xIp").Value = sIP
' We don't want to block 10 times the same IP, here we choose 3 failed logins for the same ip to block it just once
' You can put this section after the Loop While but you'll need it if you want to block the ip right away
iCountSameIP = 0
For k = 0 To DataGridView1.Rows.Count - 1
If DataGridView1.Rows(k).Cells("xIp").Value.ToString = sIP Then iCountSameIP += 1
Next
DataGridView1.Rows(iLine).Cells("xSameIP").Value = iCountSameIP
If iCountSameIP = 3 Then ' 3 failed (same ip) = block, you can change to 1
DataGridView1.Rows(iLine).Cells("xAction").Value = "BLOCK"
bOneOrMoreIPStoBlock = True
' you can block the IP at this point, in this example we do it later
' just get the ip from the var sIP
End If
End If
iLine += 1
Loop While iNewLoginFailed > 0 AndAlso iLine <= 5000
If bOneOrMoreIPStoBlock Then
For k = 0 To DataGridView1.Rows.Count - 1
If DataGridView1.Rows(k).Cells("xAction").Value = "BLOCK" Then
' BLOCK THIS IP
sIP = DataGridView1.Rows(k).Cells("xIp").Value.ToString)
MsgBox("BLOCK IP: " & DataGridView1.Rows(k).Cells("xIp").Value.ToString)
' I use this next lines to block the IP, just uncomment it in production mode
'Dim sProcess As String = "netsh advfirewall firewall add rule name=""BlockIP_" & sIP _
'& """ dir=in interface=any action=block remoteip=" & sIP & "/32"
'Shell(sProcess, AppWinStyle.Hide)
End If
Next
End If
End Sub

Answer 2

在Linux上，我会使用fail2ban，似乎有一个windows版本可以研究。