Just to add one more thing-- just the act of moving the cluster group for
SQL services to another node punks up the logging and forces LDM. It
recovers by itself (which is cool) but the ODBC logging mechanism needs to
be far more "robust" in regard to it's tolerance in this regard...
t
----- "I may disapprove of what you say, but I will defend to the death your right to say it."
http://www.ISAserver.org
Nice detail! Consider the bug filed.
-------------------------------------------- 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: Friday, December 16, 2005 12:28 AM To: [ISAserver.org Discussion List] Subject: [isalist] RE: Lockdown Mode
http://www.ISAserver.org
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
CREATE CLUSTERED INDEX [IX_CustomWebLog_1] ON [dbo].[CustomWebLog]([ClientUserName]) WITH FILLFACTOR = 90 ON [PRIMARY] GO
CREATE INDEX [IX_CustomWebLog-Greg's-a-Fag] ON [dbo].[CustomWebLog]([logDate]) WITH FILLFACTOR = 90 ON [PRIMARY] GO </snip>
Note that I have my own "SourceSystem" field rather than the default "server" field as I have a default value that add's other information in the field. Most users can prolly use the default "server" field. This table structure is great as I have a clustered index for the user and a standard index for logDate. You can't create the ClientUserName index on the default table because of dev dorkdom.
Since the custom table has field sizes of my own specs, I ensure that only what I need from the ODBC log table gets inserted. This runs as 12:05am
each night..
<snip> Use ISALogs Insert into CustomWebLog (SourceSystem,ClientIP,ClientUserName,ClientAgent,logDate,logTime,servic e,DestHost,DestHostIP,DestHostPort,protocol,transport,
URI,mimetype,resultcode,[rule],FilterInfo,srcNetwork,DstNetwork,ErrorInf o,[Action])selectltrim(rtrim(left(SourceSystem,50))),ltrim(rtrim(left(Cl ientIP,32))),ltrim(rtrim(left(ClientUsername,50))),ltrim(rtrim(left(Clie ntAgent,50))),logDate,logTime,ltrim(rtrim(left(service,16))),ltrim(rtrim (left(DestHost,100))),ltrim(rtrim(left(DestHostIP,32))),DestHostPort,ltr im(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(Src Network,25))),ltrim(rtrim(left(DstNetwork,25))),ErrorInfo,ltrim(rtrim(le ft([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>>
------------------------------------------------------ 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