November 23, 2012 at 8:18 pm
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
November 26, 2012 at 9:53 am
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.
November 27, 2012 at 4:11 am
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
November 29, 2012 at 1:40 pm
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.
December 3, 2012 at 3:15 pm
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
February 26, 2015 at 10:46 am
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
February 26, 2015 at 11:42 am
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
April 2, 2018 at 11:14 pm
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
April 3, 2018 at 2:39 am
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply