摘要：脚本功能说明自动删除重名数据库，然后创建自动创建登陆管理表自动删除重名的登陆触发器，然后重新创建。如果身份验证失败，将不激发登录触发器。如果由其他例程调用即使这些例程由或登录触发器进行调用，将返回。
查看数据库日志发现好多非法登陆失败的记录，虽然之前已经更改了服务器和数据库的管理员账户的用户名，但是为了保险起见，还是再加上一道措施。创建登录触发器，只允许指定的IP访问，服务器设置白名单，但是这只适用于访问用户的IP固定不会经常变更的情况。而我们的开发服务器却不符合这种情况，只能想办法实施一个动态的管理方案。
声明：本文为Willem（Mongo）原创，转载请注明原文链接：https://segmentfault.com/a/11...。
为节省您的宝贵时间，请直接看触发器2.0 + SQL变更入站规则即可，有问题可先参照问题汇总。如有其他问题请在评论中留言，一起探讨。谢谢！
网上有文章（点击查看） 写的很详细，这里就不赘述了。下面的SQL是创建数据库触发器时给的默认模板，根据自己的情况进行修改：
--====================================
-- 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
登陆触发器的创建脚本 v1.0
下面这个脚本只能DBA自己先向IP管理表插入允许访问数据库的IP，因为登陆触发器只有身份验证通过时才能触发，所以暂时没去记录非法登陆失败的IP：
/**登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。如果身份验证失败，将不激发登录触发器。**/
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
--将数据库回滚到原始配置状态，然后删除
IF DB_ID("LoginIP") IS NOT NULL
ALTER DATABASE LoginIP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE LoginIP
GO
--创建数据库
CREATE DATABASE [LoginIP]
GO
USE [LoginIP]
GO
--创建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
--删除触发器（注意：登陆触发器是存放在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
--创建触发器
--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 或登录触发器内部引用 EVENTDATA 时，EVENTDATA 才会返回数据。 如果 EVENTDATA 由其他例程调用（即使这些例程由 DDL 或登录触发器进行调用），将返回 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
相关链接：
点击查看关于“EVENTDATA() ”的说明；
点击查看“Sql Server中判断表或者数据库是否存在 ”
点击查看“IS_SRVROLEMEMBER("sysadmin") ”的详细说明
点击查看“登录触发器”的详细说明
点击查看“sys.server_triggers”的详细说明
点击查看“sys.triggers”的详细说明
上述脚本复制到SQL Server直接执行就可以。
脚本功能说明：自动删除重名数据库，然后创建；自动创建登陆IP管理表；自动删除重名的登陆触发器，然后重新创建。
登陆触发器的创建脚本 v2.0
上面提到了关于允许访问数据库的IP的管理问题，1.0 版本只能手动操作，而不能动态的自行管理IP，这样就造成了不够灵活的问题。
问题产生的情景：
如果我在表中添加了家里和公司的IP，光是这就很麻烦，因为要去统计开发人员家中的IP地址，更何况一旦到客户现场演示时，IP又无法添加，只能让已添加的IP进入数据库手动添加，实在有些麻烦。但是又不得不去设置，因为查看一下数据库记录就不难看到，每天都会有很多外界的IP来光顾，虽然还未成功，但难保哪天被黑了。
需求：来访IP记入IP管理表，连续登陆失败超过设定的次数就将该IP设置为黑名单，可以防止其暴力破解数据库密码。如果未超过设定次数登陆成功，则将失败次数清0。听起来跟输入银行密码的感觉差不多。下面是最终的逻辑图：
又经过了一天半的时间，产生了下面最终的脚本，可以动态控制IP，这里的动态是相对前面的静态而言；
/**登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。如果身份验证失败，将不激发登录触发器。**/
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
--创建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
--删除触发器（注意：登陆触发器是存放在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
--创建触发器
--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 或登录触发器内部引用 EVENTDATA 时，EVENTDATA 才会返回数据。 如果 EVENTDATA 由其他例程调用（即使这些例程由 DDL 或登录触发器进行调用），将返回 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
--删除临时表
ELSE IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--创建临时表
CREATE TABLE #ErrorLog(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--读取当前日志插入到临时表
INSERT INTO #ErrorLog EXEC sp_readerrorlog 0,1,"匹配",@ip;
-- @p1 = 0, -- int 0为当前日志，1-9为对应编号日志
-- @p2 = 1, -- int 1为服务器日志，2为代理日志
-- @p3 = N"", -- nvarchar(4000) 包含的第一个字段
-- @p4 = N"" -- nvarchar(4000) 包含的第二个字段
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
参考资料：
点击查看“捕获登录触发器事件数据”;
点击查看“GETDATE”；
点击查看“OBJECT_ID”；
将当前日志中已存在的非法IP导入数据库
SQL如下：
USE master
GO
--设置允许错误密码的最大次数
DECLARE @MaxFalse int;
SET @MaxFalse = 10;
--删除日志临时表
IF OBJECT_ID(N"tempdb..#LogTemp") IS NOT NULL
BEGIN
DROP TABLE #LogTemp;
END
--创建日志临时表
CREATE TABLE #LogTemp(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--删除错误日志临时表
IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--创建错误日志临时表
CREATE TABLE #ErrorLog(
[ClientIP] [NVARCHAR](150),
[TotalFalse] [int]
);
--从日志临时表筛选出登陆错误的日志记录
INSERT INTO #LogTemp EXEC sp_readerrorlog 0,1,"匹配","客户端";
--对错误日志进行统计
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访问数据库而已，原因之前也提到了，登录触发器只能在验证通过后才能激活，反应到实际操作中就是通过验证和未通过验证的提示是不同的，只要发现问题提示发生的变化就可以根据这个来判断账户和密码是否正确，当然最好的做法就是不要对外开放数据库。不过这样做就会让开发人员麻烦些。现在的问题就是如何可以将定为非法的IP阻止在验证之前。这里想到的一个做法是将该IP放到防火墙阻止列表中，这样的话无法通过防火墙，那么就不会访问数据库，也就不会进行验证了。
因为前面的操作都配合触发器、计划任务或者维护计划进行了自动化，所以这样也要做到自动向防火墙中添加IP，这样就需要脚本来进行操作了。而可以操作防火墙的指令：
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)]
备注:
- 将新的入站或出站规则添加到防火墙策略。
- 规则名称应该是唯一的，且不能为 "all"。
- 如果已指定远程计算机或用户组，则 security 必须为 authenticate、authenc、authdynenc 或 authnoencap。
- 为 authdynenc 设置安全性可允许系统动态协商为匹配 给定 Windows 防火墙规则的通信使用加密。 根据现有连接安全规则属性协商加密。选择此选项后，只要入站 IPSec 连接已设置安全保护， 但未使用 IPSec 进行加密，计算机就能够接收该入站连接的第一个 TCP 或 UDP 包。一旦处理了第一个数据包，服务器将重新协商连接并对其进行升级，以便所 有后续通信都完全加密。
- 如果 action=bypass，则 dir=in 时必须指定远程计算机组。
- 如果 service=any，则规则仅应用到服务。
- ICMP 类型或代码可以为 "any"。
- Edge 只能为入站规则指定。
- AuthEnc 和 authnoencap 不能同时使用。
- Authdynenc 仅当 dir=in 时有效。
- 设置 authnoencap 后，security=authenticate 选项就变成可选参数。
示例:
为不具有封装的 messenger.exe 添加入站规则:
netsh advfirewall firewall add rule name="allow messenger" dir=in program="c:programfilesmessengermsmsgs.exe" security=authnoencap action=allow
为端口 80 添加出站规则:
netsh advfirewall firewall add rule name="allow80" protocol=TCP dir=out localport=80 action=block
为 TCP 端口 80 通信添加需要安全和加密的入站规则:
netsh advfirewall firewall add rule name="Require Encryption for Inbound TCP/80" protocol=TCP dir=in localport=80 security=authdynenc action=allow
为 messenger.exe 添加需要安全的入站规则:
netsh advfirewall firewall add rule name="allow messenger" dir=in program="c:program filesmessengermsmsgs.exe" security=authenticate action=allow
为 SDDL 字符串标识的组 acmedomainscanners 添加经过身份验证的防火墙跳过规则:
netsh advfirewall firewall add rule name="allow scanners" dir=in rmtcomputergrp= action=bypass security=authenticate
为 udp- 的本地端口 5000-5010 添加出站允许规则
Add rule name="Allow port range" dir=out protocol=udp localport=5000-5010 action=allow
通过帮助信息，我们可以了解到其中各个参数的含义及用途。而我们所需要达到的目的是：防止某IP访问该服务器上的数据库。对照上面翻译成简单的脚本就是：
netsh advfirewall firewall add rule name=BlockIP dir=in action=block description=阻止访问服务器数据库，甚至所有程序。 enable=yes remoteip=115.29.77.97
而我们需要把所有需要阻止的IP都要加入该规则中的 remoteip 中。不过在执行过程中出现了权限限制的问题，退而求其次，将bat命令存储为bat文件。
SQL输出bat文件
这种做法比较恶心，因为还要计划任务去调用执行，而且保存的文件还有问题，因为复制其中的命令到新建的bat文件中可以正常执行，但是直接执行该文件则有问题。不推荐此种方法，请查看下一种方法。
USE master
GO
--设置允许错误密码的最大次数
DECLARE @MaxFalse int;
SET @MaxFalse = 66;
--删除日志临时表
IF OBJECT_ID(N"tempdb..#LogTemp") IS NOT NULL
BEGIN
DROP TABLE #LogTemp;
END
--创建日志临时表
CREATE TABLE #LogTemp(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--删除错误日志临时表
IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--创建错误日志临时表
CREATE TABLE #ErrorLog(
[ClientIP] [NVARCHAR](150),
[TotalFalse] [int]
);
--从日志临时表筛选出登陆错误的日志记录
INSERT INTO #LogTemp EXEC sp_readerrorlog 0,1,"匹配","客户端";
--对错误日志进行统计
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变更入站规则
在SQL中直接执行cmd命令无法绕过管理员权限，尝试转为管理员权限但是并未成功。后来在服务器上执行下面的SQL则没有出现权限问题，可以顺利更改防火墙入站规则。需要注意的是该脚本包含了从日志导入非法IP，所以在创建计划任务的时候只使用该脚本就可以，不要再多带带执行导入非法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
--设置允许错误密码的最大次数
DECLARE @MaxFalse int;
SET @MaxFalse = 66;
--删除日志临时表
IF OBJECT_ID(N"tempdb..#LogTemp") IS NOT NULL
BEGIN
DROP TABLE #LogTemp;
END
--创建日志临时表
CREATE TABLE #LogTemp(
[LogDate] [datetime],
[ProcessInfo] [nvarchar](200),
[Text] [NVARCHAR](1000)
);
--删除错误日志临时表
IF OBJECT_ID(N"tempdb..#ErrorLog") IS NOT NULL
BEGIN
DROP TABLE #ErrorLog;
END
--创建错误日志临时表
CREATE TABLE #ErrorLog(
[ClientIP] [NVARCHAR](150),
[TotalFalse] [int]
);
--从日志临时表筛选出登陆错误的日志记录
INSERT INTO #LogTemp EXEC sp_readerrorlog 0,1,"匹配","客户端";
--对错误日志进行统计
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
执行结果如下：
此时再查看防火墙入站规则中的作用域就会发现里面多了很多的远程IP地址。
接下来要做的就简单多了，创建代理作业，将上面的代码拷贝粘贴进代理作业要执行的SQL区，让作业循环进行就可以了。这样当恶意访问次数操作指定次数（代码里可以修改MaxFalse）就会被放到数据库黑名单，同时加入防火墙阻止名单。
相关资料：
点击查看 “xp_cmdshell”
点击查看 “Ole Automation Procedures”
最终版本如下：
USE master;
GO
-- 创建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
--删除日志临时表
IF OBJECT_ID(N"TempLog") IS NOT NULL
BEGIN
DROP TABLE dbo.TempLog;
END;
--创建日志临时表
CREATE TABLE dbo.TempLog
(
[LogDate] [DATETIME] NOT NULL,
[ProcessInfo] [NVARCHAR](200) NULL,
[Text] [NVARCHAR](1000) NULL
);
--删除错误日志临时表
IF OBJECT_ID(N"ErrorLog") IS NOT NULL
BEGIN
DROP TABLE dbo.ErrorLog;
END;
--创建错误日志临时表
CREATE TABLE dbo.ErrorLog
(
[ClientIP] [NVARCHAR](150) NOT NULL,
[TotalFalse] [INT] NOT NULL
);
--从日志临时表筛选出登陆错误的日志记录
INSERT INTO dbo.TempLog
(
LogDate,
ProcessInfo,
Text
)
EXEC sp_readerrorlog 0, 1, N"匹配", N"客户端";
--对错误日志进行统计
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;
-- 设置允许错误密码的最大次数
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
问题汇总
小问题
在没有将本机添加到表中的情况下创建了登陆触发器，会让本机也无法连接数据库，如下面所述。
在调试SQL之前，我并没有向表中添加数据，结果可想而知，重连数据库就会跳出这么个东西来。
图中分别用了“Windows 身份验证”和“SQL Server 身份验证”，都无法连接，顿时感觉自己真逗，把自己锁外面了，还没带钥匙。不过我相信SQL Server会兼容我这种智商的存在，网上查了下，果真可以。具体做法：
打开命令行，Ctrl + R，cmd回车；
通过DAC登录到服务器（mongo为主机名）：sqlcmd -A -S mongo，然后会出现这种提示符就说明登陆成功：1>
输入“DROP TRIGGER [check_login_ip] ON ALL SERVER”回车（“check_login_ip”为触发器名）；
输入“go”回车；
然后再去连接数据库试试吧，问题解决了。接下来就是把自己的IP插入到数据库，然后做进一步的测试。
中问题
SQLServer 错误: 15404，无法获取有关 Windows NT 组/用户 NQAdministrator 的信息，错误代码 0534。
很明显是账户问题，起因：数据库安装完成之后，更改过计算机管理员账户名，但是数据库这边的用户并未做同步设置，还是用的原用户名。
解决方法：连接数据库，然后在【安全性】-【登录名】下找到原管理员用户名，如果是服务器一般都是带有“Administrator”的那一个，右键重命名，改成现在的之后重启SQL Server访问就可以了。
大问题
请求的操作需要提升(作为管理员运行)。
这个问题发生在用SQL通过“xp_cmdshell”执行“EXEC master..xp_cmdshell "@netsh advfirewall firewall delete rule name = BlockIP"”时提示：请求的操作需要提升(作为管理员运行)。 之所以说它是大问题是因为网上的答案要么不适合我遇到的问题，要么无效，总之不对症。近期因为开发部同事需要调用cmd总是失败，才联想到cmd的安全权限问题。解决方案：将“SQL Server (SQLSERVER)”服务的登录用户更改为管理员用户，并将管理员用户添加到cmd.exe的安全权限里；或者是新建一个用户，并将“SQL Server (SQLSERVER)”服务的登录用户更改为新用户，并将新用户添加到cmd.exe的安全权限里。如此一来，SQL Server就有调用cmd.exe的权限了。
起因：我这次要操作的是master数据库，而我并未将管理员用户映射到改数据库。
解决方法：选择要映射的数据库，在【安全性】-【用户】下查看是否有管理员用户，如果没有将其添加进来即可。添加方法，就是到全局的【安全性】-【登录名】下找到管理员用户名，然后右键，选择属性，选择用户映射，勾选要映射的数据库并选择数据库角色成员身份，这里要勾上“db_owner”。
Willem 更新于 2016年10月21日10:29:37
