If I could effectively manage the MSDE instance (schedule jobs, build custom
procs, etc) I would, but I shouldn't have to load Enterprise Manager on my
firewall to do that. Hell, even doing that doesn't really work to hook into
the local instance...
I don't think many people know this, but when you load SQL "Client tools"
only on a system, you can't apply the SQL Service Packs. They return a "SQL
isn't installed on this machine" error. The real problem is that the
client-side tools install (and mark safe-for-scripting) some dangerous
ActiveX controls. These are the controls I leveraged to steal creds via my
rouge SQL server code I wrote years ago (the old Defcon talk.)
God, I remember it now-- I was talking to your Doug Bayer, telling him how I
could force silent NTLM/LM authentication over outbound 1433 via an HTML
email, and he said "What difference does it make what port you authenticate
on??" I was like, "Oh my God! You are a total tool!!" I hope he's learned
something about security sense then... Anyway, I digress... The point is,
while subsequent SP's fixed the problem, since you can't load SP's on
"client-tools only" boxes, there is no way in hell I'm going to load EM on
my farking firewall, just so I can log!!
Here is my proc: I have my ISA web proxies logging to the WebProxyLog
table, but take what I want from that table on a daily basis (run after
midnight for daily logs) to grab the day before's events, and to clear out
any log entries in the main table that are over a week old.
First you have to create a custom table. There are a couple of reasons for
this- one, the default table sucks- you can't even index on "ClientUserName"
because the field is too big (index can't be over 900 bytes, you know) plus
you don't need all that crap. Here's my custom table:
<snip>
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomWebLog]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[CustomWebLog]
GO
CREATE TABLE [dbo].[CustomWebLog] ( [LogID] [bigint] IDENTITY (1, 1) NOT NULL , [SourceSystem] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ClientIP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ClientUserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ClientAgent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [logDate] [datetime] NULL , [logTime] [datetime] NULL , [service] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestHost] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestHostIP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestHostPort] [int] NULL , [protocol] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [transport] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [uri] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [mimetype] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [resultcode] [int] NULL , [rule] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [FilterInfo] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SrcNetwork] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DstNetwork] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ErrorInfo] [int] NULL , [Action] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
<snip> Use ISALogs Insert into CustomWebLog (SourceSystem,ClientIP,ClientUserName,ClientAgent,logDate,logTime,service,DestHost,DestHostIP,DestHostPort,protocol,transport, URI,mimetype,resultcode,[rule],FilterInfo,srcNetwork,DstNetwork,ErrorInfo,[Action])selectltrim(rtrim(left(SourceSystem,50))),ltrim(rtrim(left(ClientIP,32))),ltrim(rtrim(left(ClientUsername,50))),ltrim(rtrim(left(ClientAgent,50))),logDate,logTime,ltrim(rtrim(left(service,16))),ltrim(rtrim(left(DestHost,100))),ltrim(rtrim(left(DestHostIP,32))),DestHostPort,ltrim(rtrim(left(protocol,12))),ltrim(rtrim(left(transport,8))),ltrim(rtrim(left(URI,500))),ltrim(rtrim(left(mimetype,32))),resultcode,ltrim(rtrim(left([rule],25))),ltrim(rtrim(left(FilterInfo,25))),ltrim(rtrim(left(SrcNetwork,25))),ltrim(rtrim(left(DstNetwork,25))),ErrorInfo,ltrim(rtrim(left([Action],32)))from webproxylog where logdate = (cast(left(getdate(),11) As datetime) -1)and action = 'Allowed'delete WebProxyLog where logdate < = (cast(left(getdate(),11) Asdatetime) -7)</snip>This takes the info I need so my Director of National Accounts can reviewthe logs without having to wade through the "crap" (hence the "Allowed" action WHERE clause) while keeping 7 days of raw data in the default table.While it is really nice that ISA will continue to monitor logging capacityand will restart services when it can resume logging, my firewall shouldn'tpuke while this proc is running. Rather than disabling LDM, ISA shouldautomatically revert to MSDE logging if the SQL con is not available,monitor the connection, and post logged entries back to it when the serviceis available again. I should at least have an option to "go into LDM, orlog to local MSDE" when I am performing the mandatory maintenance requiredto log to SQL.t-----"I may disapprove of what you say,but I will defend to the death yourright to say it."----- Original Message -----From: "Jim Harrison" <Jim@xxxxxxxxxxxx>To: "[ISAserver.org Discussion List]" <isalist@xxxxxxxxxxxxx>Sent: Thursday, December 15, 2005 10:48 PMSubject: [isalist] RE: Lockdown Mode> http://www.ISAserver.org>> The best way to handle data munging is to BCP the data from t he ISA MSDE> instance to your main database and let the ISA logs disappear of their> own accord.> This way, you don't have to contend with DB locking from your S&D> missions that cause ISA to go into LDM.>> If you wanna share the process you've created, I'll be happy to file the> bug.> It'll be good to have a SQL-heavy on my side for once.>> --------------------------------------------> Jim Harrison> MCP(NT4, W2K), A+, Network+, PCG> http://isaserver.org/Jim_Harrison/> http://isatools.org> Read the help / books / articles!> --------------------------------------------> -----Original Message-----> From: Thor (Hammer of God) [mailto:thor@xxxxxxxxxxxxxxx]> Sent: Thursday, December 15, 2005 10:20 PM> To: [ISAserver.org Discussion List]> Subject: [isalist] Lockdown Mode>> http://www.ISAserver.org>>> OK, I've tested the availability of System Policy access rules when an> ISA> server is in "lockdown" mode, and they just don't work. So, I'm calling>> "horse-hockey" on that. Has an yone else added remote management rules> to> the System Policy and forced ISA into lockdown mode by not allowing it> to> log, and still accessed the server via RDP or otherwise?>> Further, if you are logging to a SQL database, it seems like you are> *forced* to disable lockdown-mode all-together. Any maintenance at all> to> the ISALog database on the SQL server seems to totally fsk the logging> connection. The ODBC logging is kind of punked anyway-- even though> you're> inserting records to a table with nvarchar and varchar data types, the> ISA> ODBC connector "pads" the data sent. So even if you have a 25 char> ClientUserName, ISA pads the data and fills the field. This is why the> default log file is so damned big-- given this, we HAVE to parse the> data> into something more manageable. 1 Gig per day for 85 or so users is> really> nuts. But I've got my own process that posts into a table of my own> design,> and trims the data in the process. This has to run every n ight-- but> when> it does, ISA punks out on logging, and goes into lockdown mode. And I'm> not> logging to some ghetto box, either-- this is to a cluster of 2 Dell 2650>> dual-proc MoFo's with a half terabyte shared SCSI array. I can> extrapolate> a million decimal places of Pi on these boxes in seconds (I've done it.> My> favorite is the eight 8's in a row at about 300 million).>> If this were documented, it would be OK- but it kind of sucks to build a>> robust infrastructure with detailed logging only to have to disable> lockdown> mode if you do so. I *like* lockdown mode. But I don't like that the> system policy doesn't seem to work in LDM, nor that you have to switch> to> MSDE logging just to run a job to clean up the data that your Enterprise>> Firewall solution is logging...>> Anyone? Beuller? Anyone?>> t>> -----> "I may disapprove of what you say,> but I will defend to the death your> right to say it.">>>> ------------------------------------------------------ > List Archives: http://www.webelists.com/cgi/lyris.pl?enter=isalist> ISA Server Newsletter: http://www.isaserver.org/pages/newsletter.asp> ISA Server FAQ: http://www.isaserver.org/pages/larticle.asp?type=FAQ> ------------------------------------------------------> Visit TechGenix.com for more information about our other sites:> http://www.techgenix.com> ------------------------------------------------------> You are currently subscribed to this ISAserver.org Discussion List as:> jim@xxxxxxxxxxxx> To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist> Report abuse to listadmin@xxxxxxxxxxxxx>> All mail to and from this domain is GFI-scanned.>>> ------------------------------------------------------> List Archives: http://www.webelists.com/cgi/lyris.pl?enter=isalist> ISA Server Newsletter: http://www.isaserver.org/pages/newsletter.asp> ISA Server FAQ: http://www.isaserver.org/pages/larticle.asp?type=FAQ> ------------------------------------------------------ > Visit TechGenix.com for more information about our other sites:> http://www.techgenix.com> ------------------------------------------------------> You are currently subscribed to this ISAserver.org Discussion List as:thor@xxxxxxxxxxxxxxx> To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist> Report abuse to listadmin@xxxxxxxxxxxxx>>
