Disabling SSMS connection for few uses

Question

Disabling SSMS connection for few uses

Damian-1673

SSCommitted

Points: 1622
More actions
February 6, 2012 at 5:33 am

#391480

Hi
Some of our users have Installed SSMS on local PC's and accessing our production server to run some custom made reports. Our SQL Server is configured with mixed mode authentication.
1. Is this a good practice to disable SSMS for users other than DBA's?
2. How can I audit this connection made using SSMS?
3. can any one let me know how I can disable this connections and to enable only few logins(DBA's only)
any information on this will be great...
Thanks

Viewing 15 posts - 1 through 15 (of 29 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Hi Damian,

Only way I can think you might be able do this is via Logon Triggers: http://msdn.microsoft.com/en-us/library/bb326598.aspx

Using ORIGINAL_LOGIN() & APP_NAME() to check for user/SSMS

Any good to you?

Cheers

Gaz

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

It should not be a problem. Create (or retain) few logins which are required for routine operations and disable (or drop) remaining logins.

For the retained logins provide minimal permissions based on ‘Rule of Least Privileges’.

Logon Triggers are also available, but it should be done if you can’t manage user access issue with above options.

Damian-1673 SSCommitted Points: 1622 More actions · Answer 3

Gazareth (2/6/2012)
Hi Damian,
Only way I can think you might be able do this is via Logon Triggers: http://msdn.microsoft.com/en-us/library/bb326598.aspx
Using ORIGINAL_LOGIN() & APP_NAME() to check for user/SSMS
Any good to you?
Cheers
Gaz

Hi Gaz

This is close to what i wanted, I have to be very careful in runing this code in my server so I will do this on my test server first... so what i will do is

GRANT VIEW SERVER STATE TO my_login_name; -- my current login to the server

GO

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

END;

is this ok?

Damian-1673 SSCommitted Points: 1622 More actions · Answer 4

Dev (2/6/2012)
It should not be a problem. Create (or retain) few logins which are required for routine operations and disable (or drop) remaining logins.
For the retained logins provide minimal permissions based on ‘Rule of Least Privileges’.
Logon Triggers are also available, but it should be done if you can’t manage user access issue with above options.

Hi

we have hundreds of uses so its hard to disable and enable users i guess but I think Logon Trigger will be a good one

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

I am sorry but I am not able to understand your requirements. Why hundreds of users are sitting on the server if you don’t want them to connect?

Damian-1673 SSCommitted Points: 1622 More actions · Answer 6

Dev (2/6/2012)
I am sorry but I am not able to understand your requirements. Why hundreds of users are sitting on the server if you don’t want them to connect?

Hi

sorry if i was not clear ...

I don't want them to connect to our sql box with SSMS that's all i wanted to do,

but they still should be able to connect to our DBs using our normal application. so with the able Logon Triggers and APP_NAME() i can do this right?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

In this particular case Yes.

But I am giving you once scenario. Please answer to it for my better understanding. The application uses a login XYZ to connect to the database server. This login is saved somewhere in application configuration files and not known to Application Users (End Users). If you think DEV team knows the password and can misuse it, just change the password. It requires application configuration change and only few DEV or Support guys (trustworthy) would be involved there.

I don’t see any harm in Logon Trigger except performance hit (conditionally). But this is a route I usually avoid.

Damian-1673 SSCommitted Points: 1622 More actions · Answer 8

Dev (2/6/2012)
In this particular case Yes.
But I am giving you once scenario. Please answer to it for my better understanding. The application uses a login XYZ to connect to the database server. This login is saved somewhere in application configuration files and not known to Application Users (End Users). If you think DEV team knows the password and can misuse it, just change the password. It requires application configuration change and only few DEV or Support guys (trustworthy) would be involved there.
I don’t see any harm in Logon Trigger except performance hit (conditionally). But this is a route I usually avoid.

thanks for your reply..

we have few DBs some of them are connecting using application users which is a single login account that is configured in the configuration file, yes your right our dev team guys all knows this user name and passwords.

And there are some other db's that uses sql server logins and our poor application is designed that way so that's why we have 100rds of uses in our sql server.

so I dont know by user names who is using SSMS so i wanted globally disable this access to our server who's application is SSMS.

Thanks for hint on performance hit I will keep this in mind

i hope this is clear to you.

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

I'm with you there Dev - I wouldn't usually recommend logon triggers either but I guessed from what Damian had said that he's in the situation he's in!

Also if mixed mode then the users could have got hold of some sql accounts for which it could be non-trivial to change the password.

If the users have been able to install SSMS then I'm assuming they all have the rights to do so - in which case I imagine the whole security setup needs looking at, not just SQL rights.

Damian-1673 SSCommitted Points: 1622 More actions · Answer 10

Gazareth (2/6/2012)
I'm with you there Dev - I wouldn't usually recommend logon triggers either but I guessed from what Damian had said that he's in the situation he's in!
Also if mixed mode then the users could have got hold of some sql accounts for which it could be non-trivial to change the password.
If the users have been able to install SSMS then I'm assuming they all have the rights to do so - in which case I imagine the whole security setup needs looking at, not just SQL rights.

yes your right the whole setup needs to be reviewed but this is going to take some time as many PC's have SSMS. but for the time being i wanted to disable this and then give them some reporting tools like Crystal Reports.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 12

Hi Damian,

That's the core of it, yes, but that trigger will just prevent a login having more than 3 sessions.

You'll need to change the IF to check APP_NAME() is LIKE 'Microsoft SQL Server Management Studio%' (there may be variations on this string)

AND IS_SRVROLEMEMBER to check for only sa's (or use a list of allowed logins, but it's going to get a bit unwieldy!)

The ROLLBACK is what terminates the login attempt. Not sure if SSMS will actually recieve the message or not.

Even so, there are plenty of ways around this too. You might find yourself in a bit of an arms race!

Gazareth One Orange Chip Points: 27737 More actions · Answer 13

Gazareth

One Orange Chip

Points: 27737

February 6, 2012 at 9:15 am

#1443244

Ha, so basically, what Gail said... 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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)

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