I have been trying to login using one of my sql server logins but I am getting the error message below:

General Error Message

enter image description here

Copy Message Text

TITLE: Connect to Server
------------------------------

Cannot connect to MY_SERVER.

------------------------------
ADDITIONAL INFORMATION:

Logon failed for login 'cola' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Advanced Information

enter image description here

I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that's why I am putting this question here:

“Logon failed for login 'sa' due to trigger execution.” When No SA Login Triggers Defined

This question here is very similar too:

Login failed for user - Error: 18456, Severity: 14, State: 38

And from this one I got the following info:

Login failed for user - Error 18456 - Severity 14, State 38

1      'Account is locked out'
2      'User id is not valid'
3-4    'Undocumented'
5      'User id is not valid'
6      'Undocumented'
7      'The login being used is disabled'
8      'Incorrect password'
9      'Invalid password'
10     'Related to a SQL login being bound to Windows domain password policy enforcement.
        See KB925744.'
11-12  'Login valid but server access failed'
16     'Login valid, but not permissioned to use the target database'
18     'Password expired'
27     'Initial database could not be found'
38     'Login valid but database unavailable (or login not permissioned)'

According to the information above I have the following issue:

Login valid but server access failed

Still no joy.

I tried to drop the login and re-create it, but encountered a Login can not be dropped because it is in use.

I used the script below to find out where it is used, by whom, from where:

USE master
go
SELECT 
        sdes.session_id 
       ,sdes.login_time 
       ,sdes.last_request_start_time
       ,sdes.last_request_end_time
       ,sdes.is_user_process
       ,sdes.host_name
       ,sdes.program_name
       ,sdes.login_name
       ,sdes.status

       ,sdec.num_reads
       ,sdec.num_writes
       ,sdec.last_read
       ,sdec.last_write
       ,sdes.reads
       ,sdes.logical_reads
       ,sdes.writes

       ,sdest.DatabaseName 
       ,sdest.ObjName
    ,sdes.client_interface_name
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.Query
    ,KillCommand  = 'Kill '+ CAST(sdes.session_id  AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec 
        ON sdec.session_id = sdes.session_id

CROSS APPLY (

                SELECT DB_NAME(dbid) AS DatabaseName
                    ,OBJECT_NAME(objectid) AS ObjName
                    ,COALESCE((
                            SELECT TEXT AS [processing-instruction(definition)]
                            FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) 
                            FOR XML PATH('')
                                ,TYPE
                            ), '') AS Query

                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

    ) sdest
WHERE sdes.session_id <> @@SPID 
--ORDER BY sdes.last_request_start_time DESC

I then killed the 2 sessions, re-created the login, tried again, same error.

What am I missing?

  • 2
    So what's in sys.server_triggers? Is there a logon trigger that prevents logins or not? If there is, there's no particular reason to assume re-creating the login would change the conditions that the trigger uses to accept or reject the login -- that would require studying the contents of the trigger. – Jeroen Mostert Sep 28 at 12:22
  • 1
    Also, aside from the trigger explicitly rejecting logins with an error, one of the most common failure modes of login triggers is failing to account for the fact that it's executing as the login, e.g. trying to insert something in an audit table that the login has no insert permission on (the usual fix is EXECUTE AS). Another popular failure mode is failing to account for the transaction isolation level (which is whatever the application has set it to be). All in all, they're not for the faint of heart. – Jeroen Mostert Sep 28 at 12:46
  • Sorry about that. Instead of commenting I went straight ahead with answering the question. Didn't even realise you had comments flowing. – hot2use Sep 28 at 12:54
up vote 3 down vote accepted

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

You might have previously had a logon trigger that evaluated if you were allowed to log in to the SQL Server or not. This trigger may have been removed or might produce wrong results which now does not allow anybody to log in to the SQL Server instance.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd –S 127.0.0.1,1434 

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe –c -m –f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.