sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
CREATE TABLE errorLog (LogDate DATETIME, ProcessInfo VARCHAR(64), [Text] VARCHAR(MAX));
INSERT INTO errorLog
EXEC sp_readerrorlog -- specify the log number or use nothing for active error log
CREATE FUNCTION RegexBysp_OA (@subject varchar(max), @pattern varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
DECLARE @objRegexExp INT,
@objMatch INT,
@Result VARCHAR(8000) --Creating COM object
EXEC sp_OACreate 'VBScript.RegExp', @objRegexExp OUT--Assigning Properties to COM object
EXEC sp_OASetProperty @objRegexExp, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
EXEC sp_OASetProperty @objRegexExp, 'MultiLine', 1
EXEC sp_OASetProperty @objRegexExp, 'Global', false
EXEC sp_OASetProperty @objRegexExp, 'CultureInvariant', true --Executing the COM object
EXEC sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @subject--Fetching the first matching value
EXEC sp_OAGetProperty @objmatch, 'item(0).Value' , @Result OUT--Releasing COM object after use
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objRegexExp RETURN @Result
END
GO
--To Check the function
select *, master.dbo.RegexBysp_OA(Text,'(?:[0-9]{1,3}\.){3}[0-9]{1,3}') as [IP_Address] from
CREATE TABLE IP_Address (IP_Address VARCHAR(64));
INSERT INTO IP_Address
SELECT master.dbo.RegexBysp_OA(Text,'(?:[0-9]{1,3}\.){3}[0-9]{1,3}') as [IP_Address]
FROM errorLog
select count(*) "Connection Count", IP_Address from IP_Address where IP_Address is not null group by IP_Address order by 1;
Based on above result you can implement firewall to block specific IP to made connection to SQL Server.
