MS SQL trc log files

Which files in particular are you referring to? The ERRORLOG and SQLnnnnn files are ASCII text and any text editor can view them. I just googled up this and it looks promising (and free).

http//www.xplg.com/home/products/xpologCenter.jsp

"Which files in particular are you referring to? "

I'm sorry, I thought I was clear on that…I'll try to clarify.

I'm interested in viewing the files that end in .trc. I was working with someone from MS this morning, as I was examining a case, and I need to find some definitive information that may be maintained in those .trc files.

Here's my issue…I'm looking at a SQL Server (on Win2K3 SP1) that had been hammered for a LONG time with attempts to log in via port 1433. The default set up applies…LoginMode is mixed (which means local and remote) and by default, only failed login attempts are logged. These attempts are logged to the ERRORLOG.n files, as well as to the Application Event Log. Even though auditing of successful Logon and Account Logon events is enabled via the OS, there are no signs of any successful logins.

The guy from MS I was talking to this morning said that in the .trc logs, there may be some signs of a successful login based on object creation and deletion messages in the .trc files. Looking at the .trc files in ProDiscover, I can see failed login messages, and I know that by default, the successful logins aren't recorded, but after a login has succeeded, there may be some objects created to indicate that successful login.

Thanks for the link to the tool, BTW…but it doesn't cover this situation.

Thanks,

Harlan

Harlan,
I came across this - not sure if it helps though.

The content of the audit files can be viewed using SQL Server Profiler (you can also import them into a new or an existing table). Alternatively, you can use for this purpose a built-in function fn_trace_gettable, which displays the content of a trace file in a table format (the following sample T-SQL command can be executed from the SQL Query Analyzer)

SELECT * FROM fn_trace_gettable('C\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
GO

I can't seem to find much else on the subject in the category of free.

hogfly,

Thanks. I'd found all of that already, and I don't have the SQL Server CD, so I can't install the client tools. I guess I could boot the image via LiveView, but I'm not a DBA, and to be honest, that's more than I really think I need to do…after all, not even MS can tell me about these files and whether I should be seeing anything related to what I'm looking for in them.

Googling, I did find some information on a C# app that is supposed to view trace files, but there wasn't enough info in the article for me to reconstruct it in Perl, and the final EXE wasn't available.

Thanks,

H

…LoginMode is mixed (which means local and remote)

Mixed Mode related to SQL Server means that to you can login with user accounts definded in SQL Server (an example is the sa account) and also using Windows Users, you can use both types to login local or remote.

Even though auditing of successful Logon and Account Logon events is enabled via the OS, there are no signs of any successful logins.

SQL Server logging has to be configured seperately from the OS. But if Logon events are audited by the OS as you mentioned it you should see at least a Windows logon event (538/540) in the security log each time somebody starts a session on the SQL Server.

"SQL Server logging has to be configured seperately from the OS."

Yes, I know.

"But if Logon events are audited by the OS as you mentioned it you should see at least a Windows logon event (538/540) in the security log each time somebody starts a session on the SQL Server."

Okay, this is where I don't follow…particulary because this isn't what the SQL guys from MS said.

If someone logs into the 'sa' account on MS SQL server, over the network (on port 1433), you're saying that there should be successful Windows logon events in the Security Event log…correct?

I used to do pen testing and assessments full time and I used an app that would connect to the 'sa' account with a given password, and let you run commands via the xp_cmdshell stored procedure, as LocalSystem. The thing I liked is that there weren't any entries in the Security Event Log showing the logon.

Harlan

If someone logs into the 'sa' account on MS SQL server, over the network (on port 1433), you're saying that there should be successful Windows logon events in the Security Event log…correct?

I used to do pen testing and assessments full time and I used an app that would connect to the 'sa' account with a given password, and let you run commands via the xp_cmdshell stored procedure, as LocalSystem. The thing I liked is that there weren't any entries in the Security Event Log showing the logon.

Harlan

You are absolutely right, there are no traces in the security log when you log on via port 1433.

What misleaded me is that our app not only connects to the SQL Server but also gets some configuration data from a shared directory on the same server.

Sorry, I should have looked into that more careful before posting…. (

"Slightly" off topic
but…
Perhaps, you could use the network. Look at the network (firewall) logs for the time period around the failed logons and look for packet sizes (outbound) to the same DST, as the failed logons, that are larger then your typical LOGON FAILED response…Then you know that something was returned to that system, probably a response to a query (meaning that you must of had a successful logon attempt before that).

Maybe if entries in .trc are time stamped you could correlate them….

*shrug*
doubt that helps…but..
Skip

An late reply but here goes

A good freeware tool for SQL Server would be Toad (www.toadsoft.com)

I assume your attempting some kind of forensic accounting on SQL, in which case I'd take a backup of the databse and restore to a separate location, then analyse the logs by querying the system tables.

Most dba's disable xp_cmdshell as its a security risk.

SQL is configurable, ie no logging, log all, log failed etc, there's also detailed logging and live logging (through profiler)

I'd suggest you tie up the SQL Logs with the servers event logs.

best of luck