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