locked
Find All Client IPs from SQL Server Alert Log RRS feed

  • Question

  • Hello,

    I have experience of Oracle database and have a very little experience in SQL Server. 
    In our environment multiple SQL Server instances are running, Our IT Security team scan the systems for vulnerabilities and share the reports accordingly. Most vulnerabilities are related to database open access,for Oracle databases we get the client IP from listener's log file and white listed in sqlnet.ora file, 
    Now for SQL Server how can I get client IPs from SQL Server alert log (if these client IPs are written in alert log) to implement firewall (for IPs white listing) and what is the way forward?

    Regards,
    Taoqir


    Monday, September 2, 2019 10:42 AM

Answers

  • I have found my solution, this is a collage from from different source not my work,

    DROP TABLE  errorLog; 

    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 verfy the function
    select *, master.dbo.RegexBysp_OA(Text,'(?:[0-9]{1,3}\.){3}[0-9]{1,3}') as [IP_Address] FROM  errorLog;

    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 distinct IP_Address from IP_Address  where IP_Address is not null

    Now I will check the unique IPs and white-list (on firewall level) whichever we need.

    Regards,

    Taoqir 


    Tuesday, September 3, 2019 4:27 AM

All replies

  • Hello, We can see client IPs in current SQL Server ERRORLOG, how to find unique IPs from this ERRORLOG please support.

    2019-09-02 15:52:18.89 Logon       Login succeeded for user 'USER_NAME'. Connection made using SQL Server authentication. [CLIENT: XX.XX.XX.XX]
    2019-09-02 15:52:24.26 Logon       Login succeeded for user 'USER_NAME'. Connection made using SQL Server authentication. [CLIENT: XX.XX.XX.XX]
    2019-09-02 15:52:26.24 Logon       Login succeeded for user 'USER_NAME'. Connection made using SQL Server authentication. [CLIENT: XX.XX.XX.XX]
    2019-09-02 15:52:26.27 Logon       Login succeeded for user 'USER_NAME'. Connection made using SQL Server authentication. [CLIENT: XX.XX.XX.XX]
    2019-09-02 15:52:32.05 Logon       Login succeeded for user 'USER_NAME'. Connection made using SQL Server authentication. [CLIENT: XX.XX.XX.XX]
    2019-09-02 15:52:34.31 Logon       Login succeeded for user 'USER_NAME'. Connection made using SQL Server authentication. [CLIENT: XX.XX.XX.XX]
    2019-09-02 15:52:39.89 Logon       Login succeeded for user 'USER_NAME'. Connection made using SQL Server authentication. [CLIENT: XX.XX.XX.XX]


    Regards, Taoqir

    Monday, September 2, 2019 10:56 AM
  • I think it would be easier to set up a logon trigger that logs this information to a table. I have an example here:
    http://www.sommarskog.se/grantperm.html#serverlevelobjects


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 2, 2019 9:38 PM
  • I have found my solution, this is a collage from from different source not my work,

    DROP TABLE  errorLog; 

    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 verfy the function
    select *, master.dbo.RegexBysp_OA(Text,'(?:[0-9]{1,3}\.){3}[0-9]{1,3}') as [IP_Address] FROM  errorLog;

    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 distinct IP_Address from IP_Address  where IP_Address is not null

    Now I will check the unique IPs and white-list (on firewall level) whichever we need.

    Regards,

    Taoqir 


    Tuesday, September 3, 2019 4:27 AM
  • Hi Taoqir,

    Did you resolve your issue? If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution. 

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 3, 2019 6:20 AM