July 23, 2012 at 8:39 pm
jobs are failing with below error message.
DB ErrorTimeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
When I checked SQL server error log we found below error at same time. also maintenance jobs (update stats)are running at same time which we are considering as primary cause.
SQL error log message:
Error: 18056, Severity: 20, State: 1.
The client was unable to reuse a session with SPID 80, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Message
Error: 17892, Severity: 20, State: 1.
Logon failed for login 'abc\user' due to trigger execution.
Any advice will be highly appreciated!!!
July 24, 2012 at 7:41 am
Have you deployed any Logon triggers in the server?
---------------------------------------------------
Thanks,
Satheesh.
July 24, 2012 at 9:15 am
Yes We have logon trigger.
July 24, 2012 at 11:32 am
Satya_0000 (7/23/2012)
jobs are failing with below error message.DB ErrorTimeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
When I checked SQL server error log we found below error at same time. also maintenance jobs (update stats)are running at same time which we are considering as primary cause.
SQL error log message:
Error: 18056, Severity: 20, State: 1.
The client was unable to reuse a session with SPID 80, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Message
Error: 17892, Severity: 20, State: 1.
Logon failed for login 'abc\user' due to trigger execution.
Any advice will be highly appreciated!!!
Looks like the login account does not have enough permissions (probably "inserts into some table" in trigger).
If that is the case, you can modify trigger and add WITH EXECUTE AS 'sa'
e.g.
ALTER TRIGGER [TriggerName]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
Here 'sa' is just an example, you can use suitable acount
July 24, 2012 at 11:56 am
What does the login trigger do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2012 at 11:57 am
Daxesh Patel (7/24/2012)
Looks like the login account does not have enough permissions (probably "inserts into some table" in trigger).
Where do you see a permissions related error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2012 at 12:24 pm
GilaMonster (7/24/2012)
Daxesh Patel (7/24/2012)
Looks like the login account does not have enough permissions (probably "inserts into some table" in trigger).Where do you see a permissions related error?
You are right, this is not permission related (my apologies).
This can be a ROLLBACK in logon for some reason
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply