SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logon Triggers. A good idea? Or a nerve-racking exprience?


Logon Triggers. A good idea? Or a nerve-racking exprience?

Author
Message
henriettewhite
henriettewhite
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 677
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?



Colleen M. Morrow
Colleen M. Morrow
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 1060
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23413 Visits: 9730
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
henriettewhite
henriettewhite
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 677
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?



GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23413 Visits: 9730
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28123 Visits: 39939
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28123 Visits: 39939
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

henriettewhite
henriettewhite
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 677
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.



Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28123 Visits: 39939
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

henriettewhite
henriettewhite
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 677
Thanks again! And I think you are quite right, because even when manualy reading from the files, it automatically rolls over when reaching eof.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search