Limit IP landing through landing triggers and firewalls

Time:2019-8-12

Looking at the database logs, we found many records of illegal login failures. Although the username of server and database administrator accounts has been changed before, for the sake of insurance, we added another measure. Create a login trigger that allows only specified IP access and whitelist settings on the server, but this only applies to situations where the fixed IP of the access user does not change frequently. However, our development server is not in line with this situation, we can only find a way to implement a dynamic management scheme.

Statement: This article is originally created by Willem (Mongo). Please note the link of the original text: https://segmentfault.com/a/11….

In order to save your precious time, please read it directly.Trigger 2.0 + SQL Change Inbound RulesAll right. If you have any questions, you can refer to them first.Summary of problems。 If there are other questions, please leave a message in the comments and discuss them together. Thank you!

There are articles on the Internet (click to see) written in great detail, which will not be repeated here. The following SQL is the default template when creating a database trigger, which can be modified according to your own situation:

--====================================
--  Create database trigger template
--====================================
USE <database_name, sysname, AdventureWorks>
GO
IF EXISTS(
  SELECT *
    FROM sys.triggers
   WHERE name = N'<trigger_name, sysname, table_alter_drop_safety>'
     AND parent_class_desc = N'DATABASE'
)
            DROP TRIGGER <trigger_name, sysname, table_alter_drop_safety> ON DATABASE
GO
CREATE TRIGGER <trigger_name, sysname, table_alter_drop_safety> ON DATABASE
            FOR <data_definition_statements, , DROP_TABLE, ALTER_TABLE>
AS
IF IS_MEMBER ('db_owner') = 0
BEGIN
   PRINT 'You must ask your DBA to drop or alter tables!'
   ROLLBACK TRANSACTION
END
GO

Creation script v1.0 for landing trigger

The following script can only insert the IP that allows access to the database into the IP management table by DBA itself, because the login trigger can only trigger when the authentication has passed, so the IP that failed to log in illegally is not recorded for the time being:

/ ** The login trigger will be fired after the login authentication phase is completed and before the user session is actually established. If authentication fails, the login trigger will not be activated. * * /
USE [master]
GO
/ ****** Object: Table [dbo]. [ManagerIP] Script Date: October 13, 2016 11:31:22********/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- Roll back the database to its original configuration state and delete it
IF DB_ID('LoginIP') IS NOT NULL
  ALTER DATABASE LoginIP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DROP DATABASE LoginIP
GO
Create a database
CREATE DATABASE [LoginIP]
GO
USE [LoginIP]
GO
Create IP Management Table
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
Insert the IP allowed to pass
INSERT INTO dbo.ManagerIP
        ( IP ,
          BlockState ,
          FalseCount ,
          UpdateTime ,
          TotalTimes
        )
VALUES  ( N'<local machine>' , -- 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
Delete triggers (note: the login triggers are stored in sys. server_triggers, not 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 triggers
--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);
   EVENTDATA returns data only when it is referenced directly within DDL or login triggers. If EVENTDATA is invoked by other routines (even if these routines are invoked by DDL or login triggers), NULL is returned.
   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

Related links:
Click on the description of “EVENTDATA ()”;
Click to see “Determine whether a table or database exists in Sql Server”
Click to see the details of “IS_SRVROLEMEMBER (‘sysadmin’)”
Click to see the details of Login Trigger
Click to see the details of “sys. server_triggers”
Click to see the details of “sys. triggers”

The above script can be copied to SQL Server and executed directly.
The script function description: automatically delete the duplicate name database, then create; automatically create the login IP management table; automatically delete the duplicate name login trigger, and then re-create.

Creation script v2.0 for landing trigger

As for the management of IP that allows access to databases, version 1.0 can only operate manually, but can not manage IP dynamically by itself, which causes the problem of insufficient flexibility.
Scenarios of problems:
If I add the IP of home and company to the table, it will be very troublesome, because it is troublesome to count the IP address of the developer’s home, especially when I go to the customer’s live demonstration, the IP can not be added, so I can only add the added IP into the database manually, which is a little troublesome. But it has to be set up, because it is not difficult to see the database records, every day there will be a lot of external IP patronage, although not yet successful, but it is difficult to guarantee that the day will be black.

Limit IP landing through landing triggers and firewalls

Requirement: Visiting IP is entered into IP management table, and if the number of successive login failures exceeds the set number, the IP is set as a blacklist, which can prevent it from violently cracking database passwords. If the number of logins does not exceed the set number, the number of failures will be cleared to zero. Sounds like entering a bank password. The following is the final logical diagram:

Limit IP landing through landing triggers and firewalls

After another day and a half, the following final script was generated, which can dynamically control IP, where the dynamic is relative to the static.

/ ** The login trigger will be fired after the login authentication phase is completed and before the user session is actually established. If authentication fails, the login trigger will not be activated. * * /
USE [master]
GO
/ ****** Object: Table [dbo]. [ManagerIP] Script Date: October 13, 2016 11:31:22********/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create IP Management Table
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
Insert the IP allowed to pass
IF (SELECT COUNT(*) FROM dbo.ManagerIP) = 0
BEGIN
            INSERT INTO dbo.ManagerIP
                                    ( IP ,
                                      LockState ,
                                      FalseCount ,
                                      UpdateTime ,
                                      TotalTimes
                                    )
            VALUES  ( N'<local machine>' , -- IP - nvarchar(15)
                                      0 , -- BlockState - bit
                                      0 , -- FalseCount - int
                                      GETDATE() , -- UpdateTime - datetime
                                      0  -- TotalTimes - int
                                    );
END
GO
Delete triggers (note: the login triggers are stored in sys. server_triggers, not 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 triggers
--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);
            EVENTDATA returns data only when it is referenced directly within DDL or login triggers. If EVENTDATA is invoked by other routines (even if these routines are invoked by DDL or login triggers), NULL is returned.
            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
            - Delete temporary tables
            ELSE IF OBJECT_ID(N'tempdb..#ErrorLog') IS NOT NULL
            BEGIN
                        DROP TABLE #ErrorLog;
            END
            Create temporary tables
            CREATE TABLE #ErrorLog(
                        [LogDate] [datetime],
                        [ProcessInfo] [nvarchar](200),
                        [Text] [NVARCHAR](1000)
            );
            -- Read the current log and insert it into the temporary table
            INSERT INTO# ErrorLog EXEC sp_readerrorlog 0,1,'Match', @ip;
            [email protected] = 0, -- int 0 is the current log and 1-9 is the corresponding number log.
            [email protected] = 1, -- int 1 for server log and 2 for proxy log
            [email protected] = N', -- nvarchar (4000) contains the first field
            [email protected] = N'-- The second field contained in 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

Reference material:
Click to view “Capture Logon Trigger Event Data”;
Click to see “GETDATE”;
Click to view “OBJECT_ID”;

Import the illegal IP that already exists in the current log into the database

SQL is as follows:

USE master
GO
- Set the maximum number of times an error password is allowed
DECLARE @MaxFalse int;
SET @MaxFalse = 10;
Delete log temporary tables
IF OBJECT_ID(N'tempdb..#LogTemp') IS NOT NULL
            BEGIN
                        DROP TABLE #LogTemp;
            END
Create log temporary tables
CREATE TABLE #LogTemp(
            [LogDate] [datetime],
            [ProcessInfo] [nvarchar](200),
            [Text] [NVARCHAR](1000)
);
Delete error log temporary table
IF OBJECT_ID(N'tempdb..#ErrorLog') IS NOT NULL
            BEGIN
                        DROP TABLE #ErrorLog;
            END
Create error log temporary tables
CREATE TABLE #ErrorLog(
            [ClientIP] [NVARCHAR](150),
            [TotalFalse] [int]
);
Screening log records for login errors from log temporary tables
INSERT INTO# LogTemp EXEC sp_readerrorlog 0,1,'Match','Client';
Statistics of error logs
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;
            [email protected] = SUBSTRING (@clientiptext, CHARINDEX ('client', @clientiptext) + 5, LEN (@clientiptext) - CHARINDEX ('client', @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

Still existing problems

This seems to be no problem, but this does not prevent other people from violently cracking the password, but only after cracking can not access the database through the IP. The reason mentioned earlier is that login triggers can only be activated after the verification has passed, which is reflected in the actual operation of the proposal that has passed the verification and failed the verification. Indication is different, as long as you find the change of the problem prompt, you can judge whether the account and password are correct or not. Of course, the best way is not to open the database. But doing so will make developers more troublesome. The problem now is how to prevent IP from being illegal before authentication. One idea here is to put the IP in the firewall blocking list, so that it can not pass through the firewall, then it will not access the database, and it will not be verified.

Because the previous operations are automated with triggers, scheduled tasks or maintenance plans, so it is also necessary to automatically add IP to the firewall, which requires scripts to operate. Instructions to operate firewalls:

C:\Windows\system32>netsh advfirewall firewall add rule ?
Usage: add rule name = < string >
      dir=in|out
      action=allow|block|bypass
      [program=<program path>]
      [service=<service short name>|any]
      [description=<string>]
      [enable=yes|no (default=yes)]
      [profile=public|private|domain|any[,...]]
      [localip=any|<IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway| <IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [localport=0-65535|<port range>[,...]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
      [remoteport=0-65535|<port range>[,...]|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=<SDDL string>]
      [rmtusrgrp=<SDDL string>]
      [edge=yes|deferapp|deferuser|no (default=no)]
      [security=authenticate|authenc|authdynenc|authnoencap|notrequired (default=notrequired)]

Remarks:
      - Add new inbound or outbound rules to the firewall policy.
      - The rule name should be unique and not "all".
      - If a remote computer or user group has been specified, security must be authenticate, authenc, authdynenc, or authnoencap.
      - Setting security for authdynenc allows the system to dynamically negotiate the use of encryption for communications that match a given Windows firewall rule. Negotiate encryption based on existing connection security rule attributes. When this option is selected, the computer can receive the first TCP or UDP packet of the inbound IPSec connection as long as the inbound IPSec connection is secured but not encrypted with IPSec. Once the first packet is processed, the server will renegotiate the connection and upgrade it so that all subsequent communications are fully encrypted.
      - If action = bypass, the remote computer group must be specified when dir = in.
      - If service = any, the rule applies only to the service.
      - ICMP type or code can be "any".
      - Edge can only be specified for the inbound rule.
      - AuthEnc and authnoencap cannot be used simultaneously.
      - Authdynenc is valid only if dir = in.
      - When authnoencap is set, the security = authenticate option becomes an optional parameter.

Examples:
      Add an inbound rule for messenger.exe that does not have encapsulation:
      netsh advfirewall firewall add rule name="allow messenger" dir=in program="c:\programfiles\messenger\msmsgs.exe" security=authnoencap action=allow
      Add outbound rules for port 80:
      netsh advfirewall firewall add rule name="allow80" protocol=TCP dir=out localport=80 action=block
      Add inbound rules that require security and encryption for TCP port 80 communications:
      netsh advfirewall firewall add rule name="Require Encryption for Inbound TCP/80" protocol=TCP dir=in localport=80 security=authdynenc action=allow
      Add inbound rules that require security for messenger.exe:
      netsh advfirewall firewall add rule name="allow messenger" dir=in program="c:\program files\messenger\msmsgs.exe" security=authenticate action=allow
      Add an authenticated firewall skip rule for the group acmedomain scanners identified by the SDDL string:
      netsh advfirewall firewall add rule name="allow scanners" dir=in rmtcomputergrp=<SDDL string> action=bypass security=authenticate
      Add outbound admission rules for udp-local port 5000-5010
      Add rule name="Allow port range" dir=out protocol=udp localport=5000-5010 action=allow

Through help information, we can understand the meaning and use of each parameter. What we need to achieve is to prevent an IP from accessing the database on the server. In contrast to the above translation into a simple script is:

Netsh advfirewall firewall add rule name = BlockIP dir = in action = block description = prevents access to server database, even all programs. Enable = yes remoteip = 115.29.77.97

And we need to add all the IP that needs to be blocked to remoteip in this rule. However, in the process of execution, there is a problem of restriction of permission. Next, the bat command is stored as a bat file.

SQL output bat file

This is disgusting because you have to plan tasks to call execution, and there is a problem with the saved files, because copying the commands into the newly created bat file can be performed normally, but directly executing the file is problematic.This method is not recommended, please look at the next method.

USE master
GO
- Set the maximum number of times an error password is allowed
DECLARE @MaxFalse int;
SET @MaxFalse = 66;
Delete log temporary tables
IF OBJECT_ID(N'tempdb..#LogTemp') IS NOT NULL
            BEGIN
                        DROP TABLE #LogTemp;
            END
Create log temporary tables
CREATE TABLE #LogTemp(
            [LogDate] [datetime],
            [ProcessInfo] [nvarchar](200),
            [Text] [NVARCHAR](1000)
);
Delete error log temporary table
IF OBJECT_ID(N'tempdb..#ErrorLog') IS NOT NULL
            BEGIN
                        DROP TABLE #ErrorLog;
            END
Create error log temporary tables
CREATE TABLE #ErrorLog(
            [ClientIP] [NVARCHAR](150),
            [TotalFalse] [int]
);
Screening log records for login errors from log temporary tables
INSERT INTO# LogTemp EXEC sp_readerrorlog 0,1,'Match','Client';
Statistics of error logs
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;
            [email protected] = SUBSTRING (@clientiptext, CHARINDEX ('client', @clientiptext) + 5, LEN (@clientiptext) - CHARINDEX ('client', @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 Log\DynamicIPControl\BlockIP.bat', 2
EXEC sp_OAMethod @ObjectToken, 'Close'EXEC sp_OADestroy @ObjectToken
GO

SQL Change Inbound Rules

Executing CMD commands directly in SQL can’t bypass administrator privileges and attempt to switch to administrator privileges, but it is unsuccessful. Later, the following SQL was executed on the server without any privilege problems, and the firewall inbound rules could be changed smoothly. It is important to note that the script contains importing illegal IP from the log, so when creating a scheduled task, you can only use the script, instead of executing the script importing illegal IP alone.

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
- Set the maximum number of times an error password is allowed
DECLARE @MaxFalse int;
SET @MaxFalse = 66;
Delete log temporary tables
IF OBJECT_ID(N'tempdb..#LogTemp') IS NOT NULL
            BEGIN
                        DROP TABLE #LogTemp;
            END
Create log temporary tables
CREATE TABLE #LogTemp(
            [LogDate] [datetime],
            [ProcessInfo] [nvarchar](200),
            [Text] [NVARCHAR](1000)
);
Delete error log temporary table
IF OBJECT_ID(N'tempdb..#ErrorLog') IS NOT NULL
            BEGIN
                        DROP TABLE #ErrorLog;
            END
Create error log temporary tables
CREATE TABLE #ErrorLog(
            [ClientIP] [NVARCHAR](150),
            [TotalFalse] [int]
);
Screening log records for login errors from log temporary tables
INSERT INTO# LogTemp EXEC sp_readerrorlog 0,1,'Match','Client';
Statistics of error logs
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;
            [email protected] = SUBSTRING (@clientiptext, CHARINDEX ('client', @clientiptext) + 5, LEN (@clientiptext) - CHARINDEX ('client', @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

The results are as follows:

Limit IP landing through landing triggers and firewalls

When you look at the scope of the firewall inbound rule, you will find that there are many more remote IP addresses in it.

Limit IP landing through landing triggers and firewalls

The next step is much simpler. Create a proxy job, copy and paste the above code into the SQL area to be executed by the proxy job, and let the job cycle proceed. This way, when malicious access times are specified (MaxFalse can be modified in the code), it will be blacklisted in the database and added to the firewall blocking list.

Relevant information:
Click View“xp_cmdshell
Click View“Ole Automation Procedures

The final version is as follows:

USE master;
GO
Create IP Management Table
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

Delete log temporary tables
IF OBJECT_ID(N'TempLog') IS NOT NULL
BEGIN
    DROP TABLE dbo.TempLog;
END;
Create log temporary tables
CREATE TABLE dbo.TempLog
(
    [LogDate] [DATETIME] NOT NULL,
    [ProcessInfo] [NVARCHAR](200) NULL,
    [Text] [NVARCHAR](1000) NULL
);
Delete error log temporary table
IF OBJECT_ID(N'ErrorLog') IS NOT NULL
BEGIN
    DROP TABLE dbo.ErrorLog;
END;
Create error log temporary tables
CREATE TABLE dbo.ErrorLog
(
    [ClientIP] [NVARCHAR](150) NOT NULL,
    [TotalFalse] [INT] NOT NULL
);
Screening log records for login errors from log temporary tables
INSERT INTO dbo.TempLog
(
    LogDate,
    ProcessInfo,
    Text
)
EXEC sp_readerrorlog 0, 1, N'Match', N'Client';
Statistics of error logs
INSERT INTO dbo.ErrorLog
(
    ClientIP,
    TotalFalse
)
SELECT Text ClientIP,
       COUNT(*) TotalFalse
FROM dbo.TempLog
GROUP BY Text;
GO

- Cleaning up IP with Illegal Access
DECLARE @clientIP NVARCHAR(MAX);
DECLARE @totalFalse INT;
DECLARE @ipStr NVARCHAR(20);
DECLARE @falseCount INT;
DECLARE @MaxFalse INT;
- Set the maximum number of times an error password is allowed
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 ('client', @clientIP) + 5, LEN (@clientIP) - CHARINDEX ('client', @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;

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

Summary of problems

  • A minor question

A landing trigger is created without adding the native to the table, making it impossible for the native to connect to the database, as described below.
Before debugging the SQL, I did not add data to the table. As a result, it is conceivable that reconnecting the database will jump out of this thing.

Limit IP landing through landing triggers and firewalls

Limit IP landing through landing triggers and firewalls

The figure uses “Windows Authentication” and “SQL Server Authentication” respectively. They can’t connect. They feel funny and lock themselves out without keys. But I believe that SQL Server will be compatible with the existence of my intelligence quotient. If you check it online, you can. Specific practices:

Open the command line,Ctrl + RcmdEnter;
Log in to the server via DAC (mongo as host name):sqlcmd -A -S mongoThis prompt then appears to indicate that the landing was successful: 1 >
Input“DROP TRIGGER [check_login_ip] ON ALL SERVER” Return(“check_login_ip” For trigger name;
Input“go” Enter the train;

Limit IP landing through landing triggers and firewalls

Then try connecting to the database, and the problem is solved. The next step is to insert your IP into the database and test it further.

  • Middling problem

SQL Server error: 15404, unable to obtain information about Windows NT group / user NQ Administrator, error code 0534.
Obviously, the account problem is caused by: after the installation of the database, the account name of the computer administrator has been changed, but the user on the database side has not done synchronization settings, but the original user name is still used.
Solution: Connect to the database, and then find the original administrator user name under the [security] – [login name]. If the server is usually the one with the `Administrator’, right-click rename, and restart the SQL Server access after changing it to the present one.

Limit IP landing through landing triggers and firewalls

  • big problems

The requested operation needs to be upgraded (run as an administrator).

This problem occurs when passing through SQL“xp_cmdshell” Implementation“EXEC master..xp_cmdshell '@netsh advfirewall firewall delete rule name = BlockIP'” When prompted:The requested operation needs to be upgraded (run as an administrator).The reason why it’s a big problem is that the answers on the Internet are either not suitable for the problems I encounter, or ineffective, in short, not symptomatic. Recently, because my colleagues in the development department need to call CMD always fail, the security permission of CMD is associated. Solution: Change the login user of the “SQL Server” service to an administrator user and add the administrator user to the security permissions of cmd.exe; or create a new user and change the login user of the “SQL Server” service to a new user, and add the new user to the security of cmd.exe In the jurisdiction. In this way, SQL Server has the right to call cmd.exe.

Cause: My main task this time is the master database, and I did not map the administrator user to the modified database.

Solution: Select the database to be mapped and check whether there is an administrator user under Security – [User], if it is not added. To add a method, you can go to the ____________Global[Security] – [Login Name] Find the administrator user name, then right-click, select attributes, select user mapping, check the database to be mapped and select the database role membership, here to tick “db_owner”.

Limit IP landing through landing triggers and firewalls

Limit IP landing through landing triggers and firewalls

Willem updated at 10:29:37 on October 21, 2016

Recommended Today

The method of obtaining the resolution of display by pyqt5

The code is as follows import sys from PyQt5.QtWidgets import QApplication, QWidget class Example(QWidget): def __init__(self): super().__init__() self.initUI() #Interface drawing to initui method def initUI(self): self.desktop = QApplication.desktop() #Get display resolution size self.screenRect = self.desktop.screenGeometry() self.height = self.screenRect.height() self.width = self.screenRect.width() print(self.height) print(self.width) #Show window self.show() if __name__ == ‘__main__’: #Create applications and objects app […]