log file for SQL logins auditing

Question

Post reply

log file for SQL logins auditing

tvu4251

Mr or Mrs. 500

Points: 510
More actions
March 23, 2007 at 1:57 pm

#68898

I would like to enable the SQL login auditing but I do not want to touch the Application Event Viewer Log. The Event Log on my server is completely filled up with auditing info, makes it very hard -almost impossible- to track any other events. Is there a way to achieve this?
By default, SQL Server writes all day-to-day operations including startup, shutdown, backups, server-side traces, audit login activity to the Windows Application log. I also see these info written in the SQL Error Logs in the SQL Server folder. Are these two the same?
Thank you.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Then use sql trace/profile to do auditing....

What kind of auding you want to do?

MohammedU
Microsoft SQL Server MVP

tvu4251 Mr or Mrs. 500 Points: 510 More actions · Answer 2

For starter, I want to know who and when some one logs in the database. The the built-in SQL security auditing does just that (logins success/failure) but I want the results to be written in my own log file, NOT in the system Application Event Viewr log.

Thanks,

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 3

It should also write to the SQL Server error log. But if you want it completely separate, as has been previously mentioned, use a SQL trace / Profiler. If it's a permanent thing, you can create a startup stored procedure which automatically starts up the trace every time SQL Server is restarted.

K. Brian Kelley
@kbriankelley

george sibbald SSC Guru Points: 104210 More actions · Answer 4

you could start sqlserver with the -n flag, then sql server events (ANYof them) are not posted to the windows application log

---------------------------------------------------------------------

tvu4251 Mr or Mrs. 500 Points: 510 More actions · Answer 5

Thank you all for the info.

George et al, to use the -n flag, do I have to create a new key in the registry? for example, SQLArg3 with value -n? Thanks.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

no need to update registry, put it in startup paramaters via enterprise manager along (right click server, properties) with the -e, id, -i. Just add another entry -n

if you start from a comamnd line just add -n parameter to sqlserver.exe command

please be aware this stops all sql server events logging in the windows application log , leaving logging in sql errorlog only. See BOL

---------------------------------------------------------------------

tvu4251 Mr or Mrs. 500 Points: 510 More actions · Answer 7

tvu4251

Mr or Mrs. 500

Points: 510

March 26, 2007 at 8:11 am

#696876

G, thank you very much.

tvu4251 Mr or Mrs. 500 Points: 510 More actions · Answer 8

a little detail: does the server need a reboot? or just a SQL Server service restart is enough? Thnaks.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 9

Event logs have lots of filters. You should be able to move out the SQL Login events easily.