Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Logon Triggers. A good idea? Or a nerve-racking exprience? Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 3:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 6:01 AM
Points: 32, Visits: 585
Because of security issues, I was requested to keep track of active login accounts so that unused logins can be cleaned up regularly. First option was Setting SQLserver Login Auditing to track both failed and successful logins, but the huge daily errorlogs that got created, was just not handy.

Second option was Logon Triggers which I am currently experimenting with and have already realized how dangerous they can be. ( I am now quite familiar with making DAC connections to my server!!) . I also realized that logon trigger info should best be captured to a table in Master db to make sure the table is available when all sorts of services, including SQLAgent, start up and tries to login.

My questions are:
How many of you are actually using logon triggers, capturing output to a table?
What else should I be aware of before implementing these on my production servers?
What other options are there to track whether a login is still used?




Post #1233837
Posted Wednesday, January 11, 2012 1:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:56 AM
Points: 317, Visits: 877
I'm not using logon triggers, but I did deploy login auditing using Event Notifications and Service Broker. I liked that it was asynchronous and wouldn't impact the end user if there happened to be a problem in my logic.



Colleen M. Morrow
Cleveland DBA
Post #1234352
Posted Wednesday, January 11, 2012 2:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
I use a trace that captures event ID 14. Server-side traces are low-impact, easy to manage, et al. Takes an on-startup proc in master to make sure it starts again after a service restart, but that's easy enough to set up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1234383
Posted Thursday, January 12, 2012 2:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 6:01 AM
Points: 32, Visits: 585
Thank you! These sound both like very good ideas and certainly less problematic if something goes wrong. I have no experience with Event Notification and use of Service Broker and this is a nice opportunity to gain experience.

Implementing a server side trace would be fairly easy, but is there a way to get the results directly into a table other than regurlarly importing the results from out of the results file on the file system?



Post #1234582
Posted Thursday, January 12, 2012 6:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
henriettewhite (1/12/2012)
Thank you! These sound both like very good ideas and certainly less problematic if something goes wrong. I have no experience with Event Notification and use of Service Broker and this is a nice opportunity to gain experience.

Implementing a server side trace would be fairly easy, but is there a way to get the results directly into a table other than regurlarly importing the results from out of the results file on the file system?


You could easily set up a job to run daily (or whatever) to pull the trace data into a table.

But you can query trace files directly, without the import step, by using fn_trace_gettable(). That's what I usually do.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1234708
Posted Thursday, January 12, 2012 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,923, Visits: 32,313
GSquared (1/12/2012)
But you can query trace files directly, without the import step, by using fn_trace_gettable(). That's what I usually do.


taking that one step further, since you can directly query a trace, you can also create a a VIEW that contains that same query.
then it's just a matter of querying the view on demand when you need to review it.


here's a simple example of my default trace:

     CREATE VIEW dbo.VW_Trace_1
AS
SELECT
ev.name ,
tr.*
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_63.trc', default) tr
INNER join sys.trace_events ev
ON tr.eventclass = ev.trace_event_id



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1234796
Posted Thursday, January 12, 2012 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,923, Visits: 32,313
here's a snippet that woudl generate a CREATE view statement for your traces;
default naming convention of VW_TRACE_[traceid]
SELECT
' CREATE VIEW master.dbo.VW_Trace_' + CONVERT(varchar,id) + '
AS
SELECT
ev.name ,
tr.*
FROM ::fn_trace_gettable(''' + path +'.trc'', default) tr
INNER join sys.trace_events ev
ON tr.eventclass = ev.trace_event_id
END '
,*
from sys.traces



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1234797
Posted Thursday, January 12, 2012 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 6:01 AM
Points: 32, Visits: 585
Thanks again! The problem is just that I need to implement this on about 40 servers and schedule a procedure for collecting data to a table. How do I handle the ever changing trace file names? The logon event generates lots of events on some servers and I would therefor have to use rollover which causes the filename to change.

The idea is to, after 3 months, implement a procedure which disables all login accounts which have not been used for 3 months.



Post #1234819
Posted Thursday, January 12, 2012 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,923, Visits: 32,313
I'm under the impression that by using the default parameter, it reads all the automatically created rollover files.
http://msdn.microsoft.com/en-us/library/aa258856(v=sql.80).aspx

Users may specify the default value "default" to tell SQL Server to read all rollover files until the end of the trace.




so if you use the example i made, in theory you should get all the results of the trace data in all rolover files as well as the current.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1234837
Posted Thursday, January 12, 2012 8:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 6:01 AM
Points: 32, Visits: 585
Thanks again! And I think you are quite right, because even when manualy reading from the files, it automatically rolls over when reaching eof.




Post #1234850
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse