March 2, 2017 at 11:44 am
we have 2 triggers on one of our SQL servers. One checks for one of 4 logins and 4 apps and then denies login. another records some DDL changes. and we have a total of a few dozen logins split between windows and SQL
yesterday morning we had a semi-outage and the SQL log says a trigger was fired for two logins which aren't in the trigger
Logon failed for login <'appuser'> due to trigger execution. [CLIENT: 10.1.7.x] was in the SQL log every few minutes for twenty minutes and then went away. starting around 9:30am to around 9:48am.
I checked sys.triggers and the last modify date of that trigger was 03-01-2017 at 9:05am which I am guessing is SQL doing something to it because that trigger had last been changed two weeks ago and hasn't been touched since. and even if someone did something it was outside the bounds of the outage
in one of the application logs for one of the apps affected we saw the same message. Is there any way to track down why the trigger fired for users that aren't in it's scope?
March 2, 2017 at 12:00 pm
After the fact that is a difficult thing to do. To capture why the trigger fired, a trace or audit needs to be running at the time of the incident to capture it. If you have some sort of monitoring that traps info from SQL Server, then maybe you can get the "why".
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 2, 2017 at 12:01 pm
What's the trigger code? I thought the trigger fire for all logins, just it's actions depended on the code inside.
March 2, 2017 at 12:08 pm
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
we've had this trigger for years now and recently we have only added user4 a few weeks ago. Due to SOX, it's to keep developers out of production SQL
yesterday for around 20 minutes we have errors in the sql log that other sql logins that aren't in this trigger failed logon due to the trigger. checking the log we also saw that one of these users triggered the trigger a few weeks ago but didn't have an outage
all the cases of the trigger being triggered were from application servers with no SQL installed on them and no MS Access or Aqua Data Studio. even if they had it installed it shouldn't have set the trigger off
March 2, 2017 at 12:27 pm
is this the actual code? If so, it looks like you are missing a "BEGIN".
Also, the %SQL % may be returning true for a number of different applications.
If you are trying to prevent developers from accessing production, a login trigger may not be the best way to go.
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 2, 2017 at 12:34 pm
that's the most important code. didn't include the start and the end from when you script it out. we rarely see it triggered and the applications don't start with SQL. even then the users affected aren't in the trigger.
we have apps connecting with each of those logins all the time. we're trying to block anyone with these SQL logins from accessing the server with MS Access or SSMS, Aqua data studio
i'm trying to figure out why logins and applications not in this trigger set it off
March 6, 2017 at 7:25 am
alen teplitsky - Thursday, March 2, 2017 12:34 PMthat's the most important code. didn't include the start and the end from when you script it out. we rarely see it triggered and the applications don't start with SQL. even then the users affected aren't in the trigger.we have apps connecting with each of those logins all the time. we're trying to block anyone with these SQL logins from accessing the server with MS Access or SSMS, Aqua data studio
i'm trying to figure out why logins and applications not in this trigger set it off
Silly question. Why would the devs have the ability to log into the server to begin with? Have they not been removed from server and database security yet?
Or are you searching for SQL Logins that the devs may have the passwords to?
March 6, 2017 at 7:52 am
we have some read only sql logins going back many many years where people have given out the passwords and so on. about a decade ago we started to move to AD integrated logins but we still keep these sql logins around because they are used in many applications.
so we use triggers to make sure people can't log in with those logins from SSMS or MS Access because everyone knows they aren't supposed to go into production but sometimes people still do
March 6, 2017 at 7:59 am
alen teplitsky - Monday, March 6, 2017 7:52 AMwe have some read only sql logins going back many many years where people have given out the passwords and so on. about a decade ago we started to move to AD integrated logins but we still keep these sql logins around because they are used in many applications.so we use triggers to make sure people can't log in with those logins from SSMS or MS Access because everyone knows they aren't supposed to go into production but sometimes people still do
Then may I suggest you find out where these applications store the password information, then go in and change it there and on the servers?
If you haven't changed the passwords of these SQL logins in over a decade, it's well past time as part of good security practices and to prevent past employees from sharing the information with hackers or from hacking into the system themselves.
March 6, 2017 at 8:00 am
If 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?
March 6, 2017 at 8:25 am
it's been working for years except for 20 minutes last week when it fired off for users and applications not in the trigger
March 6, 2017 at 8:40 am
I would create a logging table and modify the trigger to dump the user and program into the logging table table and any other useful infromation you want for logging (user machine perhaps). I would have a column that is auto-populated with the current date and time as well and have an identity column that is the primary key so you can quickly and easily sort it on the identity column (lowest value would be the oldest date/time and highest value would be the newest).
That way you can see what is causing it to trigger.
Has any of your software that connects to that been modified recently? It could be they did something silly like changing the identifying application name to something that got hit in the trigger.
Since you can see the login name, you likely don't need to track that in the logging table.
Doesn't help to retroactively see why things are failing, but I presume that the issue is ongoing when a specific user tries to run the specific application or do a specific task? I imagine this isn't intermittent?
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 8:45 am
there was an app roll back right before to a previous version but the trigger is only supposed to fire for SSMS and MS Office Apps along with Aqua Data Studio which is kind of like SSMS but supports lots of other databases
in this case it fired for users and apps not defined in the trigger. instead of a table, looking to add the detail to an email when it fires
March 6, 2017 at 8:57 am
alen teplitsky - Monday, March 6, 2017 8:45 AMthere was an app roll back right before to a previous version but the trigger is only supposed to fire for SSMS and MS Office Apps along with Aqua Data Studio which is kind of like SSMS but supports lots of other databasesin this case it fired for users and apps not defined in the trigger. instead of a table, looking to add the detail to an email when it fires
Yes, I do understand that. But in an application, you can have it report back as anything you want to SQL. It is a configurable value. It could be that they have it sending back to SQL that the application name is "SQL STEVE" (for example) and that'd get caught by your trigger.
We don't configure ours most of the time, so SQL gets back something like .NET application (or whatever the default is for visual studio SQL connections). It could be some developer thought it'd be handy to have it named something they thought was more useful, but resulted in getting caught by your trigger.
On a side note, you could likely simplify your trigger by replacing the whole program_name section with:(program_name() like '%Microsoft%' or program_name() like '%Aqua%')
Since SSMS reports back as "Microsoft SQL Server Management Studio". That is unless you have other Microsoft products that connect to it that you WANT connecting to it.
Since you know the application that caused that, can your end users reproduct the problem? If so, do you have a test system they can reproduce it on? If so, I'd run profiler/extended events session and see exactly what comes across when the user does stuff. See what username and program name comes out.
As a thought though, you mentioned a second trigger... is that a logon trigger as well? If so, is there a chance that that trigger could be failing? A trigger failure would cause the login to fail too. One other thing to check would be if either trigger does any sort of insert, is there a case where the insert could fail (inserting too many characters into a column, inserting null in a non-null column, identity values exceed data type, foreign key violations, etc)?
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 9:17 am
i believe older versions of Access report as Microsoft Access and newer versions are Microsoft Office. and we still have lots of old versions being used. it's a few apps that were affected and they run tens of millions of commands daily and this happened only for 20 minutes last week. apps weren't restarted, sql wasn't restarted, it just went away. only thing we verified was that the IP addresses from the machines that set it off were from the app servers these apps run on
the second trigger is to log any DDL events and i've testing in QA with it to try to set it off with similar conditions as production and haven't had luck yet
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply