January 20, 2009 at 1:14 pm
Hi,
I have implemented an auditing solution that uses 3 different trace files. My ultimate goal was to have a static trace id for each type of audit (DDL,DML,Logins).
The basic process goes like this...
1. stop and unload the trace file
2. populate a table in a seperate audit database
3. run a dos batch file to rename the trace file with a timestamp
4. re-create and start a new trace file.
this works 95% of the time. However, there are some times where where the job seems to be failing to re-create the trace, due to a file with the same nme already exisiting...
Could not create a trace file. [SQLSTATE 42000] (Error 19062) Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).
And this causes a whole load of errors in the subsquenced jobs since even though, I'm statically assigning trace id's, SQL server still uses the next value. So in other words, if trace id #2 fails with the error above which is supposed to be for failed logins, the next trace id created (which is DML in this case), used trace ID#2, instead of it's statically assigned value of 3. So next time the DML trace job runs it stops trace ID 3, then can't read the required trace files cause SQL server decided to use trace id #2 from it's previous job...
exec sp_trace_setstatus 3, 0
exec sp_trace_setstatus 3, 2 ...(for trace id=3)
...
SET @strTraceFile = 'D:\MSSQL\reports\audit\audit_dml'
SET @Traceid = 3
/* Create the server side trace profile */
exec @return_code = sp_trace_create @traceid output, 2, @strTraceFile, @maxfilesize, NULL
if (@return_code != 0) goto error
I've tried to put in a delay before renaming and re-create the trace file, thinking that sql server didn't have enough time to read from the trace file before trying to create a new one. I also spread the sql server jobs apart by 5 minutes... but one of the jobs still randomly fails
So I guess I have 2 questions out of all this...
1. Can Trace id be manually set, and if so,.. what am I doing wrong?
2. Why does the first error happen in the first place and how to fix it?
Any help is appreciated...
Thanks
January 20, 2009 at 2:00 pm
Norm (1/20/2009)
Hi,I have implemented an auditing solution that uses 3 different trace files. My ultimate goal was to have a static trace id for each type of audit (DDL,DML,Logins).
The basic process goes like this...
1. stop and unload the trace file
2. populate a table in a seperate audit database
3. run a dos batch file to rename the trace file with a timestamp
4. re-create and start a new trace file.
this works 95% of the time. However, there are some times where where the job seems to be failing to re-create the trace, due to a file with the same nme already exisiting...
Could not create a trace file. [SQLSTATE 42000] (Error 19062) Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).
And this causes a whole load of errors in the subsquenced jobs since even though, I'm statically assigning trace id's, SQL server still uses the next value. So in other words, if trace id #2 fails with the error above which is supposed to be for failed logins, the next trace id created (which is DML in this case), used trace ID#2, instead of it's statically assigned value of 3. So next time the DML trace job runs it stops trace ID 3, then can't read the required trace files cause SQL server decided to use trace id #2 from it's previous job...
exec sp_trace_setstatus 3, 0
exec sp_trace_setstatus 3, 2 ...(for trace id=3)
...
SET @strTraceFile = 'D:\MSSQL\reports\audit\audit_dml'
SET @Traceid = 3
/* Create the server side trace profile */
exec @return_code = sp_trace_create @traceid output, 2, @strTraceFile, @maxfilesize, NULL
if (@return_code != 0) goto error
I've tried to put in a delay before renaming and re-create the trace file, thinking that sql server didn't have enough time to read from the trace file before trying to create a new one. I also spread the sql server jobs apart by 5 minutes... but one of the jobs still randomly fails
So I guess I have 2 questions out of all this...
1. Can Trace id be manually set, and if so,.. what am I doing wrong?
2. Why does the first error happen in the first place and how to fix it?
Any help is appreciated...
Thanks
Did you grant the service account write authority to the folder:
D:\MSSQL\reports\audit\
You can also find a sample in my little article ...
http://www.sqlservercentral.com/articles/Security/3203/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 20, 2009 at 2:06 pm
oh yeah, as I mentioned, it works like 95% of the time... for some odd reason, about once a week it will fail randomly, I'm trying to develop a pattern but I can't find any connections with anything... different days of the week, different job etc... I wish it could at least be consistent :crazy:
January 22, 2009 at 12:58 am
Does your "3. run a dos batch file to rename the trace file with a timestamp" succeed when the start new trace fails ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 22, 2009 at 6:30 am
nope, I think thats where the problem lies.... Is there a way to do this withot having to use xp_cmdshell, I know using this presents security hole. maybe this can help the procedure run a little more smoothly. basically all it does is renames the trace file and adds a timestamp to it in order for a new trace file to be created. here's that section of the store procedure..
-- #-------------------------------------#
-- # Load trace file into auditing table #
-- #-------------------------------------#
BEGIN TRY
INSERT INTO audit.dbo.audit_ddl
(StartTime, EndTime, EventClass, TextData, NTUserName, DatabaseID, DatabaseName, HostName, LoginName, DBUserName, ApplicationName, ServerName)
SELECT StartTime, EndTime, EventClass, TextData, NTUserName, DatabaseID, DatabaseName, HostName, LoginName, DBUserName, ApplicationName, ServerName
FROM ::fn_trace_gettable('d:\mssql\reports\audit\audit_ddl.trc', 1)
WHERE (textdata like '%DROP%' or TextData like '%ALTER%' or TextData like '%CREATE%')
and textdata not like '%#tmp%'
and textdata not like '%sys.%'
and textdata not like '%sv.%'
and textdata not like '%Has_Perms%'
-- and databasename not like 'LandatLands'
END TRY
-- #--------------------------------#
-- # Rename tracefile for archiving #
-- #--------------------------------#
BEGIN CATCH
IF ERROR_NUMBER() = 567 --skips rename script if file does not exist
GOTO skip
END CATCH;
WAITFOR DELAY '000:00:20' --delays for 20 seconds
EXEC xp_cmdshell 'd:\mssql\scripts\audit\rename_ddltrace.bat', NO_OUTPUT
skip:
-- #---------------#
-- # Setup tracing #
-- #---------------#
DECLARE @strTraceFile nvarchar(128)
SET @strTraceFile = 'D:\MSSQL\reports\audit\audit_ddl'
SET @Traceid = 4
January 22, 2009 at 7:35 am
- Did your stop trace work ?
- Since you are using "EXEC xp_cmdshell 'd:\mssql\scripts\audit\rename_ddltrace.bat', NO_OUTPUT"
Why don't you just provide the rename statement itself ?
Dim @Doscmd nvarchar(max)
Set @Doscmd = 'ren D:\MSSQL\reports\audit\audit_ddl.trc audit_ddl_' + convert(nvarchar(26),getdate(),121) + '.trc'
EXEC xp_cmdshell @Doscmd, NO_OUTPUT
or just run them in separated steps in a job and using one step for handle the dos commands natively. (operating system type)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 22, 2009 at 9:36 am
I actually did have the renaming part done as a seperate step in the scheduled job, but there was one reason why I put it directly in the SP, I think it was due to the try and catch to make sure the file existed before trying to pull information from a trace file that didn't exists... although come to think of it, I could simply just use the "go to step #" option on failure instead... That would also elminate the need for the delay, since it already has to wait for the step to complete before proceeding. (I'm not even sure if it's actually resolving my problem in the first place).
question: Would xp_cmdshell still have to be enabled if I run it as a operating system (cmdexec) type in my sql server job?
Thanks for the advice, I think I'll try to implement this in a seperate instance, and compare the two options side by side... This problem doesn't exist in production, thankfully, but I'm also capturing DDL using triggers instead of trace files. Which we discussed as a group as not being a valid solution, since all users would need write access to the audit database as well... due to application functionality. I didn't even think of that problem since I have sysadmin option and when I was testing it, everything was working fine for me,... (obviously) 🙂
Anyhow, looks like I have some work to do...
Thanks again.
January 22, 2009 at 12:45 pm
Norm (1/22/2009)
....question: Would xp_cmdshell still have to be enabled if I run it as a operating system (cmdexec) type in my sql server job?
No, you can disable xp_cmdshell, SQLAgent has a separate set of authorities to grant use of cmdshell. Jobs owned by sa have the auth to execute "operating system" typed steps.
Thanks for the advice, I think I'll try to implement this in a seperate instance, and compare the two options side by side... This problem doesn't exist in production, thankfully, but I'm also capturing DDL using triggers instead of trace files. Which we discussed as a group as not being a valid solution, since all users would need write access to the audit database as well... due to application functionality. I didn't even think of that problem since I have sysadmin option and when I was testing it, everything was working fine for me,... (obviously) 🙂
Anyhow, looks like I have some work to do...
Thanks again.
With sql2005 you can create a trigger at db level , using the execute as clause with a higher privileged login to write into your other db audit table.
(with auth on both db)
(I'll have to test that again)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply