locked
Securing Windows for SQL Management RRS feed

  • Question

  • Our scenario is that we have a department full of database administrators who require limited access to our db servers ranging from sql 2000 to 2008 on windows 2000, 2003 and 2008 (about 60 or so). We need to allow these dba’s access to restart the sql services without the ability to have administator rights on the servers. We have assigned them sysadmin rights for enterprise manager so they have full access to sql related tasks and currently they have administrator rights to the servers just to accommodate mmc access to restart services (or through enterprise manager / management studio) but we have restricted rdc access.

    This still poses a large security risk since anyone who has knowledge of unc to hidden shares can still manipulate the file system as well as using psexec or a number of other remote admin tools. We have created various shares on the servers to allow them access to and to manipluate only the sql related information, such as databases, logs, a backup directory, a replication directory, etc.

    The issue I discovered is this. If the dba’s do not have admin rights on the servers then they do not have access to use the services mmc snap-in to access remote servers. I have used group policy to assign them access only to the services they need and they can stop and restart sql services without admin rights but they must use sc or netsvc commands in order to do so. They don’t like this and want mmc access or at the least the ability to restart through enterprise manager (since we have prevented them rdc access, I think that’s fair).

    So what I need to understand is how to allow them access to an mmc snap-in (services specifically) or to shutdown/restart from within enteprise manager without  making them a member of the local administrators groups. From everything I have read this dosent seem possible, but I cannot believe Microsoft would not have a solution to this. You can not tell me that Microsoft expects a database administrator to have full control over a server in order to manage SQL. Im sure I just havent found the right solution.

    Hoping for some advice and suggestions here. Thanks guys

    Sunday, May 10, 2009 12:15 PM

All replies

  • Hopefully someone has some information to share on this topic...
    Monday, May 11, 2009 11:54 AM
  • Hi Scott

    It looks like this is more properly a Windows Admin / Security question than a SQL Server question, so you might be better off on a different forum.

    I have LA rights to our database servers (although with a separate sysadmin account to my normal workstation login) so I don't have this issue.

    I didn't realise that EM required rights over and above standard rights that you have delegated though. It seems like what you want to do should be achievable.

    Regards

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link under the star. If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Monday, May 11, 2009 1:34 PM
  • Ceate a Widows Group and include your adinistrators i the widows Group and use it in sql logins and apply appropriate permissions.
    For Services.msc permissions, configure the local security policy.
    Check this out for more info:

    http://www.theeldergeek.com/microsoft_management_console.htm

    and http://www.liutilities.com/products/registrybooster/tweaklibrary/tweaks/10728/



    Maninder
    • Edited by Maninder Monday, May 11, 2009 1:45 PM
    Monday, May 11, 2009 1:41 PM
  • By default the sysadmin fixed server role in SQL Server has the BUILTIN\Administrators group as a member, which is what gives you the default behavior your describing.  You can add/remove anyone from this sysadmin role.    You'll also notice that sa, which is a SQL Login is also a member of this role if your server are setup for mixed mode

    Create a domain group for all your DBA's & add that group in the sysadmin fixed server role.  In SSMS connect to the server, click on Security->Logins.  for your login name type in the domain group e.g. DOMAIN.LOCAL\DBA Group
    Click on server roles and add the sysadmin fixed server role.

    You can also check who's an existing member of this group by looking in Security->Server Roles
    Monday, May 11, 2009 1:51 PM
  • Thanks Nick,

    The DBA's AD group is already a member of the sysadmin's sql group. They have full access to sql related functions but this does not allow them to ability to restart the SQL services from either an mmc snap-in or from the Studio Management console without being members of the local administrators group. Windows 2008 with SQL2008 specifically in this case. We run our SQL services as standard domain based user accounts with only domain user permissions. I have also tried running the services as an administrator account though with the same results.
    Monday, May 11, 2009 4:38 PM
  • Thanks Maninder. I have already used the services under computer management object to apply gpo's. The problem as outlined in my above post though however is that these users only have access to manipulate the mmc to remote systems as admin's unless they use sc.exe or netsvc.exe. I could author and distribute an mmc but this still leaves the systems accessible by unc or any other app able to connect to $ipc and $admin or wmi, com, etc since the users have admin rights. This is what Im trying to get away from. Thanks for your suggestions though.
    Monday, May 11, 2009 4:41 PM
  • I've miss-understood your question, my apologies.
    Have you looked at the local group policy settings on server? specifically the Windows Settings->Security->User Rights Assignment policies?

    What I've seen usually done in big environments is that the db servers will have remote desktop application mode running which means you can RDP in & not have to be an admin. This gives you enough permission to poke around SQL Server, but not control the whole server.

    Tuesday, May 12, 2009 2:37 PM
  • No problem and thanks Nick. I did have a look at those policies and do not see anything that will help that is different from the domain based policies i have already configured where can I allow and restrict access to specific services. As mentioned however there is the limitation on how these services can be manipulated.

    I think what I am going to do is create an admin account for the dba's like we have for our operations department. the .dba account will allow them admin rights on the servers but restrict rdc access. Their regular account will grant them remote desktop access but will restrict their rights on the servers. This is the only viable option I see at this point.

    I cannot believe there is not a way to create an account, either domain or local - that grants the right just to manipulate a set group of services and nothing else.

    Tuesday, May 12, 2009 3:45 PM