Disabling SSMS connection for few uses
-
GilaMonster (2/6/2012)
Nothing wrong with login triggers, however a few things to consider...You block connections from SSMS, now, what about Query Analyser? isql? osql? sqlcmd? Access? Excel? 3rd party tools like Toad? Users who figure out how to change the application name that SSMS passes to SQL (it's not hard)?
Also, in your script:
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'my_login_name'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'my_login_name' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'my_login_name') > 3
-- ROLLBACK; for the moment I will not use rollback but just to display a msg
All that means is no more than 3 connections. The user can quite easily open a single connection from SSMS.
Also note that you can't display a message from a login trigger. The session is not fully active at the point that login triggers fire, any print is redirected to the error log, the user won't see it.
Unless that was just sample code from somewhere.
Finally, if anyone has sysadmin rights and knows a bit about SQL, connections via the DAC bypass login triggers (so that an admin can't lock themselves out of the server with a buggy login trigger)
Hi
This code will be changed a bit using the APP_NAME() so what i will do is insert a record to a table every time if I find a uses connecing to sql box with SSMS so i can review how many users are using SSMS so it will not be a msg sorry i realized this later.
with regards to Query Analyser, isql, osql, sqlcmd, Access, Excel & 3rd party tools I am not too worried at the moment meaning if my trigger works fine then i will capture all this applications and disable them one my one with APP_NAME().
only few trustworthy logins have sysadmin not all
-
Gazareth (2/6/2012)
Ha, so basically, what Gail said... 😀awfully easy to change if you know what to look for:
:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
GilaMonster (2/6/2012)
Oh, one other added complexity - Maintenance plans have an app name of Microsoft SQL Server Management Studio..., so be careful you don't throw your maint plans out (I found that one out the hard way)thanks I will keep this in mind,
at the moment I have developed a custom made maintenance scripts I am not relying in the SQL servers maintenance plan
-
Lowell (2/6/2012)
Gazareth (2/6/2012)
Ha, so basically, what Gail said... 😀awfully easy to change if you know what to look for:
:
WoW... I never thought of this... so all this can be a wast if a uses know how to change this... 🙁
-
That's what I was saying....
Btw, I've been through this, had a couple devs who knew the app's password and thought they were above the rules. I put a login trigger in place, they changed apps, wrote their own little query tool, etc, etc.
Just to add, this is actually not a technical problem (devs using tools to connect to servers they shouldn't). It's a management problem. If what they are doing is against a clearly defined policy, then management deals with it. If it's not, then a policy needs defining or the devs can legitimately say they weren't aware that what they were doing was wrong, and they can even tell management that you (with your login trigger) are interfering with their ability to work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (2/6/2012)
That's what I was saying....Btw, I've been through this, had a couple devs who knew the app's password and thought they were above the rules. I put a login trigger in place, they changed apps, wrote their own little query tool, etc, etc.
So please tell me how did you manage to stop them?
-
Damian-167372 (2/6/2012)
GilaMonster (2/6/2012)
That's what I was saying....Btw, I've been through this, had a couple devs who knew the app's password and thought they were above the rules. I put a login trigger in place, they changed apps, wrote their own little query tool, etc, etc.
So please tell me how did you manage to stop them?
Took the problem up with management (they were in violation of a clearly written and well known IT security policy). Next time they were caught they were sent down to HR for a written warning and a promise that if they were caught again they would be fired.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (2/6/2012)
Damian-167372 (2/6/2012)
GilaMonster (2/6/2012)
That's what I was saying....Btw, I've been through this, had a couple devs who knew the app's password and thought they were above the rules. I put a login trigger in place, they changed apps, wrote their own little query tool, etc, etc.
So please tell me how did you manage to stop them?
Took the problem up with management (they were in violation of a clearly written and well known IT security policy). Next time they were caught they were sent down to HR for a written warning and a promise that if they were caught again they would be fired.
ok .. but in my case its not only Dev team but internal staffs also uses SSMS and this is been a big headache for me. good thing is they dont have sysadmin rights but still they can make the SQL server busy or block other users by running queries that will not time out
-
Is there an IT security policy (or other policy) in place that prohibits what they are doing? If not, the first thing to do would be to get management approval for creating such a policy.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
yes I am working with the management team to do this ...
thanks a lot for your information ..
I also hard coded the script with app name something like this...
IF APP_NAME() = 'Microsoft SQL Server Management Studio'
Rollback;
ELSE
insert a record into my monitoring table
in this way I can disable access to SSMS for the time being
but if they change the app name it will record it and i will take them to management.
-
Damian-167372 (2/6/2012)
yes I am working with the management team to do this ...thanks a lot for your information ..
I also hard coded the script with app name something like this...
IF APP_NAME() = 'Microsoft SQL Server Management Studio'
Rollback;
ELSE
insert a record into my monitoring table
in this way I can disable access to SSMS for the time being
but if they change the app name it will record it and i will take them to management.
That trigger wil lock YOU out too, as well as the items Gail mentioned previously;
i've done something like this:
--Prevent access from SSMS
--drop TRIGGER logon_trigger_not_from_SSMS on all server
CREATE TRIGGER logon_trigger_not_from_SSMS
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft SQL Server%'
BEGIN
IF suser_name() IN ('Stormdev\Lowell','sa') --the only persons allowed to use SSMS, no sa allowed for testing
BEGIN
--only allowed from my host machine
IF host_name() !='STORMDEV'
BEGIN
RAISERROR('SSMS connections are restricted on to specific dba machines.', 16, 1)
ROLLBACK
END --host name check
END --suser_name check
ELSE
BEGIN
RAISERROR('SSMS connections are not permitted with this logon.', 16, 1)
ROLLBACK
END
END --app name check
ELSE
BEGIN
RAISERROR('SSMS connections are restricted on this server.', 16, 1)
ROLLBACK
END
END --trigger
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (2/6/2012)
Damian-167372 (2/6/2012)
yes I am working with the management team to do this ...thanks a lot for your information ..
I also hard coded the script with app name something like this...
IF APP_NAME() = 'Microsoft SQL Server Management Studio'
Rollback;
ELSE
insert a record into my monitoring table
in this way I can disable access to SSMS for the time being
but if they change the app name it will record it and i will take them to management.
That trigger wil lock YOU out too, as well as the items Gail mentioned previously;
i've done something like this:
--Prevent access from SSMS
--drop TRIGGER logon_trigger_not_from_SSMS on all server
CREATE TRIGGER logon_trigger_not_from_SSMS
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft SQL Server%'
BEGIN
IF suser_name() IN ('Stormdev\Lowell','sa') --the only persons allowed to use SSMS, no sa allowed for testing
BEGIN
--only allowed from my host machine
IF host_name() !='STORMDEV'
BEGIN
RAISERROR('SSMS connections are restricted on to specific dba machines.', 16, 1)
ROLLBACK
END --host name check
END --suser_name check
ELSE
BEGIN
RAISERROR('SSMS connections are not permitted with this logon.', 16, 1)
ROLLBACK
END
END --app name check
ELSE
BEGIN
RAISERROR('SSMS connections are restricted on this server.', 16, 1)
ROLLBACK
END
END --trigger
yes this is also good.. i will copy some of your code..
Thank you for your previous point it helped me
-
Also make sure that you have support from management before kicking people out, otherwise those internal users will go to management and complain that you are preventing them from doing their jobs.
p.s. I prefer not to hardcode apps and login names into a trigger. Rather create tables with allowed or disallowed items and make sure that the permissions are adequate for the trigger to always be able to access them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you all for your kind suggestions and information 🙂
-
Well, good luck mate. I don't envy you the situation!
Hope it goes well. 🙂
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply