Monitoring log-in failures-problem with DMZ servers

  • I have a script running daily to pull login failures from many servers.  The script runs xp_cmdshell, runs DOS commands FIND to locate the text "Login Failed" within the latest SQL Server error log file.  This is very successful for servers behind the firewall, but does not work on servers in a DMZ.  My question is:

     

    What port does xp_cmdshell use to hit a server?

     

     

     

  • If you are returning the results of the xp_cmdshell to a table on a machine outside the dmz, then it would be the MDTC that will be blocked.

  • The output would be downloaded to the server inside the firewall.  Not to a table but to a file on the server inside the firewall.

     

    Here's the script:

    CREATE proc sp_AuditLogs as

    set nocount on

    exec master..xp_cmdshell 'del c:\temp\Final.txt c:\temp\Parsed.txt c:\temp\Filter.txt', no_output

    --declaration

    declare @today varchar(10)

    declare @sql varchar(255)

    declare @ServerName varchar(30), @LogPath varchar(255), @Application varchar(30)

    --set date

    select @today = convert(varchar(10), getdate(), 121)

    --select @today

    --sql

    declare crsrServer cursor for

    select ServerName, LogPath, Application from Servers order by ServerName asc

    open crsrServer

    fetch next from crsrServer into @ServerName, @LogPath, @Application

    while @@FETCH_STATUS = 0

    begin

    --select 'Server is ' + @ServerName + ' and Logfiles reside in ' + @LogPath

    select @sql = 'master..xp_cmdshell ''find /N /I "' + @today + '" < "' + @LogPath + '\ERRORLOG" > "c:\temp\Filter.txt"'', no_output'

    --select @sql

    exec (@sql)

    select @sql = 'master..xp_cmdshell ''find /N /I "Login failed" < "c:\temp\Filter.txt" > "c:\temp\Parsed.txt"'', no_output'

    exec (@sql)

    if (len(rtrim(@ServerName)) + len(Rtrim(@Application))+ 13)  > 50

    select @sql = 'master..xp_cmdshell ''echo ==       ' +  @Servername + '--' + @Application +  '== >> "c:\temp\Final.txt"'', no_output'

    else

    select @sql = 'master..xp_cmdshell ''echo ==       ' +  @Servername + '--' + @Application + replicate(' ', 50 - (len(rtrim(@ServerName)) + len(Rtrim(@Application))+ 13)) + '== >> "c:\temp\Final.txt"'', no_output'

    exec master..xp_cmdshell 'echo ================================================== >> "c:\temp\Final.txt"', no_output

    exec (@sql)

    exec master..xp_cmdshell 'echo ================================================== >> "c:\temp\Final.txt"', no_output

    exec master..xp_cmdshell 'type "C:\temp\Parsed.txt" >> "c:\temp\Final.txt"', no_output

    fetch next from crsrServer into @ServerName, @LogPath, @Application

    end

    close crsrServer

    deallocate crsrServer

    set nocount off

    exec master..xp_cmdshell 'type "C:\temp\Final.txt"'

    GO

  • Bump! To see if anyone else has any idea?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply