none
Logon Trigger to control Sessions

    Question

  • Good afternoon!

    Please forgive my English mistakes.

    I'm trying to control number of connections or session form every user on a instance server in SQL Server Express 2008R2 and 2012 but it doesn't work as I want.

    First i used the link http://msdn.microsoft.com/es-es/library/bb326598%28v=sql.110%29.aspx

    I wrote the next code:

    USE MASTER
    GO
    CREATE TRIGGER test
    ON ALL SERVER
    WITH ENCRYPTION
    FOR LOGON
    AS
    BEGIN
    IF
      ORIGINAL_LOGIN()= SYSTEM_USER
      AND
      (
        SELECT
          COUNT(*)
        FROM
          [SYS].[DM_EXEC_SESSIONS]
        WHERE
          [IS_USER_PROCESS] = 1
          AND [ORIGINAL_LOGIN_NAME] = SYSTEM_USER
      ) > 1
      BEGIN
        ROLLBACK;
      END;
    END;

    But I locked all connections EVEN MINE! ooops.

    I restored the connectios Successfuly and I read more about when i ran the select in Management it returned more than one connection and I suspect that one is for object explorer and the other is for the sql script.

    If I run the next script with the user sa and loggin with the Management Studio I obtain as a result 2 connections:

    SELECT
      COUNT(*)
    FROM
      [SYS].[DM_EXEC_SESSIONS]
    WHERE
      [IS_USER_PROCESS] = 1
      AND [ORIGINAL_LOGIN_NAME] = SYSTEM_USER

    If I run Management Studio whitout connection and just use the Database Engine Query I obtain a different result show just one connection.

    If I use sa user and open 5 query windows and I can see the correct result.

    As I see I have 5 connections one for each Sql Query window that's right! BUT THE TRIGGER DOESN'T DO ITS WORK! I HAVE MORE CONNECTION THAN SPECIFIED IN THE TRIGGER!

    I read that the number of connections may be different because some tasks in management studio and the services running.

    But if I use a test user with less permissions than sa I have different results because  Sql Server shows me only one connection but I have Object Explorer and Query window opened the same as the user sa and if I open more Query Windows sql returns me always just 1 connection.

    I openned 9 Query windows but SQL server returns me just 1 connection. I'M GETTING CRAZY AND I DON'T UNDERSTAND WHY AND WHY THE TRIGGER DOESN'T DO ITS WORK!

    I tried this varying code with the test user:

    SELECT
      COUNT(*)
    FROM
      [SYS].[DM_EXEC_SESSIONS] AS [DS] WITH(NOLOCK) INNER JOIN
        [SYS].[DM_EXEC_CONNECTIONS] AS [DC] WITH(NOLOCK) ON
          [DS].[SESSION_ID] = [DC].[SESSION_ID]
    WHERE
      [DS].[IS_USER_PROCESS] = 1
      AND [DS].[ORIGINAL_LOGIN_NAME] = SYSTEM_USER

    But I have the result that the user hasn't permission to do this acction.

    I modified the trigger with the next code:

    USE MASTER;
    GO
    CREATE TRIGGER test
    ON ALL SERVER
    WITH ENCRYPTION, EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
    IF
      ORIGINAL_LOGIN() = SYSTEM_USER
      AND
      (
        SELECT
          COUNT([DS].[SESSION_ID])
        FROM
          [SYS].[DM_EXEC_SESSIONS] AS [DS] WITH(NOLOCK) INNER JOIN
            [SYS].[DM_EXEC_CONNECTIONS] AS [DC] WITH(NOLOCK) ON
              [DS].[SESSION_ID] = [DC].[SESSION_ID]
        WHERE
          [DS].[IS_USER_PROCESS] = 1
          AND [DS].[ORIGINAL_LOGIN_NAME] = SYSTEM_USER
          AND [DS].[ORIGINAL_LOGIN_NAME] <> 'sa'
      ) > 1
      BEGIN
        ROLLBACK;
      END;
    END;

    But it doesn't work because I can open various Sql Query windows and Management (One normal and one as Manager and one directly on the server as normal user and as Manager) 4 connections with the user test.

    I tried to upload immages but the page says: "Body text cannot contain images or links until we are able to verify your account." can you tell me what must I do too.

    Please tell me what am I doing wrong.

    Thanks a lot for your help.

    Friday, July 04, 2014 5:36 PM

Answers

  • Hi I tested the first query block and then I was able to restrict the user.

    The Query that you use basically queries Sys.dm_exec_sessions. If you create login trigger for say login test, then login test needs to have view server state permissions.

    If you have a login which doesnt have this permission it will not be able to trigger the logon trigger as anytime the below query will not be greater than 1.

    SELECT
          COUNT(*)
        FROM
          [SYS].[DM_EXEC_SESSIONS]
        WHERE
          [IS_USER_PROCESS] = 1
          AND [ORIGINAL_LOGIN_NAME] = SYSTEM_USER
    

    Try to give one particular login view server state permissions and then try to login with that login.

    I successfully tested .

    Created a login test with no permissions

    Created logon trigger with your query

    Was able to logon any number of times with login test.

    Gave login test view server state permissions

    Then I was not able to login again.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by Jamesit0 Tuesday, July 08, 2014 6:30 PM
    • Unmarked as answer by Jamesit0 Tuesday, July 08, 2014 7:36 PM
    • Marked as answer by Jamesit0 Wednesday, July 09, 2014 12:56 PM
    Tuesday, July 08, 2014 1:50 PM
  • Sorry , I dont know much about CLR. It would be good if you open a new thread may be in the correct group for CLR?

    ".NET Framework inside SQL Server"


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    • Edited by Ashwin Menon Wednesday, July 09, 2014 8:30 AM
    • Marked as answer by Jamesit0 Wednesday, July 09, 2014 12:55 PM
    Wednesday, July 09, 2014 8:29 AM

All replies

  • To verify your account, go to

    http://social.technet.microsoft.com/Forums/en-US/home?forum=reportabug

    At the top of that forum will be a sticky thread with the current thread for requests to verify your account. Go to that thread and reply to it asking them to verify your account.

    Tom 


    Friday, July 04, 2014 6:09 PM
  • Thanks...

    I did.

    I'll wait for my account to be verified.

    Friday, July 04, 2014 6:41 PM
  • Hello,

    In order to query the following DMVs to get server level information: sys.dm_exec_connections, sys.dm_exec_sessions, you should have VIEW SERVER STATE permission on server.If you have VIEW SERVER STATE permission on the server, you will see all executing sessions on the instance of SQL Server; otherwise, you will see only the current session.

    Reference:http://msdn.microsoft.com/en-IN/library/ms176013.aspx

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support


    Monday, July 07, 2014 9:54 AM
  • Good morning!

    I'm not agree because I'm talking about new querys windows and I'm doing test on my laptop and no more peaple or aplications have been started. For example look at these two images:

    In this image I have five query windows oppened and Sql server counts five with the user sa.

    But in this images I have oppened nine query windows and Sql server just count 1 with the user test.

    how can it be possible?

    Thanks.


    Jamesit0

    Monday, July 07, 2014 12:37 PM
  • Hi James,

    instead of Count(*) can you do login_name, session_id, Program_name, is_user_process and see the output?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, July 07, 2014 1:01 PM
  • Good afternoo!

    Thanks a lot for the reply.

    Here are the outputs:

    I'm curious because I openned six Query Windows but one doesn't say "Query" ("Consulta" in the PROGRAM_NAME field.

    Thanks a lot for your valuable help.


    Jamesit0

    Monday, July 07, 2014 5:57 PM
  • James,

    The user test doesnt have enough permissions on the box to execute sys.dm_exec_sessions and that is the reason why you wont see any threads other than the one session that you have

    Check this link

    http://msdn.microsoft.com/en-us/library/ms176013.aspx

    Requires VIEW SERVER STATE permission on server.

    NoteNote

    If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

    If you want the user "test" to see all other connections just run the below command to give additional permissions

    Grant view server state to test

    Once this is given try the same query and you would see all the connections. You can revoke the permissions by running the below query

    Revoke view server state to test

    HTH

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, July 08, 2014 8:44 AM
  • Good morning!

    Thanks a lot for your reply that worked for me, but returning to my initial question: Why my server trigger doesn't work or doesn't control my sessions to limit connections or sessions to the user test just to one or two.

    I have tried with this two triggers (repalcing one for the other) but no one works:

    USE MASTER
    GO
    CREATE TRIGGER test
    ON ALL SERVER
    WITH ENCRYPTION
    FOR LOGON
    AS
    BEGIN
    IF
      ORIGINAL_LOGIN()= SYSTEM_USER
      AND
      (
        SELECT
          COUNT(*)
        FROM
          [SYS].[DM_EXEC_SESSIONS]
        WHERE
          [IS_USER_PROCESS] = 1
          AND [ORIGINAL_LOGIN_NAME] = SYSTEM_USER
      ) > 1
      BEGIN
        ROLLBACK;
      END;
    END;

    This is the other:

    USE MASTER;
    GO
    CREATE TRIGGER test
    ON ALL SERVER
    WITH ENCRYPTION, EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
    IF
      ORIGINAL_LOGIN() = SYSTEM_USER
      AND
      (
        SELECT
          COUNT([DS].[SESSION_ID])
        FROM
          [SYS].[DM_EXEC_SESSIONS] AS [DS] WITH(NOLOCK) INNER JOIN
            [SYS].[DM_EXEC_CONNECTIONS] AS [DC] WITH(NOLOCK) ON
              [DS].[SESSION_ID] = [DC].[SESSION_ID]
        WHERE
          [DS].[IS_USER_PROCESS] = 1
          AND [DS].[ORIGINAL_LOGIN_NAME] = SYSTEM_USER
          AND [DS].[ORIGINAL_LOGIN_NAME] <> 'sa'
      ) > 1
      BEGIN
        ROLLBACK;
      END;
    END;

    I extracted the first script from: Logon Triggers

    I need to control sessions or connections but no one works and the user test continue oppening more and more sql query windows even Opening Sql server Management Visual Studio windows (one normal and one as manager and one directly on the server where the trigger is runing) and I did the same in my laptop and the same hapens. What am I doing wrong?


    Jamesit0

    Tuesday, July 08, 2014 12:57 PM
  • Hi I tested the first query block and then I was able to restrict the user.

    The Query that you use basically queries Sys.dm_exec_sessions. If you create login trigger for say login test, then login test needs to have view server state permissions.

    If you have a login which doesnt have this permission it will not be able to trigger the logon trigger as anytime the below query will not be greater than 1.

    SELECT
          COUNT(*)
        FROM
          [SYS].[DM_EXEC_SESSIONS]
        WHERE
          [IS_USER_PROCESS] = 1
          AND [ORIGINAL_LOGIN_NAME] = SYSTEM_USER
    

    Try to give one particular login view server state permissions and then try to login with that login.

    I successfully tested .

    Created a login test with no permissions

    Created logon trigger with your query

    Was able to logon any number of times with login test.

    Gave login test view server state permissions

    Then I was not able to login again.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by Jamesit0 Tuesday, July 08, 2014 6:30 PM
    • Unmarked as answer by Jamesit0 Tuesday, July 08, 2014 7:36 PM
    • Marked as answer by Jamesit0 Wednesday, July 09, 2014 12:56 PM
    Tuesday, July 08, 2014 1:50 PM
  • Good afternoon!

    Thanks a lot for your reply.

    It worked for me. I'm just curious because the trigger didn't work and I just drop it and recreate it, maybe it was created with the second script and I can't reviewed it because of the option "with encryption".

    When I drop it and recreated it with the first script it worked well.

    I suppose that the difference was between DM_EXEC_SESSIONS and DM_EXEC_CONNECTIONS

    Again, thanks a lot for your valuable help.


    Jamesit0

    • Marked as answer by Jamesit0 Tuesday, July 08, 2014 6:29 PM
    • Unmarked as answer by Jamesit0 Tuesday, July 08, 2014 7:29 PM
    Tuesday, July 08, 2014 6:29 PM
  • Good afternoon!

    Please I'm sorry because I'm too dummy!

    Now I'm trying to implement the trigger by CLR but when I implement it and then try to connect it doesn't admit any connection even sa.

    The CLR is:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    
    public partial class Triggers
    {
        [Microsoft.SqlServer.Server.SqlTrigger(Name = "test", Target = "ALL SERVER", Event = "LOGON")]
        public static void test()
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand comandoSql = new SqlCommand();
                comandoSql.CommandText =
                    "IF" + "\r\n" +
                    "  (ORIGINAL_LOGIN() = SYSTEM_USER" + "\r\n" +
                    "  AND" + "\r\n" +
                    "  (" + "\r\n" +
                    "    SELECT" + "\r\n" +
                    "      COUNT(*)" + "\r\n" +
                    "    FROM" + "\r\n" +
                    "      [SYS].[DM_EXEC_SESSIONS]" + "\r\n" +
                    "    WHERE" + "\r\n" +
                    "      [IS_USER_PROCESS] = 1" + "\r\n" +
                    "      AND [ORIGINAL_LOGIN_NAME] = SYSTEM_USER" + "\r\n" +
                    "      AND [ORIGINAL_LOGIN_NAME] <> 'sa'" + "\r\n" +
                    "  ) > 5)" + "\r\n" +
                    "  BEGIN" + "\r\n" +
                    "    ROLLBACK;" + "\r\n" +
                    "  END;";
                comandoSql.Connection = conn;
                conn.Open();
                comandoSql.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    And I implement it with this code:

    --EXEC sp_configure 'clr enabled';
    --EXEC sp_configure 'clr enabled' , '1';
    --RECONFIGURE;
    
    --DROP TRIGGER [test] ON ALL SERVER
    
    --DROP ASSEMBLY [test]
    
    CREATE ASSEMBLY test
    FROM 'X:\S\D\test.dll'
    WITH PERMISSION_SET = SAFE;
    
    CREATE TRIGGER [test] ON ALL SERVER
    FOR LOGON
    AS EXTERNAL NAME [test].[Triggers].[test]
    GO

    What am I doing wrong?

    Thanks a lot for your valuable help.


    Jamesit0

    Tuesday, July 08, 2014 7:36 PM
  • Sorry , I dont know much about CLR. It would be good if you open a new thread may be in the correct group for CLR?

    ".NET Framework inside SQL Server"


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    • Edited by Ashwin Menon Wednesday, July 09, 2014 8:30 AM
    • Marked as answer by Jamesit0 Wednesday, July 09, 2014 12:55 PM
    Wednesday, July 09, 2014 8:29 AM
  • Good Morning!

    O. k.

    Thanks a lot for your valuable help.


    Jamesit0

    Wednesday, July 09, 2014 12:55 PM