Limit Concurrent Logins by database and/or user ID

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    kwoznica (11/23/2012)


    OPC,

    How could I expand on this so that if a login is not in the login table then the login is allowed. Right now if a login is not in that particular table then the login is denied.

    I am on my tablet so I cannot test, but from just reading the code it seems like it should work even if there is no row in the login-table. If there is no row then the selection of NUM_ALLOWED should eval to NULL and any test against NULL will be NULL therefore you should not hit the ROLLBACK. Have you confirmed the trigger works for any low-privilege (i.e. non-sysadmin) logins? I ask because I am wondering if you forgot to include EXECUTE AS 'logon_trigger_login'

    COMMIT seems like a natural choice to add as the counter-task to ROLLBACK but it doesn't really work that way. A COMMIT has to be paired with an explicit BEGIN TRANSACTION statement whereas ROLLBACK can be issued without being paired with a BEGIN TRANSACTION because when you are inside a trigger it is implied that you are within, at the very least, an implicit transaction. The ROLLBACK serves as a signal that the current operation should be rolled back, whether that be a login attempt, or in the case of a DML trigger, maybe an UPDATE, DELETE or INSERT.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • kwoznica

    SSCertifiable

    Points: 5275

    OPC,

    It does work with a non-sysadmin user if they are in the table. I also only tested this at the sql level so I still need to see what happens when I . I actually locked myself out of the database after putting the trigger in place because I did not include my login in the LoginTable and I am part of sysadmin. Fortunately I still had a live connection and the trigger didn't disconnect me so I was able to drop it.

    Otherwise if a login is not in the table they are not able to establish a session.

    I kept the original code from this post for the trigger. The only changes I made were to the table as per your performance suggestions.

    Let me know what you think. In the meantime I'll test a bit further. I appreciate the assistance.

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    It might not be a bad idea to bypass the logic for sysadmins using IS_SRVROLEMEMBER().

    E.g.

    IF NOT IS_SRVROLEMEMBER ( 'sysadmin', ORIGINAL_LOGIN())

    AND ... The check for num allowed in the table

    Then rollback.

    When you say you tested 'at the sql level' were you by chance using EXECUTE AS?

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • kwoznica

    SSCertifiable

    Points: 5275

    OPC,

    When I tested it at the SQL level I was logging into the sql server as the user in the limitedlogins table.

    At the application level I would receive the attached error message.

    I then added the principal logon_trigger_login to the sysadmin role and the application error went away. Also I was able to login as a user not in the limited login table, which is the ideal scenario.

    I still want to test a few scenarios but with your suggestions this looks to be working.

    Do you see any issue with having the logon_trigger_login principal a member of the sysadmin role? Would a lesser role accomplish the same thing?

    Thanks for your help.

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    Sorry for the delay in responding. I just returned from vacation.

    kwoznica (11/29/2012)


    OPC,

    When I tested it at the SQL level I was logging into the sql server as the user in the limitedlogins table.

    At the application level I would receive the attached error message.

    I then added the principal logon_trigger_login to the sysadmin role and the application error went away. Also I was able to login as a user not in the limited login table, which is the ideal scenario.

    Excellent. A working model that proves the concept.

    Do you see any issue with having the logon_trigger_login principal a member of the sysadmin role? Would a lesser role accomplish the same thing?

    I do not see any issues with it as it relates to the trigger itself but do not recommend having anyone in the sysadmin role that does not technically need it, and this login should not need to be in the sysadmin role so I would recommend to continue until you can once again remove the login from the role. Two other items to check:

    1. Did you add a user for the login in the database where the user-table resides, and then grant that database user select permissions on the user-table?

    From and earlier post:

    USE GK50LIVE;

    GO

    CREATE USER [logon_trigger_login] FROM LOGIN [logon_trigger_login] WITH DEFAULT_SCHEMA = [dbo];

    GO

    GRANT SELECT ON dbo.LimitedLogins TO logon_trigger_login;

    GO

    The login needs access to the user table which implies it must have a user in the database and have permission to select from it.

    2. Did you grant the login permissions to view the server-state? From an earlier post:

    USE master;

    GO

    GRANT VIEW SERVER STATE TO logon_trigger_login;

    GO

    Because we are referring to sys.dm_exec_sessions in the trigger code the executing the trigger must be granted the VIEW SERVER STATE permission.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • ltussing

    SSC Enthusiast

    Points: 144

    Since this thread has been so helpful to me I thought I would add in some extra features I created to this same Logon Trigger. The issue I saw was that it did not take into account users not in the table. The query that existed when run would ignore a user if they weren't listed so I created a default value and a check to see if they were in the table.

    USE master

    GO

    CREATE TRIGGER connection_limit_trigger ON ALL SERVER

    WITH EXECUTE AS 'logon_trigger_login'

    FOR LOGON

    AS

    BEGIN

    -- Check to see if the user is in the table & if not set a limit

    DECLARE @LogonLimit INT, @LogonLimitTest INT;

    SET @LogonLimit = 10

    SET @LogonLimitTest = (SELECT NUM_ALLOWED

    FROM GK50LIVE.dbo.LimitedLogins

    WHERE LOGIN_ID = ORIGINAL_LOGIN())

    IF @LogonLimitTest >= 0

    BEGIN

    SET @LogonLimit = @LogonLimitTest

    END

    -- the list of logins to restrict

    IF (@LogonLimit) <

    (

    SELECT COUNT(*)

    FROM sys.dm_exec_sessions

    WHERE is_user_process = 1

    AND original_login_name = ORIGINAL_LOGIN()

    )

    BEGIN

    ROLLBACK

    END

    END;

    GO

    Of note since others have more recently been posting this data to make it easier to find this all corresponds to the DISA STIGs too.

    Rule Title: SQL Server must limit the number of concurrent sessions for each system account to an organization-defined number of sessions.

    STIG ID: SQL2-00-000100 Rule ID: SV-53793r2_rule Vuln ID: V-41311

  • ltussing

    SSC Enthusiast

    Points: 144

    I also created a "development" version of this Logon Trigger too. I modified the LimitedLogins table to add a column to track the maximum # of logins an account is using. It only ever updates the table if the current # is greater than the last recorded number. Unless you had some very specific need for this on a production system I would recommend against it since it adds a couple more selects and an sometimes an update every time a user starts a session and requires UPDATE permission for the user.

    I did this to get an idea of logon session usage across all users. Since this table already existed from making the other trigger I thought it was a useful location to store & evaluate this. Mainly I wanted to make sure my numbers I was setting were not too limiting or too liberal.

    Firstly I updated the LimitedLogins table to add a default of the current date to ensure it's populated and then I added the Max_Logon column with a default of 0. If there's no # present it causes the logic to fail in the trigger and nothing gets recorded. You also have to grant the loggon_trigger_login UPDATE permission to the table.

    -- Create LIMITEDLOGINS Table

    CREATE TABLE dbo.LimitedLogins

    (

    Id INT IDENTITY(100,1) PRIMARY KEY

    ,Login_Name VARCHAR (50) NOT NULL

    ,LOGIN_ID VARCHAR (50) NOT NULL

    ,NUM_ALLOWED INT NOT NULL

    ,Date_Added Date NOT NULL CONSTRAINT [DF_LimitedLogins_Date_Added] DEFAULT (getdate())

    ,Max_Logon INT NOT NULL CONSTRAINT [DF_LimitedLogins_Max_Logon] DEFAULT ((0))

    );

    GO

    GRANT UPDATE ON dbo.LimitedLogins TO logon_trigger_login;

    GO

    Then I created a specific trigger to after checking for the limit record the maximum sessions they've used.

    USE master

    GO

    CREATE TRIGGER connection_limit_trigger_development ON ALL SERVER

    WITH EXECUTE AS 'logon_trigger_login'

    FOR LOGON

    AS

    BEGIN

    DECLARE @LogonLimit INT, @LogonLimitTest INT,

    @LoggedMaxLogons INT, @CurrentLogons INT;

    SET @LogonLimit = 10

    SET @LogonLimitTest = (SELECT NUM_ALLOWED

    FROM GK50LIVE.dbo.LimitedLogins

    WHERE LOGIN_ID = ORIGINAL_LOGIN())

    -- Get the current # of sessions for this login

    SET @CurrentLogons = (SELECT COUNT(*)

    FROM sys.dm_exec_sessions

    WHERE is_user_process = 1

    AND original_login_name = ORIGINAL_LOGIN())

    -- Check to see if the user is in the table & if not use the default limit

    IF @LogonLimitTest >= 0

    BEGIN

    SET @LogonLimit = @LogonLimitTest

    END

    -- the list of logins to restrict

    IF (@LogonLimit) < (@CurrentLogons)

    BEGIN

    ROLLBACK

    END

    -- Get the historic max sessions for this login

    SET @LoggedMaxLogons = (SELECT Max_Logon

    FROM GK50LIVE.dbo.LimitedLogins

    WHERE LOGIN_ID = ORIGINAL_LOGIN())

    -- Compare the sessions and if the new value is greater update the log

    IF (@CurrentLogons > @LoggedMaxLogons)

    BEGIN

    UPDATE GK50LIVE.dbo.LimitedLogins

    SET Max_Logon = @CurrentLogons

    WHERE LOGIN_ID = ORIGINAL_LOGIN()

    END

    END;

    GO

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4789

    Wow what a wonderful post
    I dont know if this is applicable to my case or not. My company CISO has asked me to do the following:

    1. The security administrator in this case its the DBA (Moi) should set a resource limit for the number of concurrent sessions to 1 where possible

    So the script you guys are using above i need to include for ALL users and i dont know if i need to create a trigger or user the Resource Governor which i dont have experience with.

    How would i go about performing the above requirement?
    have a great week everyone
    Hurricane

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Start by asking the 'security' person where they get these ideas from.

    Does every application in your environment use individual logins to the DB for each different person using the app? If not, this requirement will be entertaining...
    Also note that this makes SQL Server Management Studio nearly useless, as it opens different connections for object explorer, each window and any properties. Hope you're good with scripting, because intellisense is also ruled out now.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply