摘要：腳本功能說明自動(dòng)刪除重名數(shù)據(jù)庫，然后創(chuàng)建自動(dòng)創(chuàng)建登陸管理表自動(dòng)刪除重名的登陸觸發(fā)器，然后重新創(chuàng)建。如果身份驗(yàn)證失敗，將不激發(fā)登錄觸發(fā)器。如果由其他例程調(diào)用即使這些例程由或登錄觸發(fā)器進(jìn)行調(diào)用，將返回。
查看數(shù)據(jù)庫日志發(fā)現(xiàn)好多非法登陸失敗的記錄，雖然之前已經(jīng)更改了服務(wù)器和數(shù)據(jù)庫的管理員賬戶的用戶名，但是為了保險(xiǎn)起見，還是再加上一道措施。創(chuàng)建登錄觸發(fā)器，只允許指定的IP訪問，服務(wù)器設(shè)置白名單，但是這只適用于訪問用戶的IP固定不會(huì)經(jīng)常變更的情況。而我們的開發(fā)服務(wù)器卻不符合這種情況，只能想辦法實(shí)施一個(gè)動(dòng)態(tài)的管理方案。
聲明：本文為Willem（Mongo）原創(chuàng)，轉(zhuǎn)載請(qǐng)注明原文鏈接：https://segmentfault.com/a/11...。
為節(jié)省您的寶貴時(shí)間，請(qǐng)直接看觸發(fā)器2.0 + SQL變更入站規(guī)則即可，有問題可先參照問題匯總。如有其他問題請(qǐng)?jiān)谠u(píng)論中留言，一起探討。謝謝！
網(wǎng)上有文章（點(diǎn)擊查看） 寫的很詳細(xì)，這里就不贅述了。下面的SQL是創(chuàng)建數(shù)據(jù)庫觸發(fā)器時(shí)給的默認(rèn)模板，根據(jù)自己的情況進(jìn)行修改：
--====================================
-- Create database trigger template
--====================================
USE
GO
IF EXISTS(
SELECT *
FROM sys.triggers
WHERE name = N""
AND parent_class_desc = N"DATABASE"
)
DROP TRIGGER ON DATABASE
GO
CREATE TRIGGER ON DATABASE
FOR
AS
IF IS_MEMBER ("db_owner") = 0
BEGIN
PRINT "You must ask your DBA to drop or alter tables!"
ROLLBACK TRANSACTION
END
GO
登陸觸發(fā)器的創(chuàng)建腳本 v1.0
下面這個(gè)腳本只能DBA自己先向IP管理表插入允許訪問數(shù)據(jù)庫的IP，因?yàn)榈顷懹|發(fā)器只有身份驗(yàn)證通過時(shí)才能觸發(fā)，所以暫時(shí)沒去記錄非法登陸失敗的IP：
/**登錄觸發(fā)器將在登錄的身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立之前激發(fā)。如果身份驗(yàn)證失敗，將不激發(fā)登錄觸發(fā)器。**/
USE [master]
GO
/****** Object: Table [dbo].[ManagerIP] Script Date: 2016年10月13日11:31:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--將數(shù)據(jù)庫回滾到原始配置狀態(tài)，然后刪除
IF DB_ID("LoginIP") IS NOT NULL
ALTER DATABASE LoginIP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE LoginIP
GO
--創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE [LoginIP]
GO
USE [LoginIP]
GO
--創(chuàng)建IP管理表
CREATE TABLE [dbo].[ManagerIP](
[IP] [nvarchar](15) NOT NULL,
[BlockState] [bit] NOT NULL,
[FalseCount] [int] NOT NULL,
[UpdateTime] [datetime] NULL,
[TotalTimes] [int] NOT NULL,
CONSTRAINT [PK_ManagerIP] PRIMARY KEY CLUSTERED
(
[IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--插入允許通過的IP
INSERT INTO dbo.ManagerIP
( IP ,
BlockState ,
FalseCount ,
UpdateTime ,
TotalTimes
)
VALUES ( N"" , -- IP - nvarchar(15)
0 , -- BlockState - bit
0 , -- FalseCount - int
GETDATE() , -- UpdateTime - datetime
0 -- TotalTimes - int
)
INSERT INTO dbo.ManagerIP
( IP ,
BlockState ,
FalseCount ,
UpdateTime ,
TotalTimes
)
VALUES ( N"221.227.108.132" , -- IP - nvarchar(15)
0 , -- BlockState - bit
0 , -- FalseCount - int
GETDATE() , -- UpdateTime - datetime
0 -- TotalTimes - int
)
GO
USE master
GO
--刪除觸發(fā)器（注意：登陸觸發(fā)器是存放在sys.server_triggers ，而不是sys.triggers）
IF EXISTS(SELECT * FROM sys.server_triggers WHERE name = "check_login_ip") --AND parent_class_desc = N"LoginIP")
DROP TRIGGER check_login_ip ON ALL SERVER
GO
--創(chuàng)建觸發(fā)器
--CREATE TRIGGER trigger_name ON LoginIP
CREATE TRIGGER check_login_ip ON ALL SERVER
FOR LOGON
AS
IF IS_SRVROLEMEMBER ("sysadmin") = 1
BEGIN
DECLARE @ip NVARCHAR(15);
--只有直接在 DDL 或登錄觸發(fā)器內(nèi)部引用 EVENTDATA 時(shí)，EVENTDATA 才會(huì)返回?cái)?shù)據(jù)。 如果 EVENTDATA 由其他例程調(diào)用（即使這些例程由 DDL 或登錄觸發(fā)器進(jìn)行調(diào)用），將返回 NULL。
SET @ip = (SELECT EVENTDATA().value("(/EVENT_INSTANCE/ClientHost)[1]","NVARCHAR(15)"));
IF NOT EXISTS(SELECT IP FROM [LoginIP].[dbo].[ManagerIP] WHERE IP = @ip)
ROLLBACK TRANSACTION;
END
GO
相關(guān)鏈接：
點(diǎn)擊查看關(guān)于“EVENTDATA() ”的說明；
點(diǎn)擊查看“Sql Server中判斷表或者數(shù)據(jù)庫是否存在 ”
點(diǎn)擊查看“IS_SRVROLEMEMBER("sysadmin") ”的詳細(xì)說明
點(diǎn)擊查看“登錄觸發(fā)器”的詳細(xì)說明
點(diǎn)擊查看“sys.server_triggers”的詳細(xì)說明
點(diǎn)擊查看“sys.triggers”的詳細(xì)說明
上述腳本復(fù)制到SQL Server直接執(zhí)行就可以。
腳本功能說明：自動(dòng)刪除重名數(shù)據(jù)庫，然后創(chuàng)建；自動(dòng)創(chuàng)建登陸IP管理表；自動(dòng)刪除重名的登陸觸發(fā)器，然后重新創(chuàng)建。
登陸觸發(fā)器的創(chuàng)建腳本 v2.0
上面提到了關(guān)于允許訪問數(shù)據(jù)庫的IP的管理問題，1.0 版本只能手動(dòng)操作，而不能動(dòng)態(tài)的自行管理IP，這樣就造成了不夠靈活的問題。
問題產(chǎn)生的情景：
如果我在表中添加了家里和公司的IP，光是這就很麻煩，因?yàn)橐ソy(tǒng)計(jì)開發(fā)人員家中的IP地址，更何況一旦到客戶現(xiàn)場演示時(shí)，IP又無法添加，只能讓已添加的IP進(jìn)入數(shù)據(jù)庫手動(dòng)添加，實(shí)在有些麻煩。但是又不得不去設(shè)置，因?yàn)椴榭匆幌聰?shù)據(jù)庫記錄就不難看到，每天都會(huì)有很多外界的IP來光顧，雖然還未成功，但難保哪天被黑了。
需求：來訪IP記入IP管理表，連續(xù)登陸失敗超過設(shè)定的次數(shù)就將該IP設(shè)置為黑名單，可以防止其暴力破解數(shù)據(jù)庫密碼。如果未超過設(shè)定次數(shù)登陸成功，則將失敗次數(shù)清0。聽起來跟輸入銀行密碼的感覺差不多。下面是最終的邏輯圖：
又經(jīng)過了一天半的時(shí)間，產(chǎn)生了下面最終的腳本，可以動(dòng)態(tài)控制IP，這里的動(dòng)態(tài)是相對(duì)前面的靜態(tài)而言；
/**登錄觸發(fā)器將在登錄的身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立之前激發(fā)。如果身份驗(yàn)證失敗，將不激發(fā)登錄觸發(fā)器。**/
USE [master]
GO
/****** Object: Table [dbo].[ManagerIP] Script Date: 2016年10月13日11:31:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--創(chuàng)建IP管理表
IF OBJECT_ID (N"dbo.ManagerIP", N"U") IS NULL
BEGIN
CREATE TABLE [dbo].[ManagerIP](
[IP] [nvarchar](15) NOT NULL,
[LockState] [bit] NOT NULL,
[FalseCount] [int] NOT NULL,
[UpdateTime] [datetime] NULL,
[TotalTimes] [int] NOT NULL,
CONSTRAINT [PK_ManagerIP] PRIMARY KEY CLUSTERED
(
[IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
END
GO
--插入允許通過的IP
IF (SELECT COUNT(*) FROM dbo.ManagerIP) = 0
BEGIN
INSERT INTO dbo.ManagerIP
( IP ,
LockState ,
FalseCount ,
UpdateTime ,
TotalTimes
)
VALUES ( N"" , -- IP - nvarchar(15)
0 , -- BlockState - bit
0 , -- FalseCount - int
GETDATE() , -- UpdateTime - datetime
0 -- TotalTimes - int
);
END
GO
--刪除觸發(fā)器（注意：登陸觸發(fā)器是存放在sys.server_triggers ，而不是sys.triggers）
IF EXISTS(SELECT * FROM sys.server_triggers WHERE name = "check_login_ip")
BEGIN
DROP TRIGGER check_login_ip ON ALL SERVER
END
GO
--創(chuàng)建觸發(fā)器
--CREATE TRIGGER trigger_name ON LoginIP
CREATE TRIGGER check_login_ip ON ALL SERVER
FOR LOGON
AS
IF IS_SRVROLEMEMBER ("sysadmin") = 1
BEGIN
DECLARE @ip NVARCHAR(15);
--只有直接在 DDL 或登錄觸發(fā)器內(nèi)部引用 EVENTDATA 時(shí)，EVENTDATA 才會(huì)返回?cái)?shù)據(jù)。 如果 EVENTDATA 由其他例程調(diào)用（即使這些例程由 DDL 或登錄觸發(fā)器進(jìn)行調(diào)用），將返回 NULL。
SET @ip = (SELECT EVENTDATA().value("(/EVENT_INSTANCE/ClientHost)[1]","NVARCHAR(15)"));
IF (SELECT COUNT(*) FROM [master].[dbo].[ManagerIP] WHERE IP = @ip AND LockState = 1) > 0
BEGIN
ROLLBACK;
END
ELSE IF (SELECT COUNT(*) FROM [master].[dbo].[ManagerIP] WHERE IP = @ip AND LockState = 0) > 0
BEGIN
UPDATE [master].[dbo].[ManagerIP] SET UpdateTime = GETDATE() WHERE IP = @ip;
SET NOEXEC ON;
END
--刪除臨時(shí)表
ELSE IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--創(chuàng)建臨時(shí)表
CREATE TABLE #ErrorLog(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--讀取當(dāng)前日志插入到臨時(shí)表
INSERT INTO #ErrorLog EXEC sp_readerrorlog 0,1,"匹配",@ip;
-- @p1 = 0, -- int 0為當(dāng)前日志，1-9為對(duì)應(yīng)編號(hào)日志
-- @p2 = 1, -- int 1為服務(wù)器日志，2為代理日志
-- @p3 = N"", -- nvarchar(4000) 包含的第一個(gè)字段
-- @p4 = N"" -- nvarchar(4000) 包含的第二個(gè)字段
DECLARE @TotalFalse int
SET @TotalFalse = (SELECT COUNT(*) FROM #ErrorLog);
IF @TotalFalse < 5
BEGIN
INSERT INTO [dbo].[ManagerIP] ([IP], [LockState], [FalseCount], [UpdateTime], [TotalTimes])
VALUES (@ip, 0, 0, GETDATE(), 0);
SET NOEXEC ON;
END
ELSE
BEGIN
INSERT INTO [dbo].[ManagerIP] ([IP], [LockState], [FalseCount], [UpdateTime], [TotalTimes])
VALUES (@ip, 1, 0, GETDATE(), 0);
END
END
GO
參考資料：
點(diǎn)擊查看“捕獲登錄觸發(fā)器事件數(shù)據(jù)”;
點(diǎn)擊查看“GETDATE”；
點(diǎn)擊查看“OBJECT_ID”；
將當(dāng)前日志中已存在的非法IP導(dǎo)入數(shù)據(jù)庫
SQL如下：
USE master
GO
--設(shè)置允許錯(cuò)誤密碼的最大次數(shù)
DECLARE @MaxFalse int;
SET @MaxFalse = 10;
--刪除日志臨時(shí)表
IF OBJECT_ID(N"tempdb..#LogTemp") IS NOT NULL
BEGIN
DROP TABLE #LogTemp;
END
--創(chuàng)建日志臨時(shí)表
CREATE TABLE #LogTemp(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--刪除錯(cuò)誤日志臨時(shí)表
IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--創(chuàng)建錯(cuò)誤日志臨時(shí)表
CREATE TABLE #ErrorLog(
[ClientIP] [NVARCHAR](150),
[TotalFalse] [int]
);
--從日志臨時(shí)表篩選出登陸錯(cuò)誤的日志記錄
INSERT INTO #LogTemp EXEC sp_readerrorlog 0,1,"匹配","客戶端";
--對(duì)錯(cuò)誤日志進(jìn)行統(tǒng)計(jì)
INSERT INTO #ErrorLog SELECT el.Text,COUNT(*) FROM #LogTemp AS el GROUP BY el.Text
DROP TABLE #LogTemp
DECLARE @clientiptext nvarchar(150)
DECLARE @total INT
DECLARE @ipstr nvarchar(15)
WHILE EXISTS ( SELECT ClientIP FROM #ErrorLog WHERE TotalFalse > @MaxFalse)
BEGIN
SELECT TOP 1 @clientiptext = logs.ClientIP, @total = logs.TotalFalse FROM #ErrorLog AS logs WHERE logs.TotalFalse > @MaxFalse;
DELETE #ErrorLog WHERE ClientIP = @clientiptext;
SET @ipstr = SUBSTRING(@clientiptext,CHARINDEX("客戶端",@clientiptext) + 5,LEN(@clientiptext) - CHARINDEX("客戶端",@clientiptext) - 5);
IF NOT EXISTS ( SELECT * FROM dbo.ManagerIP WHERE IP = @ipstr )
BEGIN
INSERT INTO dbo.ManagerIP
( IP ,
LockState ,
FalseCount ,
UpdateTime ,
TotalTimes
)
VALUES ( @ipstr , -- IP - nvarchar(15)
1 , -- LockState - bit
@total , -- FalseCount - int
GETDATE() , -- UpdateTime - datetime
0 -- TotalTimes - int
)
END
ELSE IF @total < (SELECT FalseCount FROM dbo.ManagerIP)
BEGIN
UPDATE dbo.ManagerIP SET FalseCount += @total WHERE IP = @ipstr
END
ELSE
BEGIN
UPDATE dbo.ManagerIP SET FalseCount = @total WHERE IP = @ipstr
END
END
DROP TABLE #ErrorLog
GO
依然存在的問題
這樣看起來是沒問題了，但是這樣做并沒能防止其他人暴力破解密碼，只是破解之后不能通過該IP訪問數(shù)據(jù)庫而已，原因之前也提到了，登錄觸發(fā)器只能在驗(yàn)證通過后才能激活，反應(yīng)到實(shí)際操作中就是通過驗(yàn)證和未通過驗(yàn)證的提示是不同的，只要發(fā)現(xiàn)問題提示發(fā)生的變化就可以根據(jù)這個(gè)來判斷賬戶和密碼是否正確，當(dāng)然最好的做法就是不要對(duì)外開放數(shù)據(jù)庫。不過這樣做就會(huì)讓開發(fā)人員麻煩些。現(xiàn)在的問題就是如何可以將定為非法的IP阻止在驗(yàn)證之前。這里想到的一個(gè)做法是將該IP放到防火墻阻止列表中，這樣的話無法通過防火墻，那么就不會(huì)訪問數(shù)據(jù)庫，也就不會(huì)進(jìn)行驗(yàn)證了。
因?yàn)榍懊娴牟僮鞫寂浜嫌|發(fā)器、計(jì)劃任務(wù)或者維護(hù)計(jì)劃進(jìn)行了自動(dòng)化，所以這樣也要做到自動(dòng)向防火墻中添加IP，這樣就需要腳本來進(jìn)行操作了。而可以操作防火墻的指令：
C:Windowssystem32>netsh advfirewall firewall add rule ?
用法: add rule name=
dir=in|out
action=allow|block|bypass
[program=]
[service=|any]
[description=]
[enable=yes|no (default=yes)]
[profile=public|private|domain|any[,...]]
[localip=any|||||]
[remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway| ||||]
[localport=0-65535|[,...]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
[remoteport=0-65535|[,...]|any (default=any)]
[protocol=0-255|icmpv4|icmpv6|icmpv4:type,code|icmpv6:type,code| tcp|udp|any (default=any)]
[interfacetype=wireless|lan|ras|any]
[rmtcomputergrp=]
[rmtusrgrp=]
[edge=yes|deferapp|deferuser|no (default=no)]
[security=authenticate|authenc|authdynenc|authnoencap|notrequired (default=notrequired)]
備注:
- 將新的入站或出站規(guī)則添加到防火墻策略。
- 規(guī)則名稱應(yīng)該是唯一的，且不能為 "all"。
- 如果已指定遠(yuǎn)程計(jì)算機(jī)或用戶組，則 security 必須為 authenticate、authenc、authdynenc 或 authnoencap。
- 為 authdynenc 設(shè)置安全性可允許系統(tǒng)動(dòng)態(tài)協(xié)商為匹配 給定 Windows 防火墻規(guī)則的通信使用加密。 根據(jù)現(xiàn)有連接安全規(guī)則屬性協(xié)商加密。選擇此選項(xiàng)后，只要入站 IPSec 連接已設(shè)置安全保護(hù)， 但未使用 IPSec 進(jìn)行加密，計(jì)算機(jī)就能夠接收該入站連接的第一個(gè) TCP 或 UDP 包。一旦處理了第一個(gè)數(shù)據(jù)包，服務(wù)器將重新協(xié)商連接并對(duì)其進(jìn)行升級(jí)，以便所 有后續(xù)通信都完全加密。
- 如果 action=bypass，則 dir=in 時(shí)必須指定遠(yuǎn)程計(jì)算機(jī)組。
- 如果 service=any，則規(guī)則僅應(yīng)用到服務(wù)。
- ICMP 類型或代碼可以為 "any"。
- Edge 只能為入站規(guī)則指定。
- AuthEnc 和 authnoencap 不能同時(shí)使用。
- Authdynenc 僅當(dāng) dir=in 時(shí)有效。
- 設(shè)置 authnoencap 后，security=authenticate 選項(xiàng)就變成可選參數(shù)。
示例:
為不具有封裝的 messenger.exe 添加入站規(guī)則:
netsh advfirewall firewall add rule name="allow messenger" dir=in program="c:programfilesmessengermsmsgs.exe" security=authnoencap action=allow
為端口 80 添加出站規(guī)則:
netsh advfirewall firewall add rule name="allow80" protocol=TCP dir=out localport=80 action=block
為 TCP 端口 80 通信添加需要安全和加密的入站規(guī)則:
netsh advfirewall firewall add rule name="Require Encryption for Inbound TCP/80" protocol=TCP dir=in localport=80 security=authdynenc action=allow
為 messenger.exe 添加需要安全的入站規(guī)則:
netsh advfirewall firewall add rule name="allow messenger" dir=in program="c:program filesmessengermsmsgs.exe" security=authenticate action=allow
為 SDDL 字符串標(biāo)識(shí)的組 acmedomainscanners 添加經(jīng)過身份驗(yàn)證的防火墻跳過規(guī)則:
netsh advfirewall firewall add rule name="allow scanners" dir=in rmtcomputergrp= action=bypass security=authenticate
為 udp- 的本地端口 5000-5010 添加出站允許規(guī)則
Add rule name="Allow port range" dir=out protocol=udp localport=5000-5010 action=allow
通過幫助信息，我們可以了解到其中各個(gè)參數(shù)的含義及用途。而我們所需要達(dá)到的目的是：防止某IP訪問該服務(wù)器上的數(shù)據(jù)庫。對(duì)照上面翻譯成簡單的腳本就是：
netsh advfirewall firewall add rule name=BlockIP dir=in action=block description=阻止訪問服務(wù)器數(shù)據(jù)庫，甚至所有程序。 enable=yes remoteip=115.29.77.97
而我們需要把所有需要阻止的IP都要加入該規(guī)則中的 remoteip 中。不過在執(zhí)行過程中出現(xiàn)了權(quán)限限制的問題，退而求其次，將bat命令存儲(chǔ)為bat文件。
SQL輸出bat文件
這種做法比較惡心，因?yàn)檫€要計(jì)劃任務(wù)去調(diào)用執(zhí)行，而且保存的文件還有問題，因?yàn)閺?fù)制其中的命令到新建的bat文件中可以正常執(zhí)行，但是直接執(zhí)行該文件則有問題。不推薦此種方法，請(qǐng)查看下一種方法。
USE master
GO
--設(shè)置允許錯(cuò)誤密碼的最大次數(shù)
DECLARE @MaxFalse int;
SET @MaxFalse = 66;
--刪除日志臨時(shí)表
IF OBJECT_ID(N"tempdb..#LogTemp") IS NOT NULL
BEGIN
DROP TABLE #LogTemp;
END
--創(chuàng)建日志臨時(shí)表
CREATE TABLE #LogTemp(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--刪除錯(cuò)誤日志臨時(shí)表
IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--創(chuàng)建錯(cuò)誤日志臨時(shí)表
CREATE TABLE #ErrorLog(
[ClientIP] [NVARCHAR](150),
[TotalFalse] [int]
);
--從日志臨時(shí)表篩選出登陸錯(cuò)誤的日志記錄
INSERT INTO #LogTemp EXEC sp_readerrorlog 0,1,"匹配","客戶端";
--對(duì)錯(cuò)誤日志進(jìn)行統(tǒng)計(jì)
INSERT INTO #ErrorLog SELECT el.Text,COUNT(*) FROM #LogTemp AS el GROUP BY el.Text
DROP TABLE #LogTemp
DECLARE @clientiptext nvarchar(150)
DECLARE @total int
DECLARE @ipstr nvarchar(15)
DELETE FROM ManagerIP WHERE FalseCount < @MaxFalse AND LockState = 1
WHILE EXISTS ( SELECT ClientIP FROM #ErrorLog WHERE TotalFalse > @MaxFalse)
BEGIN
SELECT TOP 1 @clientiptext = logs.ClientIP, @total = logs.TotalFalse FROM #ErrorLog AS logs WHERE logs.TotalFalse > @MaxFalse;
DELETE #ErrorLog WHERE ClientIP = @clientiptext;
SET @ipstr = SUBSTRING(@clientiptext,CHARINDEX("客戶端",@clientiptext) + 5,LEN(@clientiptext) - CHARINDEX("客戶端",@clientiptext) - 5);
IF NOT EXISTS ( SELECT * FROM dbo.ManagerIP WHERE IP = @ipstr )
BEGIN
INSERT INTO dbo.ManagerIP
( IP ,
LockState ,
FalseCount ,
UpdateTime ,
TotalTimes
)
VALUES ( @ipstr , -- IP - nvarchar(15)
1 , -- LockState - bit
@total , -- FalseCount - int
GETDATE() , -- UpdateTime - datetime
0 -- TotalTimes - int
)
END
END
DROP TABLE #ErrorLog;
EXEC sys.sp_configure @configname = "show advanced options", -- varchar(35)
@configvalue = 1; -- int
GO
RECONFIGURE;
GO
EXEC sys.sp_configure @configname = "Ole Automation Procedures", -- varchar(35)
@configvalue = 1; -- int
GO
RECONFIGURE;
GO
DECLARE @blockips nvarchar(MAX) = "";
DECLARE @tempip nvarchar(15) = "";
IF OBJECT_ID(N"tempdb..#ForFirewall") IS NOT NULL
BEGIN
DROP TABLE #ForFirewall
END
CREATE TABLE #ForFirewall(
BlockIP NVARCHAR(15)
);
INSERT INTO #ForFirewall SELECT IP FROM dbo.ManagerIP WHERE LockState = 1
WHILE EXISTS (SELECT TOP 1 BlockIP FROM #ForFirewall ORDER BY BlockIP)
BEGIN
SET @tempip = (SELECT TOP 1 BlockIP FROM #ForFirewall ORDER BY BlockIP);
SET @blockips = @blockips + @tempip + ",";
DELETE FROM #ForFirewall WHERE BlockIP = @tempip;
END
SET @blockips = "netsh advfirewall firewall delete rule name = BlockIP & netsh advfirewall firewall add rule name = BlockIP dir = in action = block enable = yes remoteip = " + SUBSTRING(@blockips, 0, LEN(@blockips) - 1);
--EXEC master..xp_cmdshell @blockips;
DECLARE @TEXT VARBINARY(MAX)
SET @TEXT = CAST(@blockips AS VARBINARY(max))
DECLARE @ObjectToken INT
EXEC sp_OACreate "ADODB.Stream", @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, "Type", 1
EXEC sp_OAMethod @ObjectToken, "Open"
EXEC sp_OAMethod @ObjectToken, "Write", NULL, @TEXT
EXEC sp_OAMethod @ObjectToken, "SaveToFile", NULL, "E:Microsoft SQL Server Backup LogDynamicIPControlBlockIP.bat", 2
EXEC sp_OAMethod @ObjectToken, "Close"EXEC sp_OADestroy @ObjectToken
GO
SQL變更入站規(guī)則
在SQL中直接執(zhí)行cmd命令無法繞過管理員權(quán)限，嘗試轉(zhuǎn)為管理員權(quán)限但是并未成功。后來在服務(wù)器上執(zhí)行下面的SQL則沒有出現(xiàn)權(quán)限問題，可以順利更改防火墻入站規(guī)則。需要注意的是該腳本包含了從日志導(dǎo)入非法IP，所以在創(chuàng)建計(jì)劃任務(wù)的時(shí)候只使用該腳本就可以，不要再多帶帶執(zhí)行導(dǎo)入非法IP的腳本了。
USE master
GO
IF OBJECT_ID("ManagerIP") IS NULL
BEGIN
CREATE TABLE [dbo].[ManagerIP](
[IP] [nvarchar](15) NOT NULL,
[LockState] [bit] NOT NULL,
[FalseCount] [int] NOT NULL,
[UpdateTime] [datetime] NULL,
[TotalTimes] [int] NOT NULL,
CONSTRAINT [PK_ManagerIP] PRIMARY KEY CLUSTERED
(
[IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
--設(shè)置允許錯(cuò)誤密碼的最大次數(shù)
DECLARE @MaxFalse int;
SET @MaxFalse = 66;
--刪除日志臨時(shí)表
IF OBJECT_ID(N"tempdb..#LogTemp") IS NOT NULL
BEGIN
DROP TABLE #LogTemp;
END
--創(chuàng)建日志臨時(shí)表
CREATE TABLE #LogTemp(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--刪除錯(cuò)誤日志臨時(shí)表
IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--創(chuàng)建錯(cuò)誤日志臨時(shí)表
CREATE TABLE #ErrorLog(
[ClientIP] [NVARCHAR](150),
[TotalFalse] [int]
);
--從日志臨時(shí)表篩選出登陸錯(cuò)誤的日志記錄
INSERT INTO #LogTemp EXEC sp_readerrorlog 0,1,"匹配","客戶端";
--對(duì)錯(cuò)誤日志進(jìn)行統(tǒng)計(jì)
INSERT INTO #ErrorLog SELECT el.Text,COUNT(*) FROM #LogTemp AS el GROUP BY el.Text
DROP TABLE #LogTemp
DECLARE @clientiptext NVARCHAR(150)
DECLARE @total INT
DECLARE @ipstr NVARCHAR(15)
DECLARE @falsecount INT
DELETE FROM ManagerIP WHERE FalseCount < @MaxFalse AND LockState = 1
WHILE EXISTS ( SELECT ClientIP FROM #ErrorLog WHERE TotalFalse > @MaxFalse)
BEGIN
SELECT TOP 1 @clientiptext = logs.ClientIP, @total = logs.TotalFalse FROM #ErrorLog AS logs WHERE logs.TotalFalse > @MaxFalse;
DELETE #ErrorLog WHERE ClientIP = @clientiptext;
SET @ipstr = SUBSTRING(@clientiptext,CHARINDEX("客戶端",@clientiptext) + 5,LEN(@clientiptext) - CHARINDEX("客戶端",@clientiptext) - 5);
SET @falsecount = (SELECT TOP 1 FalseCount FROM dbo.ManagerIP);
IF NOT EXISTS ( SELECT * FROM dbo.ManagerIP WHERE IP = @ipstr )
BEGIN
INSERT INTO dbo.ManagerIP
( IP ,
LockState ,
FalseCount ,
UpdateTime ,
TotalTimes
)
VALUES ( @ipstr , -- IP - nvarchar(15)
1 , -- LockState - bit
@total , -- FalseCount - int
GETDATE() , -- UpdateTime - datetime
0 -- TotalTimes - int
)
END
ELSE IF @total < @falsecount
BEGIN
UPDATE dbo.ManagerIP SET FalseCount = @falsecount + @total WHERE IP = @ipstr
END
ELSE
BEGIN
UPDATE dbo.ManagerIP SET FalseCount = @total WHERE IP = @ipstr
END
END
DROP TABLE #ErrorLog;
EXEC sys.sp_configure @configname = "show advanced options", -- varchar(35)
@configvalue = 1; -- int
GO
RECONFIGURE;
GO
EXEC sys.sp_configure @configname = "xp_cmdshell", -- varchar(35)
@configvalue = 1; -- int
GO
RECONFIGURE;
GO
DECLARE @blockips01 varchar(8000) = "";
DECLARE @blockips02 varchar(8000) = "";
DECLARE @tempip nvarchar(15) = "";
--DECLARE @addrule NVARCHAR(1000);
IF OBJECT_ID(N"tempdb..#ForFirewall") IS NOT NULL
BEGIN
DROP TABLE #ForFirewall
END
CREATE TABLE #ForFirewall(
BlockIP NVARCHAR(15)
);
INSERT INTO #ForFirewall SELECT IP FROM dbo.ManagerIP WHERE LockState = 1
WHILE EXISTS (SELECT TOP 1 BlockIP FROM #ForFirewall ORDER BY BlockIP)
BEGIN
SET @tempip = (SELECT TOP 1 BlockIP FROM #ForFirewall ORDER BY BlockIP);
IF LEN(@blockips01) > 3500
BEGIN
SET @blockips02 = @blockips02 + @tempip + ",";
END
ELSE
BEGIN
SET @blockips01 = @blockips01 + @tempip + ",";
END
DELETE FROM #ForFirewall WHERE BlockIP = @tempip;
END
IF @blockips01 != ""
BEGIN
SET @blockips01 = "@netsh advfirewall firewall delete rule name = BlockIP01 & netsh advfirewall firewall add rule name = BlockIP01 dir = in action = block enable = yes remoteip = " + SUBSTRING(@blockips01, 0, LEN (@blockips01) - 1);
EXEC master..xp_cmdshell @blockips01;
END
IF @blockips02 != ""
BEGIN
SET @blockips02 = "@netsh advfirewall firewall delete rule name = BlockIP02 & netsh advfirewall firewall add rule name = BlockIP02 dir = in action = block enable = yes remoteip = " + SUBSTRING(@blockips02, 0, LEN (@blockips02) - 1);
EXEC master..xp_cmdshell @blockips02;
END
SELECT @blockips01
SELECT @blockips02
GO
EXEC sys.sp_configure @configname = "xp_cmdshell", -- varchar(35)
@configvalue = 0; -- int
GO
RECONFIGURE;
GO
EXEC sys.sp_configure @configname = "show advanced options", -- varchar(35)
@configvalue = 0; -- int
GO
RECONFIGURE;
GO
執(zhí)行結(jié)果如下：
此時(shí)再查看防火墻入站規(guī)則中的作用域就會(huì)發(fā)現(xiàn)里面多了很多的遠(yuǎn)程IP地址。
接下來要做的就簡單多了，創(chuàng)建代理作業(yè)，將上面的代碼拷貝粘貼進(jìn)代理作業(yè)要執(zhí)行的SQL區(qū)，讓作業(yè)循環(huán)進(jìn)行就可以了。這樣當(dāng)惡意訪問次數(shù)操作指定次數(shù)（代碼里可以修改MaxFalse）就會(huì)被放到數(shù)據(jù)庫黑名單，同時(shí)加入防火墻阻止名單。
相關(guān)資料：
點(diǎn)擊查看 “xp_cmdshell”
點(diǎn)擊查看 “Ole Automation Procedures”
最終版本如下：
USE master;
GO
-- 創(chuàng)建IP管理表
IF OBJECT_ID("ManagerIP") IS NULL
BEGIN
CREATE TABLE [dbo].[ManagerIP]
(
[IP] [NVARCHAR](15) NOT NULL,
[LockState] [BIT] NOT NULL,
[FalseCount] [INT] NOT NULL,
[UpdateTime] [DATETIME] NULL,
[TotalTimes] [INT] NOT NULL,
CONSTRAINT [PK_ManagerIP]
PRIMARY KEY CLUSTERED ([IP] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY];
END;
GO
IF NOT EXISTS (SELECT IP FROM dbo.ManagerIP WHERE IP = "localhost")
BEGIN
INSERT INTO dbo.ManagerIP
(
IP,
LockState,
FalseCount,
UpdateTime,
TotalTimes
)
VALUES
( N"localhost", -- IP - nvarchar(15)
0, -- LockState - bit
0, -- FalseCount - int
GETDATE(), -- UpdateTime - datetime
0 -- TotalTimes - int
);
END;
GO
--刪除日志臨時(shí)表
IF OBJECT_ID(N"TempLog") IS NOT NULL
BEGIN
DROP TABLE dbo.TempLog;
END;
--創(chuàng)建日志臨時(shí)表
CREATE TABLE dbo.TempLog
(
[LogDate] [DATETIME] NOT NULL,
[ProcessInfo] [NVARCHAR](200) NULL,
[Text] [NVARCHAR](1000) NULL
);
--刪除錯(cuò)誤日志臨時(shí)表
IF OBJECT_ID(N"ErrorLog") IS NOT NULL
BEGIN
DROP TABLE dbo.ErrorLog;
END;
--創(chuàng)建錯(cuò)誤日志臨時(shí)表
CREATE TABLE dbo.ErrorLog
(
[ClientIP] [NVARCHAR](150) NOT NULL,
[TotalFalse] [INT] NOT NULL
);
--從日志臨時(shí)表篩選出登陸錯(cuò)誤的日志記錄
INSERT INTO dbo.TempLog
(
LogDate,
ProcessInfo,
Text
)
EXEC sp_readerrorlog 0, 1, N"匹配", N"客戶端";
--對(duì)錯(cuò)誤日志進(jìn)行統(tǒng)計(jì)
INSERT INTO dbo.ErrorLog
(
ClientIP,
TotalFalse
)
SELECT Text ClientIP,
COUNT(*) TotalFalse
FROM dbo.TempLog
GROUP BY Text;
GO
-- 整理非法訪問的IP
DECLARE @clientIP NVARCHAR(MAX);
DECLARE @totalFalse INT;
DECLARE @ipStr NVARCHAR(20);
DECLARE @falseCount INT;
DECLARE @MaxFalse INT;
-- 設(shè)置允許錯(cuò)誤密碼的最大次數(shù)
SET @MaxFalse = 66;
DELETE FROM dbo.ManagerIP
WHERE FalseCount < @MaxFalse
AND LockState = 1;
DECLARE LogCursor CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
SELECT ClientIP,
TotalFalse
FROM dbo.ErrorLog
WHERE TotalFalse > @MaxFalse;
OPEN LogCursor;
FETCH NEXT FROM LogCursor
INTO @clientIP,
@totalFalse;
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM dbo.ErrorLog
WHERE ClientIP = @clientIP;
SET @ipStr
= SUBSTRING(@clientIP, CHARINDEX("客戶端", @clientIP) + 5, LEN(@clientIP) - CHARINDEX("客戶端", @clientIP) - 5);
SET @falseCount =
(
SELECT TOP (1) FalseCount FROM dbo.ManagerIP ORDER BY FalseCount
);
IF NOT EXISTS (SELECT IP FROM dbo.ManagerIP WHERE IP = @ipStr)
BEGIN
INSERT INTO dbo.ManagerIP
(
IP,
LockState,
FalseCount,
UpdateTime,
TotalTimes
)
VALUES
( @ipStr, -- IP - nvarchar(15)
1, -- LockState - bit
@totalFalse, -- FalseCount - int
GETDATE(), -- UpdateTime - datetime
0 -- TotalTimes - int
);
END;
ELSE IF @totalFalse < @falseCount
BEGIN
UPDATE dbo.ManagerIP
SET FalseCount = @falseCount + @totalFalse
WHERE IP = @ipStr;
END;
ELSE
BEGIN
UPDATE dbo.ManagerIP
SET FalseCount = @totalFalse
WHERE IP = @ipStr;
END;
FETCH NEXT FROM LogCursor
INTO @clientIP,
@totalFalse;
END;
CLOSE LogCursor;
DEALLOCATE LogCursor;
-- 操作防火墻
EXEC sys.sp_configure @configname = "show advanced options", -- varchar(35)
@configvalue = 1; -- int
GO
RECONFIGURE;
GO
EXEC sys.sp_configure @configname = "xp_cmdshell", -- varchar(35)
@configvalue = 1; -- int
GO
RECONFIGURE;
GO
DECLARE @blockips01 VARCHAR(8000) = "";
DECLARE @blockips02 VARCHAR(8000) = "";
DECLARE @tempip NVARCHAR(15) = "";
--DECLARE @addrule NVARCHAR(1000);
IF OBJECT_ID(N"ForFirewall") IS NOT NULL
BEGIN
DROP TABLE dbo.ForFirewall;
END;
CREATE TABLE dbo.ForFirewall
(
BlockIP NVARCHAR(15) NOT NULL
);
INSERT INTO dbo.ForFirewall
(
BlockIP
)
SELECT IP
FROM dbo.ManagerIP
WHERE LockState = 1;
WHILE EXISTS (SELECT TOP (1) BlockIP FROM dbo.ForFirewall ORDER BY BlockIP)
BEGIN
SET @tempip =
(
SELECT TOP (1) BlockIP FROM dbo.ForFirewall ORDER BY BlockIP
);
IF LEN(@blockips01) > 3500
BEGIN
SET @blockips02 = @blockips02 + @tempip + ",";
END;
ELSE
BEGIN
SET @blockips01 = @blockips01 + @tempip + ",";
END;
DELETE FROM dbo.ForFirewall
WHERE BlockIP = @tempip;
END;
IF @blockips01 <> ""
BEGIN
SET @blockips01
= "@netsh advfirewall firewall delete rule name = BlockIP01 & netsh advfirewall firewall add rule name = BlockIP01 dir = in action = block enable = yes remoteip = "
+ SUBSTRING(@blockips01, 0, LEN(@blockips01));
EXEC master..xp_cmdshell @blockips01;
END;
IF @blockips02 <> ""
BEGIN
SET @blockips02
= "@netsh advfirewall firewall delete rule name = BlockIP02 & netsh advfirewall firewall add rule name = BlockIP02 dir = in action = block enable = yes remoteip = "
+ SUBSTRING(@blockips02, 0, LEN(@blockips02));
EXEC master..xp_cmdshell @blockips02;
END;
SELECT @blockips01 Firewalls_CMD_STR_01;
SELECT @blockips02 Firewalls_CMD_STR_02;
GO
EXEC sys.sp_configure @configname = "xp_cmdshell", -- varchar(35)
@configvalue = 0; -- int
GO
RECONFIGURE;
GO
EXEC sys.sp_configure @configname = "show advanced options", -- varchar(35)
@configvalue = 0; -- int
GO
RECONFIGURE;
GO
IF OBJECT_ID(N"TempLog") IS NOT NULL
BEGIN
DROP TABLE dbo.TempLog;
END;
IF OBJECT_ID(N"ErrorLog") IS NOT NULL
BEGIN
DROP TABLE dbo.ErrorLog;
END;
IF OBJECT_ID(N"ForFirewall") IS NOT NULL
BEGIN
DROP TABLE dbo.ForFirewall;
END;
GO
問題匯總
小問題
在沒有將本機(jī)添加到表中的情況下創(chuàng)建了登陸觸發(fā)器，會(huì)讓本機(jī)也無法連接數(shù)據(jù)庫，如下面所述。
在調(diào)試SQL之前，我并沒有向表中添加數(shù)據(jù)，結(jié)果可想而知，重連數(shù)據(jù)庫就會(huì)跳出這么個(gè)東西來。
圖中分別用了“Windows 身份驗(yàn)證”和“SQL Server 身份驗(yàn)證”，都無法連接，頓時(shí)感覺自己真逗，把自己鎖外面了，還沒帶鑰匙。不過我相信SQL Server會(huì)兼容我這種智商的存在，網(wǎng)上查了下，果真可以。具體做法：
打開命令行，Ctrl + R，cmd回車；
通過DAC登錄到服務(wù)器（mongo為主機(jī)名）：sqlcmd -A -S mongo，然后會(huì)出現(xiàn)這種提示符就說明登陸成功：1>
輸入“DROP TRIGGER [check_login_ip] ON ALL SERVER”回車（“check_login_ip”為觸發(fā)器名）；
輸入“go”回車；
然后再去連接數(shù)據(jù)庫試試吧，問題解決了。接下來就是把自己的IP插入到數(shù)據(jù)庫，然后做進(jìn)一步的測試。
中問題
SQLServer 錯(cuò)誤: 15404，無法獲取有關(guān) Windows NT 組/用戶 NQAdministrator 的信息，錯(cuò)誤代碼 0534。
很明顯是賬戶問題，起因：數(shù)據(jù)庫安裝完成之后，更改過計(jì)算機(jī)管理員賬戶名，但是數(shù)據(jù)庫這邊的用戶并未做同步設(shè)置，還是用的原用戶名。
解決方法：連接數(shù)據(jù)庫，然后在【安全性】-【登錄名】下找到原管理員用戶名，如果是服務(wù)器一般都是帶有“Administrator”的那一個(gè)，右鍵重命名，改成現(xiàn)在的之后重啟SQL Server訪問就可以了。
大問題
請(qǐng)求的操作需要提升(作為管理員運(yùn)行)。
這個(gè)問題發(fā)生在用SQL通過“xp_cmdshell”執(zhí)行“EXEC master..xp_cmdshell "@netsh advfirewall firewall delete rule name = BlockIP"”時(shí)提示：請(qǐng)求的操作需要提升(作為管理員運(yùn)行)。 之所以說它是大問題是因?yàn)榫W(wǎng)上的答案要么不適合我遇到的問題，要么無效，總之不對(duì)癥。近期因?yàn)殚_發(fā)部同事需要調(diào)用cmd總是失敗，才聯(lián)想到cmd的安全權(quán)限問題。解決方案：將“SQL Server (SQLSERVER)”服務(wù)的登錄用戶更改為管理員用戶，并將管理員用戶添加到cmd.exe的安全權(quán)限里；或者是新建一個(gè)用戶，并將“SQL Server (SQLSERVER)”服務(wù)的登錄用戶更改為新用戶，并將新用戶添加到cmd.exe的安全權(quán)限里。如此一來，SQL Server就有調(diào)用cmd.exe的權(quán)限了。
起因：我這次要操作的是master數(shù)據(jù)庫，而我并未將管理員用戶映射到改數(shù)據(jù)庫。
解決方法：選擇要映射的數(shù)據(jù)庫，在【安全性】-【用戶】下查看是否有管理員用戶，如果沒有將其添加進(jìn)來即可。添加方法，就是到全局的【安全性】-【登錄名】下找到管理員用戶名，然后右鍵，選擇屬性，選擇用戶映射，勾選要映射的數(shù)據(jù)庫并選擇數(shù)據(jù)庫角色成員身份，這里要勾上“db_owner”。
Willem 更新于 2016年10月21日10:29:37
