none
sql server 2012 Logon trigger not working for certain logins

    Question

  • Hello. I created a login trigger to insert data for each login in a table, and it works for all logins except one that is format domain\login and the login ends with the dollar sign(actual name is domain\CTXDEVDCSI1$).
    I had been using varchar, but after reading other forum posts, I changed the varchar's to nvarchar's, but it still fails for that id.
    The errors written to the sql server error log were the usual "login failed due to trigger execution".

    I had granted insert on the rvvlogindata table in dsa to public, and only one id wasn't able to login after that.
    Any suggestions would be much appreciated!

    Here's the modified table ddl:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[rvvlogindata](
    [sessionId] [int] NULL,
    [LoginTime] [datetime] NULL,
    [HostName] [nvarchar](50) NULL,
    [ProgramName] [nvarchar](300) NULL,
    [LoginName] [nvarchar](50) NULL,
    [ClientHost] [nvarchar](50) NULL
    ) ON [PRIMARY]
    GO

    Here's the logon trigger code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    create trigger [LOGIN_IP_RESTRICTION] on all server for logon
    as
    Begin
    Declare @LogonTriggerData xml,
    @EventTime datetime,
    @LoginName nvarchar(50),
    @ClientHost nvarchar(50),
    @HostName nvarchar(50),
    @AppName nvarchar(300)
    Set @LogonTriggerData = eventdata()
    set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
    set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
    set @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
    set @HostName = HOST_NAME()
    set @AppName = APP_NAME()
    insert into dsa.dbo.rvvlogindata
    (
    sessionId,
    LoginTime,
    HostName,
    ProgramName,
    LoginName,
    ClientHost
    )
    select @@spid,
    @EventTime,
    convert(nvarchar(50),@HostName),
    convert(nvarchar(300),@AppName),
    convert(nvarchar(50),@LoginName),
    convert(nvarchar(50),@ClientHost)
    END
    GO
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ENABLE TRIGGER [LOGIN_IP_RESTRICTION] ON ALL SERVER
    GO

    Thursday, April 10, 2014 7:42 PM

Answers

  • So I finally got the time to test this, this worked for me:

    CREATE LOGIN loginauditer WITH PASSWORD = 'dflögjdfgjdf-.,gnmdf.,gnd.f,gdnf.g'
    CREATE DATABASE loginaudit
    ALTER AUTHORIZATION ON DATABASE::loginaudit TO loginauditer

    That is, I created a special login to be owner of the database.

    Then I created the trigger as:

    CREATE TRIGGER [LOGIN_IP_RESTRICTION] ON ALL SERVER
    WITH EXECUTE AS 'loginauditer' FOR LOGON AS 

    Earlier, I suggested EXECUTE AS OWNER, but I found that this is not permitted for logon triggers.

    Because I forgot to change the database name in your trigger, I also found that the SQL Server errorlog has information about the real error:

    2014-04-18 13:48:08.67 spid53            Error: 208, Severity: 16, State: 1.
    2014-04-18 13:48:08.67 spid53            Invalid object name 'dsa.dbo.rvvlogindata'.
    2014-04-18 13:48:08.67 Logon             Error: 17892, Severity: 20, State: 1.
    2014-04-18 13:48:08.67 Logon             Logon failed for login 'ture' due to trigger execution. [CLIENT: <local machine>]

    Once I had corrected the error, I was able to login with a freshly created SQL login that I had not granted anything.

    Your problem was with machine accounts. Unfortunately, this is something which is difficult to test for me, as I don't have a domain at home and thus no AD.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kartar Rana Monday, April 21, 2014 4:57 PM
    • Marked as answer by Richard Van Veen Monday, April 28, 2014 11:12 AM
    Friday, April 18, 2014 12:06 PM

All replies

  • So do these logins have permission to access the database dsa? I seem to recall that something like "WITH EXECUTE AS OWNER" works in this particular case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 10, 2014 10:17 PM
  • You might have to enable cross-database ownership chaining on dsa, bad idea.

    Start with

    GRANT INSERT ON rvvlogindata TO PUBLIC

    All logins irrespective of how they are setup need to be able to write to that table.

    Saturday, April 12, 2014 4:27 AM
  • I did a test setup with logon trigger that logged to a separate database EXECUTE AS some time back and it worked. Without enabling cross-db chaining.

    Granting INSERT to public works, if you also enable the guest user, but that is certainly questionable. (And you would permit users who are logged in to add junk data to the table.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 12, 2014 12:31 PM
  • I tried including WITH EXECUTE AS OWNER, and also WITH EXECUTE AS another id, and neither worked for the logon trigger.

    Monday, April 14, 2014 1:08 PM
  • I don't have the time to set up a test right now, but in the meanwhile, test with having the log table in the master database and grant INSERT to public on the table. I don't see this as a solution, but it is a troubleshooting aid to see whether this is a permissions issue.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 14, 2014 10:07 PM
  • Thanks very much for the suggestion, Erland.

    I created the table in master, and granted insert on it to public, and the problem login didn't get the login trigger error.

    Of course, I don't think it's a good idea to have a growing table in master, so I disabled the trigger for now.

    I see the guest id is in master and dsa, so any suggestions on what needs to be done to get rid of the error in dsa?

    Thursday, April 17, 2014 11:19 AM
  • So I finally got the time to test this, this worked for me:

    CREATE LOGIN loginauditer WITH PASSWORD = 'dflögjdfgjdf-.,gnmdf.,gnd.f,gdnf.g'
    CREATE DATABASE loginaudit
    ALTER AUTHORIZATION ON DATABASE::loginaudit TO loginauditer

    That is, I created a special login to be owner of the database.

    Then I created the trigger as:

    CREATE TRIGGER [LOGIN_IP_RESTRICTION] ON ALL SERVER
    WITH EXECUTE AS 'loginauditer' FOR LOGON AS 

    Earlier, I suggested EXECUTE AS OWNER, but I found that this is not permitted for logon triggers.

    Because I forgot to change the database name in your trigger, I also found that the SQL Server errorlog has information about the real error:

    2014-04-18 13:48:08.67 spid53            Error: 208, Severity: 16, State: 1.
    2014-04-18 13:48:08.67 spid53            Invalid object name 'dsa.dbo.rvvlogindata'.
    2014-04-18 13:48:08.67 Logon             Error: 17892, Severity: 20, State: 1.
    2014-04-18 13:48:08.67 Logon             Logon failed for login 'ture' due to trigger execution. [CLIENT: <local machine>]

    Once I had corrected the error, I was able to login with a freshly created SQL login that I had not granted anything.

    Your problem was with machine accounts. Unfortunately, this is something which is difficult to test for me, as I don't have a domain at home and thus no AD.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kartar Rana Monday, April 21, 2014 4:57 PM
    • Marked as answer by Richard Van Veen Monday, April 28, 2014 11:12 AM
    Friday, April 18, 2014 12:06 PM
  • Thanks for the suggestion, Erland.

    I created the login, database and trigger on my sql server 2012 dev server, and many different entries for different logins were written to the table, but when I tried to start sql server management studio from the sql server's machine, it was hanging as one id, while it worked as another id.

    I saw no blocking or errors in the errorlog, and did see some entries in the table for the id that never completely connected on the remote box, so I need to investigate this further.

    Monday, April 21, 2014 1:19 PM
  • Erland, I wanted to add more info to my reply earlier today.

    sp_who2 showed no blocking, activity monitor from my local PC SSMS showed no major waits or high i/o or cpu activity.

    I was wondering if you have any suggestions on how to find out what was the cause of the 5+ minute wait for SSMS on the remote desktop to respond and fully come up.

    I definitely can't put this in production with this hanging delay possibly occurring there.

    I was connecting as read_user when it hung.

    sp_who2 output at the time was:

    1     BACKGROUND                     sa   .   . NULL LOG WRITER       54631 0 04/10 06:59:43                                                1     0   
    2     BACKGROUND                     sa   .   . NULL RECOVERY WRITER  8673 0 04/10 06:59:43                                                2     0   
    3     BACKGROUND                     sa   .   . NULL LAZY WRITER      300691 0 04/10 06:59:43                                                3     0   
    4     BACKGROUND                     sa   .   . NULL RESOURCE MONITOR 1207010 0 04/10 06:59:43                                                4     0   
    5     BACKGROUND                     sa   .   . NULL XE TIMER         38828 0 04/10 06:59:43                                                5     0   
    6     BACKGROUND                     sa   .   . NULL XE DISPATCHER    1404 0 04/10 06:59:43                                                6     0   
    7     BACKGROUND                     sa   .   . master SIGNAL HANDLER   0 0 04/10 06:59:43                                                7     0   
    8     BACKGROUND                     sa   .   . NULL LOCK MONITOR     179978 0 04/10 06:59:43                                                8     0   
    9     sleeping                       sa   .   . master TASK MANAGER     0 11 04/21 08:37:04                                                9     0   
    10    sleeping                       sa   .   . master TASK MANAGER     0 0 04/10 06:59:44                                                10    0   
    11    BACKGROUND                     sa   .   . master TRACE QUEUE TASK 546 0 04/10 06:59:44                                                11    0   
    12    BACKGROUND                     sa   .   . NULL SYSTEM_HEALTH_MO 4930 0 04/10 06:59:44                                                12    0   
    13    BACKGROUND                     sa   .   . NULL RECEIVE          422 0 04/10 06:59:45                                                13    0   
    14    BACKGROUND                     sa   .   . master CHECKPOINT       79137 31811 04/10 06:59:46                                                14    0   
    15    BACKGROUND                     sa   .   . master TASK MANAGER     1606 0 04/10 06:59:46                                                15    0   
    16    BACKGROUND                     sa   .   . NULL UNKNOWN TOKEN    0 0 04/10 06:59:46                                                16    0   
    17    sleeping                       sa   .   . master TASK MANAGER     0 74 04/21 08:37:04                                                17    0   
    18    sleeping                       sa   .   . master TASK MANAGER     0 0 04/21 08:18:49                                                18    0   
    19    sleeping                       sa   .   . master TASK MANAGER     0 0 04/21 08:30:29                                                19    0   
    20    sleeping                       sa   .   . master TASK MANAGER     0 1 04/21 08:37:14                                                20    0   
    21    sleeping                       sa   .   . master TASK MANAGER     0 7 04/21 08:30:59                                                21    0   
    22    sleeping                       sa   .   . master TASK MANAGER     16 4 04/21 08:37:44                                                22    0   
    23    sleeping                       sa   .   . master TASK MANAGER     0 15 04/21 08:39:24                                                23    0   
    25    BACKGROUND                     sa   .   . master BRKR EVENT HNDLR 0 95 04/10 06:59:48                                                25    0   
    30    BACKGROUND                     sa   .   . master BRKR TASK        0 0 04/10 06:59:48                                                30    0   
    31    BACKGROUND                     sa   .   . master BRKR TASK        16926 0 04/10 06:59:48                                                31    0   
    32    BACKGROUND                     sa   .   . master BRKR TASK        0 0 04/10 06:59:48                                                32    0   
    34    BACKGROUND                     sa   .   . master BRKR TASK        10701 0 04/10 06:59:48                                                34    0   
    51    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 03:16:56                                                51    0   
    52    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           52    0   
    53    sleeping                       NT AUTHORITY\SYSTEM SQLDEV2012      . msdb AWAITING COMMAND 0 0 04/10 06:59:58 SQLAgent - Email Logger                        53    0   
    54    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:51 Citrix:Broker#1                                54    0   
    55    sleeping                       edit_user ASDEV1          . dsa AWAITING COMMAND 0 0 04/21 08:55:27 jTDS                                           55    0   
    56    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           56    0   
    57    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           57    0   
    58    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           58    0   
    59    sleeping                       NT AUTHORITY\SYSTEM SQLDEV2012      . msdb AWAITING COMMAND 124 242 04/10 06:59:59 SQLAgent - Generic Refresher                   59    0   
    60    sleeping                       NT AUTHORITY\SYSTEM SQLDEV2012      . msdb AWAITING COMMAND 2790 1160 04/21 08:55:00 SQLAgent - Job invocation engine               60    0   
    61    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           61    0   
    62    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           62    0   
    63    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           63    0   
    64    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           64    0   
    65    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           65    0   
    66    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 03:16:56                                                66    0   
    67    sleeping                       reports_adm REPORTSDEVSI2   . JBOSS_Cluster_CRServer2011 AWAITING COMMAND 0 0 04/21 08:52:12 jTDS                                           67    0   
    68    sleeping                       edit_user DCMA10685       . dsa AWAITING COMMAND 0 0 04/21 08:56:47 jTDS                                           68    0   
    69    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:49 Citrix:Configuration                           69    0   
    70    sleeping                       rvanveen DCMA8460        . master AWAITING COMMAND 1794 7120 04/21 08:51:37 Microsoft SQL Server Management Studio         70    0   
    71    sleeping                       xsp_user XSPDEVSI2       . xspv5 AWAITING COMMAND 0 0 04/21 08:58:52 .Net SqlClient Data Provider                   71    0   
    72    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:56:01 Citrix:Monitor                                 72    0   
    73    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:30 Citrix:Monitor                                 73    0   
    74    sleeping                       rvanveen DCMA8460        . master AWAITING COMMAND 16 2 04/21 08:53:37 Microsoft SQL Server Management Studio - Query 74    0   
    75    sleeping                       rvanveen DCMA8460        . loginaudit AWAITING COMMAND 0 22 04/21 08:50:29 Microsoft SQL Server Management Studio - Query 75    0   
    76    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:05:07                                                76    0   
    77    sleeping                       adm_jbossportal ASDEV1          . jbossportal AWAITING COMMAND 0 0 04/21 08:40:27 jTDS                                           77    0   
    78    sleeping                       rvanveen DCMA8460        . master AWAITING COMMAND 110 542 04/21 08:46:17 Microsoft SQL Server Management Studio - Query 78    0   
    79    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:05:56                                                79    0   
    80    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:54 Citrix:MachineCreation                         80    0   
    81    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 08:08:29                                                81    0   
    82    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:02:13                                                82    0   
    83    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:02:15                                                83    0   
    84    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:44 Citrix:Monitor                                 84    0   
    85    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:11:05                                                85    0   
    86    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:53 Citrix:AdIdentity                              86    0   
    87    sleeping                       DAIWA_USA\admsql SQLDEV2012      . master AWAITING COMMAND 15 2 04/21 08:56:20 Microsoft SQL Server Management Studio - Query 87    0   
    88    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:36 Citrix:SiteServices                            88    0   
    89    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:42 Citrix:Host                                    89    0   
    90    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:47 Citrix:ConfigurationLogging                    90    0   
    91    RUNNABLE                       rvanveen DCMA8460        . master SELECT INTO      15 51 04/21 08:58:46 Microsoft SQL Server Management Studio - Query 91    0   
    92    sleeping                       rvanveen DCMA8460        . master AWAITING COMMAND 63 30 04/21 08:52:34 Microsoft SQL Server Management Studio - Query 92    0   
    94    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:53 Citrix:DelegatedAdmin                          94    0   
    95    sleeping                       DAIWA_USA\admsql SQLDEV2012      . loginaudit AWAITING COMMAND 173 27 04/21 08:56:10 Microsoft SQL Server Management Studio         95    0   
    96    sleeping                       xsp_user XSPDEVSI2       . xspv5 AWAITING COMMAND 0 0 04/21 08:58:33 .Net SqlClient Data Provider                   96    0   
    97    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 08:10:07                                                97    0   
    98    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 31 38 04/21 08:00:31                                                98    0   
    99    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 08:10:12                                                99    0   
    100   sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 282 04/21 08:10:14                                                100   0   
    101   sleeping                       DAIWA_USA\admsql SQLDEV2012      . master AWAITING COMMAND 0 8 04/21 08:56:50 Microsoft SQL Server Management Studio         101   0   
    102   sleeping                       DAIWA_USA\admsql SQLDEV2012      . master AWAITING COMMAND 31 0 04/21 08:54:57 Microsoft SQL Server Management Studio         102   0   
    103   sleeping                       read_user SQLDEV2012      . master AWAITING COMMAND 0 8 04/21 08:57:09 Microsoft SQL Server Management Studio         103   0   
    104   sleeping                       read_user SQLDEV2012      . dsa AWAITING COMMAND 0 0 04/21 08:57:09 Microsoft SQL Server Management Studio         104   0   
    105   sleeping                       rvanveen DCMA8460        . tempdb AWAITING COMMAND 8875 336 04/21 08:58:54 Microsoft SQL Server Management Studio         105   0   
    106   sleeping                       read_user SQLDEV2012      . master AWAITING COMMAND 16 0 04/21 08:57:39 Microsoft SQL Server Management Studio         106   0   
    107   sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:47 Citrix:EnvTest                                 107   0   
    108   sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 2200 8514 04/21 08:00:31                                                108   0   
    109   sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:05:56                                                109   0   
    110   sleeping                       rvanveen DCMA8460        . master AWAITING COMMAND 0 0 04/21 08:58:48 Microsoft SQL Server Management Studio         110   0   
    113   sleeping                       Citrix_adm CTXDEVSI1       . XenApp6 AWAITING COMMAND 284 777 04/21 08:51:33 Citrix IMA                                     113   0   
    119   sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:42 Citrix:ConfigurationLoggingData                119   0   
    120   sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/21 08:58:26 Citrix:Storefront                              120   0   
    125   sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/16 13:55:16                                                125   0   
    126   sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 16 0 04/16 11:25:30                                                126   0   
    131   sleeping                       echouliak DCMA10685       . master AWAITING COMMAND 139 8 04/14 15:26:55 Microsoft SQL Server Management Studio         131   0   
    135   sleeping                       echouliak DCMA10685       . dsa AWAITING COMMAND 0 0 04/14 15:24:52 Microsoft SQL Server Management Studio - Query 135   0   
    136   sleeping                       echouliak DCMA10685       . dsa AWAITING COMMAND 0 0 04/14 15:00:17 Microsoft SQL Server Management Studio - Query 136   0   
    140   sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:05:56                                                140   0   
    145   sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/21 07:05:56                                                145   0   

    Monday, April 21, 2014 2:19 PM
  • I'm not fully understanding your description.

    It seems that when you started SSMS locally and tried to connect with a certainly login (Windows login, I presume?), it took five minutes to complete? Does it happen repeatedly for that specific account? Or did it only happen once? It is obviously not a permissions issue, but if it is not repeatable, one possibility is autogrow.

    If it happens repeatedly, does it only happen when you connect locally, but not when you connect remotely?

    And more importantly, what happens if you disable the trigger?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 21, 2014 4:36 PM
  • I left my SSMS on my pc logged in as my SA id so there'd be no risk of the login trigger stopping everyone from connecting, so it would be easy to disable or delete the trigger, if needed.

    So I used remote desktop to connect to the server box, and succeeded connecting to the server using my id in SSMS(with sql server authentication).

    Then I disconnected and tried to reconnect using a read id, with sql server audthentication, and it hung, even after I disabled the trigger.

    I just figured out why it hung: the loginauditer id had default DB of master, and when I changed it to loginaudit, no processes hung any more.

    I'm going to keep the trigger running on the dev server, and, if it's not getting any login failures in the log by tomorrow, I'll mark your reply as the answer tomorrow.

    Thanks very much for your help, Erland. It's much appreciated.

    Monday, April 21, 2014 4:56 PM
  • Interesting. I was not able to repeat the scenario, but I'm at home with a workgroup and not a real domain.

    I will try to keep in mind that it's a good idea to change the defaultl database for the owner of the audit database.

    If you have the time, change the default database back to master, and try again. This time run this query:

    SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
    FROM sys.dm_os_waiting_tasks
    ORDER BY session_id, wait_type

    Maybe this reveals something interesting.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, April 21, 2014 8:49 PM
  • I couldn't reproduce the issue when I changed it back to master.

    I did one other thing yesterday, that may be more likely to have been the cause of the issue.

    I had made the trigger write to dsa again, since we didn't want to have a whole different DB in production.

    So I made the login a member of db_datareader in that DB.

    I just confirmed this was the issue, since I took away db_datareader in the dsa DB, and it hung.

    Here's the output from the select:

    NULL 260475488 CLR_AUTO_EVENT NULL
    NULL 260475486 CLR_AUTO_EVENT NULL
    NULL 6773 FT_IFTS_SCHEDULER_IDLE_WAIT NULL
    1 26 LOGMGR_QUEUE NULL
    2 80 DIRTY_PAGE_POLL NULL
    3 857 LAZYWRITER_SLEEP NULL
    5 2785 XE_TIMER_EVENT NULL
    6 73517 XE_DISPATCHER_WAIT NULL
    7 1042490620 KSOURCE_WAKEUP NULL
    8 4150 REQUEST_FOR_DEADLOCK_SEARCH NULL
    11 3369 SQLTRACE_INCREMENTAL_FLUSH_SLEEP NULL
    12 15143 SP_SERVER_DIAGNOSTICS_SLEEP NULL
    14 147646 CHECKPOINT_QUEUE NULL
    15 4984513 ONDEMAND_TASK_QUEUE NULL
    16 1042517168 FT_IFTSHC_MUTEX NULL
    25 66487506 BROKER_EVENTHANDLER NULL
    30 1042515695 BROKER_TRANSMITTER NULL
    31 394 HADR_FILESTREAM_IOMGR_IOCOMPLETION NULL
    32 1042515693 BROKER_TRANSMITTER NULL
    34 26 SLEEP_TASK NULL
    80 21 CXPACKET 80
    80 1709 CXPACKET 80
    80 21 CXPACKET 80
    80 24 PAGEIOLATCH_SH NULL
    80 1 SLEEP_TASK NULL
    80 1 SLEEP_TASK NULL
    108 19 PAGEIOLATCH_SH NULL

    And the abridged output from sp_who2(due to too many bytes for forum post):

    79    sleeping                       edit_user INFADEVFS2      . dsa AWAITING COMMAND 0 0 04/22 07:05:18                                                                    79    0   
    80    SUSPENDED                      xsp_user XSPDEVSI2       . xspv5 SELECT           15 1290171 04/22 08:34:26 .Net SqlClient Data Provider                                       80    0   
    80    SUSPENDED                       XSPDEVSI2       . xspv5 SELECT           8876 12917 04/22 08:34:26 .Net SqlClient Data Provider                                       80    0   
    80    SUSPENDED                       XSPDEVSI2       . xspv5 SELECT           21809 48512 04/22 08:34:26 .Net SqlClient Data Provider                                       80    0   
    80    SUSPENDED                       XSPDEVSI2       . xspv5 SELECT           63 0 04/22 08:34:26 .Net SqlClient Data Provider                                       80    0   
    80    SUSPENDED                       XSPDEVSI2       . xspv5 SELECT           78 0 04/22 08:34:26 .Net SqlClient Data Provider                                       80    0   
    80    SUSPENDED                       XSPDEVSI2       . xspv5 SELECT           624 0 04/22 08:34:26 .Net SqlClient Data Provider                                       80    0   
    81    sleeping                       AMERICAS\CTXDEVDCSI1$ CTXDEVDCSI1     . CitrixXDFarm1 AWAITING COMMAND 0 0 04/22 08:34:01 Citrix:Monitor                                                     81    0   
    82    sleeping                       edit_user ASDEV1          . dsa AWAITING COMMAND 16 0 04/22 08:33:14 jTDS                                                               82    0    
                                                        171   0   

    Tuesday, April 22, 2014 12:41 PM
  • I suppose you mean db_data*writer*. Obviously, if the login is not owner of the database, it must be a user in the database and have permssion to write to the table. (So I would recommend that you take out the audit login from db_datawriter and only grant INSERT on the audit table.)

    And that output from sys.dm_os_waiting_tasks was taken while the login attempt was hanging? Hm, there is nothing that matches.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 22, 2014 9:48 PM
  • I had granted insert, update, select and delete to the id when I created the id in the DB, and it hung anyway, so I killed the SSMS and put it in db_datareader, and reconnected, and it went in ok.

    The hang was a little further along in the SSMS connection attempt, but it never returned to me in 3 minutes.

    Wednesday, April 23, 2014 11:05 AM
  • Strange. INSERT permission should be sufficent. Which it is in my setup. Very funny. Anyway, since you have it working, we can lay it to rest.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 23, 2014 10:12 PM
  • I was testing something completely different today when your login trigger trapped me for a reason I had entirely forgotten. I tried to connect with the very old ISQL that uses DB-Library, but the trigger failed. In the errorlog I found this error:

    /SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations./

    The logon trigger does indeed use XML data type methods. I don't know if it is a concern to you, but if you have legacy clients, or clients that connects through badly configured ODBC DSNs (I've seen that happen), you should add this line first in the trigger:

    SET CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING ON


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, April 25, 2014 8:55 PM