June 16, 2015 at 8:34 am
I am STIGing a database and have to create a trigger that will stop any
connection from occurring after a certain limit has been reached. For
example I set the concurrent connection to 50 so I need a trigger that will
stop any connection from all logins happening after 50.
The STIG info:
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
The below triggers works, but for only the one login, so how can I have it
include all logins for that SQL instance.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
Any help is appreciated.
Jeff
June 16, 2015 at 9:37 am
jayoub (6/16/2015)
I am STIGing a database and have to create a trigger that will stop anyconnection from occurring after a certain limit has been reached. For
example I set the concurrent connection to 50 so I need a trigger that will
stop any connection from all logins happening after 50.
The STIG info:
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
The below triggers works, but for only the one login, so how can I have it
include all logins for that SQL instance.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
Any help is appreciated.
How about just removing the part that says:
ORIGINAL_LOGIN() = 'login_test' AND
and leaving the rest ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 17, 2015 at 5:45 am
I will give it a try. Thank you very much for the reply
Jeff
February 15, 2016 at 1:47 pm
Hi Jeff,
I am working thorough this STIG and hoped you could share how you can up with the concurrent connection limit 50?
Is there a way to identify the number of concurrent connections needed?
Thanks, Michael
February 16, 2016 at 10:58 am
It looks like I found an answer for my question. Here is one way for others that may be looking.
Activity Monitor is a tool in SQL Server Management Studio that gives you a view of current connections on SQL Server. To open Activity Monitor in SQL Server Management Studio, right-click the SQL Server instance name in Object Explorer and then select Activity Monitor. Click on Processes and view the logins to see who is logged in and the number of connections.
-Michael
February 16, 2016 at 11:52 am
Sorry I was not able to reply earlier. I have a different way that you can try. Let me know if this works for you.
Step One:
To determine the required number of concurrent sessions. We do this by setting
up an SQL Agent Job that will show the number of concurrent sessions. We set
the job up to execute every hour and set the output in the history of the
job. This way we can look at the output over time and determine the number.
A: Create a SQL Agent Job called "Concurrent Sessions" with the following
script
SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame AS LoginName,
nt_domain AS NT_Domain,
nt_username AS NT_UserName,
hostname AS HostName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid,
hostname,
loginame,
nt_domain,
nt_username
ORDER BY NumberOfConnections DESC;
in the advance settings of the job and use "Include Step output as part of
history" to have the output as part of the history
Create a schedule to run every 1 hour.
B: After a week or so look at the output in the history and determine what
you should set the concurrent sessions.
Step two: Check the current settings Value in the instance and modify
Right click on the instance - Properties - connection and update the
settings with the value from step one
Step Three: Create the Trigger.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN IF
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1) > <value>
ROLLBACK;
END;
Jeff
February 16, 2016 at 12:09 pm
Jeff, No worries, thought maybe you were busy.
I like your approach and will get it a try.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply