security events in sql 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

security events in sql 2008

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-05-05 : 10:31:13

can we capture below security events in SQL Server 2008

successfull logins and connections to sql server
logoffs and disconnects from sql server
thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-05 : 10:50:57

SQL Server Profiler -- >Security Audit -- > Audit Login & Audit Logout
Best way to get information for these events is SQL Diag

--------------------------
http://connectsql.blogspot.com/

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-05-05 : 10:53:31

Is it one time audit?
I want to enable this everyday, please let me know

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-05 : 11:44:39

Capture these events in a server side trace.

Audit Login
Audit Logout

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-05-05 : 11:52:18

Thank you.

I could see Audit option in Security in version SQL 2008.

Can I do this in creating audit in sql 2008 ? any idea

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-05-05 : 12:38:55

and also tracking connections to SQL, by default sql writes to log or do we need to enable ? thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-05 : 13:06:43

By default, it write failed conections to the event log. you can change to write failed and successful ones, but it won't write logouts. Which is why I suggest a trace for this.

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-05-05 : 16:43:05

thank you.
but I need to track the attempted connections including failed/success logins. How do I track the connections to SQL Server

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-05 : 16:57:19

Via a trace.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-05-05 : 21:50:06

I meant event name to trace

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-05 : 22:29:15

quote:
Originally posted by russell

Capture these events in a server side trace.

Audit Login
Audit Logout

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-06 : 08:05:28

create a Server level trigger or we can say Logon Trigger on the SQL Server,create a table auditTab with 2 columns login and logindate then create a trigger

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
Insert into auditTab values(ORIGINAL_LOGIN(),getdate())
END;

and check EVENTDATA function for further information

http://msdn.microsoft.com/en-us/library/ms173781.aspx

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 08:12:57

^ That's not a good idea. And it only solves half of the problem.

A server side trace is the right approach.

Unless the OP needs to perform immediate actions when a logon or logoff occurs (very rare), then Service Broker is the tool for the job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources