July 29, 2008 at 4:04 am
Hi All
i need to disable management studio from my live servers, please help me on this, i want to block some users who are using management studio from the servers, how can i do this, i cant uninstall since if i do then i will be uninstalling server configuration as well.
July 29, 2008 at 4:39 am
CrazyMan (7/29/2008)
Hi Alli need to disable management studio from my live servers, please help me on this, i want to block some users who are using management studio from the servers, how can i do this, i cant uninstall since if i do then i will be uninstalling server configuration as well.
SQL Server Management Studio is run by executing sqlwb.exe (it is usually installed into C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE ). You can change the permissions of this executable, you can rename it, delete it, etc. If someone really wants to execute it, he could find a way, but by removing execute permission could indicate them that you would not like them to execute it.
Regards,
Andras
July 29, 2008 at 4:57 am
Thanks Andras
will it affect any other services, if i rename the exe file 🙂
July 29, 2008 at 7:16 am
Why is management studio even installed on the servers? Workstation utilities should not be installed on servers.
Second, why do your users have enough privileges to log on to a production server?
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
July 29, 2008 at 7:32 am
Thats what exactly i was asking to management, i just took charge on the servers and i saw all this, since every one knows Sa and the administrator password they log into using them , i cant change sa password suddnely since they even use that on all application, spoke to the developers and about to create a new set of logings for applications, in mean time i need to keep users out, so i have to take this crude way, untill or unless there is and other way???
July 29, 2008 at 9:23 am
Oooh, you have a lot of work ahead of you. Some suggestions:
Uninstall the client tools from all of the servers. Servers don't need management studio and books online on them.
Change the administrator login on the servers. No app should be using that so it should be safe to do fairly quickly.
Make sure that all of the SQL Servers are running using a domain account, not local system. Change said service account's password.
Create logins for all the apps (without sa permissions), then change the sa password. This won't be easy.
Create individual, restricted, logins for everyone who needs access to the servers.
Good luck. You're going to need it. 😉
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
July 29, 2008 at 10:12 am
Thanks Gial, i got a small doubt, MS says that it is secure to allow users to login into SQL using Windows account, what do you recon? is it good to allocate username and password to all users or allow them to use windows authentication??
Which will be usefull for auditing, in case when i have some problems
Cheers
July 29, 2008 at 10:23 am
Windows authentication is best. It's more secure than SQL authent. That said, their windows accounts only need access to SQL, not to the server itself. They don't need any rights on the server that SQL is running on.
One of the better ways to do that is to create a domain group for all users that need the same access to SQL, then just grant that group access and permissions. It simplifies management. You'll still be able to see who logged in for each session. sys.dm_exec_sessions shows the windows user name, not the group name.
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
July 29, 2008 at 10:29 am
Thanks Gial :D:)
July 30, 2008 at 6:31 am
I'm definately with Gail on this one.
I have been through this pain that you are about to embark on - it may be a long and painful path - and you WILL make yourself unpopular, but hey - we aren't in this business to win friends 😀
You would probably best off creating an AD group, putting all users into that group who need access to your SQL server and granting that group specific read and execute permissions on the databases they need - exactly as Gail said. Do remember to remove the SQL and windows logins (all but this group) after you have done this though.
If I were you, I would use Gails suggestions as a work list and tick my way through them.
Very best of luck 😀
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
July 30, 2008 at 7:47 am
I disagree that servers don't need management tools on them (I do agree they don't need BOL). I put the management tools on my production servers sitting in colo facilities for 2 reasons:
1) If you're working on a machine that doesn't have the manangement tools installed and you need to troubleshoot a problem you can remote in to the server and you've got the tools available.
2) If you need to execute a query that will run for hours and you don't want to run the risk of dropping connectivity to the server.
Control who gets access to remote into the server and remove the BUILTIN\Administrators rights and you negate most of the risks associated with putting SSMS on your server. If you're really paranoid then create an AD group for the people who should have rights to execute SSMS and modify the NTFS permissions on the server so that only the group has access to run the tools.
July 30, 2008 at 7:53 am
Thanks Wilts, 🙂
July 30, 2008 at 7:54 am
Kendal,
Whilst I see your point, I would never allow management tools on any database server. If you need to manage them, then you use a workstation with those tools installed. If the tools arent installed, then the user doesn't even get the opportunity to try and get into the system.
As for long running queries, I would tend to put them into a proc and schedule them to run under the job engine at quiet times, or even just to kick them off immediately.
No need for those tools on the server.
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
July 30, 2008 at 8:11 am
I doubt that whomever is trying to get into the server is doing so to be able to run the management tools. Besides, most SQL Servers I know accept external connections...so if "they" have access to try and log into the server what's preventing them from just running the management tools on their own machine? If someone you don't want running management tools can log into your server you've got bigger problems to deal with.
Not all queries can be run as a "fire and forget" job. Often the kind of things that are complex enough to require you to run it on the server to begin with are the things that you probably want to watch very carefully anyways.
July 30, 2008 at 8:19 am
Kendal,
You are right in that if someone has access to your server, then there are bigger problems. But a server is a server - not a workstation.
I wasn't talking about "fire and forget" type of queries, but more along the lines of the long running queries that you were talking about. Yes, there are occassions when I might want to run such beasts interactively, but most of the time I put heavy logging into procs/scripts that are causing issues, so it makes little difference between interactive running and batch. There is the possible issue of running it under a specific profile of course...
But my principal has always been a server is a server, and a workstation is a workstation. Besides, if you have a long running script that needs to be debugged, wouldn't you do it in your dev environment? Surely you wouldn't test in the live production environment?
And regardless of what we, as DBAs think, when we are security audited in a Financial environment, the auditors can (and from my experience do) complain about additional possible entry points into the data.
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply