Best Practice for MS SQL 2005 Logging

[RSAAdmin]

We have to log SQL 2005, and the default logging method requires turing on OLE Embedding and XP_Cmdshell.

These 2 methods are against all hardening best practices.

How are people monitoring SQL 2005 databases?

Also we are getting a NULL value in the dbo_nic_aud_trace table for curr_trace_ID.  When this happens the logging stops.  Anyone know why this value is getting NULL?

Thanks

Tim **bleep**

[RSAAdmin]

TIM

The null value should not be stopping collection.  Have you opened a case with support?

[DavidBruskin]

Tim -

There is no great way to log SQL. Unlike Oracle, the logging structure mechanism's are differen't. SQL 2k5 is less intrusive as you only need specific priv's to call XP_CMD_Shell as 2k it had to be SA. With that being said, the best I have seen using a trace vai ODBC pull to execute and retrieve is with mapped permissions, launch stored proc, collected, extract to LC, all via odbc connection. Since as I agree with you, XP_Cmd_shell is a no no, I have also seen where the MSSQL api to dump events to the local event viewer has been used. This creates a load on the OS, and a buffer increase in the event viewer, but keeps the logs in the event viewer. Events then being collected from enVision is done via RPC and remote registeries, which then is not encrypted, but hash'd to an extend. Also, you are able to use lesser account priv's as you dont need domain admin's if you tweak the registry to extract logs via windows agentless. Doing so would require a custom XML to be written, but it would be much safer. You can google the MSSQL API or piece of code that will dump the SQL events to the OS without XP_cmd_shell.

let me know if you have any questions, comments, concerns...

/db

Message Edited by dbruskin on09-23-200810:15 AM

[RSAAdmin]

I find that when the NULL value exists, the trace file continues but doesn't roll over to a new one, and thus stops when the trace file reaches maximum size or time.

In order to keep the SQL Best practices we have created an assembly which specifically handles the deletion of the TraceFile.  This is the only reason why xp_cmdshell was required by RSA EnVision.  Our assembly, which is signed, handles the file deletion, but can't delete the file if there is a NULL value in the current_trace_ID.

Since we have deviated from the norm, RSA EnVision is reluctant to help

Has anyone else seen this?

Thanks

Tim

[RSAAdmin]

Thanks for the Post DB

Kudos

We have tackled this by using a Signed assembly to handle the TraceFile deletion.

Works most of the time, but fails consistantly on a SQL cluster.  That is the NULL value we keep getting.

BTW we are usiung 3.7.0 build 0169

Thanks

Tim

[RSAAdmin]

I have a DBA who is asking if he can run the script localy to create the trace file, has anyone tried this? Collectiong the trace file could be retreived vis SFTP but I am not sure if this would work in getting the trace file into envision to parse. Anyone ever try this?

[RSAAdmin]

It's been a year since this post soooo has anyone come up with a better way to collect SQL logs?