March 6, 2017 at 9:38 am
Ok, so to confirm - there is only 1 logon trigger, correct?
If so, and that is the entire query, I would still create a logging table to watch the failed logins and report back the user and application. The only risk that could come from that is that somebody does something to the logging table to create either blocking or a deadlock, but neither case should occur with normal use.
Do you have any 3rd party SQL monitoring apps (redgate, idera, etc)? Those should give you more insight into what happened. Otherwise you are just grasping at straws.
Did you have any SQL jobs running at that time? I'm guessing that something changed for those 20 minutes or somebody tried to connect to the database with a banned app.
As another thought - is the only thing missing from the trigger you posted a "BEGIN" statement? If not, could you post the entirity of your trigger? Are you assigning any variables that are not using a MAX or TOP 1 or anything else that might cause it to have multiple values?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 6, 2017 at 12:49 pm
Steve Jones - SSC Editor - Monday, March 6, 2017 8:00 AMIf that's all that's in the trigger, I wouldn't expect a failure, but I always worry about lots of AND/ORs and something slipping through I didn't expect.Do you have auditing on the trigger? Could someone have changed it and changed it back?
thought of that and checked sys.server_triggers and it had a modify date of 25 minutes before the outage started but nothing after that. i'm thinking that either SQL updated it for some reason or it might have been disabled and enabled again since it didn't change.
March 6, 2017 at 12:49 pm
bmg002 - Monday, March 6, 2017 9:38 AMOk, so to confirm - there is only 1 logon trigger, correct?If so, and that is the entire query, I would still create a logging table to watch the failed logins and report back the user and application. The only risk that could come from that is that somebody does something to the logging table to create either blocking or a deadlock, but neither case should occur with normal use.
Do you have any 3rd party SQL monitoring apps (redgate, idera, etc)? Those should give you more insight into what happened. Otherwise you are just grasping at straws.
Did you have any SQL jobs running at that time? I'm guessing that something changed for those 20 minutes or somebody tried to connect to the database with a banned app.As another thought - is the only thing missing from the trigger you posted a "BEGIN" statement? If not, could you post the entirity of your trigger? Are you assigning any variables that are not using a MAX or TOP 1 or anything else that might cause it to have multiple values?
USE [master]GO/****** Object: DdlTrigger [userrw_trigger]
Script Date: 3/3/2017 3:55:13 PM ******
/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [userrw_trigger]ON ALL SERVER FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()in ( 'user1', 'user2','user3', 'user4') AND (program_name() like '%SQL %' or program_name() like 'Microsoft® Access' or program_name() like '%Microsoft Office %' or program_name() like '%Aqua%')
ROLLBACK
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [userrw_trigger] ON ALL SERVER
GO
March 6, 2017 at 3:19 pm
I don't see anything in that trigger that should cause any failure unless, as you suggested, somebody modified the trigger and rolled back their changes OR the application was updated and those changes were rolled back.
Might not hurt to keep a log though of who and/or what was trying to log in incorrectly.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 6, 2017 at 3:25 pm
If this is the approach you have to use, I think you should capture every login name, and every program name for every login.
Examine the results of that after a few weeks, and I'm willing to bet that there may be some surprises in there.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2017 at 6:47 pm
been doing that since last year due to some other issues. did catch a few people doing minor things they aren't supposed to and the trigger was updated 3 weeks ago by adding another SQL login to it but nothing too major in there.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply