ansaurus

Question

Logon failed for login 'user' due to trigger execution.

Answer 1

+2 A:

You could double check for the presence of triggers with:

use YourDb
select * from sys.objects where type = 'TR'

EDIT: This looks like ASP.NET membership, which ends up in a user database (it's not SQL Server logins.) Can you see what connection string the ASP.NET membership provider uses? It's usually in the Web.config, for example:

<providers>
  <add name="AspNetSqlMembershipProvider"  
      ...
      connectionStringName="<<connection string>>"

Andomar 2010-09-27 11:59:33

Or select * from sys.triggers

GalacticCowboy 2010-09-27 12:01:14

I checked the sys,objects where typer = 'TR' and received back an empty result. There seem to be no triggers on my database. What do you mean by the asp.net snipped that is throwing the exception? I can not even access the site before the error is thrown

EvanGWatkins 2010-09-27 12:02:50

@Evan - look in `master.sys.server_triggers`

Martin Smith 2010-09-27 12:06:37

@Andomar - the snippet has been added to the original post on where I first get the error

EvanGWatkins 2010-09-27 12:07:24

@Martin - there are no results wheen running select * from master.sys.server_triggers

EvanGWatkins 2010-09-27 12:09:08

@Evan - Are you sysadmin on the machine?

Martin Smith 2010-09-27 12:10:13

@Martin - I have admin rights but I am not logged in as sa, the password that worked until friday no longer works for sa...

EvanGWatkins 2010-09-27 12:11:59

@Evan - The definition of `sys.server_triggers` only returns rows meeting the following `has_access('TR', o.id, o.pid, o.nsclass) = 1` so it's possible (likely) that you can't see the logon trigger with your current credentials. Why can't you login as sa? Has the password changed or are you getting the same error about trigger execution? If the password has changed have you been hacked? Or has someone in your company making changes?

Martin Smith 2010-09-27 12:17:15

@Martin - I do not think we were hacked and no one in my office has said they made any changes. Is there a way to override the sa password if I log into the server physically to get in as sa?

EvanGWatkins 2010-09-27 12:20:59

@Evan - If you log into the server using Windows Authentication as an admin then hopefully you should be able to connect. What error message do you see when you try and log in as sa? (I'm trying to establish if the issue is just a buggy logon trigger or something else)

Martin Smith 2010-09-27 12:23:41

@Martin - I can log in with windows authentication just fine, when I try to log in as sa I get a login failed for user 'sa'

EvanGWatkins 2010-09-27 12:26:45

@andomar - My connection string appears fine

EvanGWatkins 2010-09-27 12:28:38

@EvanGWatkins: Then it sounds like IIS gets an error while trying to login to SQL Server. Check out GBN's answer, especially the DAC connection in Pinal Dave's post.

Andomar 2010-09-27 12:39:35

@martin @Andomar - I got the password by using sqlcmd scripts on the server, when logged in as sa I still do not see any triggers

EvanGWatkins 2010-09-27 13:22:56

@Evan - Very strange. Anything of interest in the default trace? `declare @filepath nvarchar(1000) SELECT @filepath = cast(value as nvarchar(1000)) FROM [fn_trace_getinfo](NULL) WHERE [property] = 2 and traceid=1 SELECT * FROM [fn_trace_gettable](@filepath, DEFAULT) ORDER BY StartTime DESC;`

Martin Smith 2010-09-27 13:30:03

@Martin - We have decided to back up everything and restore to a new server, it appears we may have had some unauthorized visitors on the server.... Thanks for all of your help

EvanGWatkins 2010-09-27 14:04:59

Answer 2

+2 A:

Login failed comes is generated at the server level, not the database level.

That is, you may have a LOGON trigger. You'd check for this in sys.server_triggers.

Now, there is at least one known issue described on MS Connect as well as some investigation+potential fix by Pinal Dave (YMMV)

gbn 2010-09-27 12:18:49

ansaurus

tags:

views:

answers:

Logon failed for login 'user' due to trigger execution.

related questions