Find Client IP from SQL Server ERRORLOG

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.