none
For once and for all: How far can you keep a Windows administrator from SQL Server?

    Question

  • It is very hard to hide anything from administrators, but the separation between DBAs and Windows administrators has improved in SQL Server 2008 and Windows 2008. The Windows groups that are created for use by the SQL Server service are no longer provisioned with the sysadmin server role. Instead, service SIDs are provisioned as a member of the sysadmin fixed server role for SQL Server and SQL Server Agent. Also, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role.

    Does that mean we can lay our heads on the pillow and have sweet dreams? Absolutely not!

    Even when we provide individual service accounts, SQL Server will still provision the Local System account as a member of the sysadmin fixed server role. Any Windows administrator will be able to easily impersonate this user using a tool such as PSExec, locally or remotely. Try it. It's both fun and terrifying.

    Microsoft says you should not delete the Local System account or remove it from the sysadmin fixed server role. The account is used by Microsoft Update and by Microsoft SMS to apply service packs and hotfixes. The account is also used by SQL Server VSS Writer service to allow backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

    Auditors will point the Local System account login as a security risk.

    So what to do?

    In my experience, we can safely delete the Local System account login from SQL Server, as long as we don't have any services that depend on it.

    Back to the pillow and sweet dreams? Not yet.

    The "-m" startup option allows any administrator to log in to SQL Server as sysadmin, regardless of whether that administrator has been explicitly given that permission. This is by design, for recovery purposes, and we will need it e.g. when someone manages to lock SA out of SQL Server. The good thing is, no one would be able to get away with it, since the SQL Server service must be restarted and we will surely notice it and send Jack Bauer after him.

    As we can see, it is impossible to fully deny Windows administrators control over SQL Server. This is both a good and a bad thing, but the truth is that we should protect access to the operating system just like we protect access to SQL Server. Unfortunately, not all companies understand that fact.

    What is your experience regarding the Local System account login? Do you remove it, disable it, deny CONNECT to it?

    Just removing the sysadmin fixed server role from it still allows it to log in to SQL Server and see everything the public role sees. (yes I know, the public role is another security issue).

    Do we really need this login for Microsoft Update, Microsoft SMS and VSS? Is the risk of keeping it as sysadmin worth it?

    So what do you do to completely "neutralize the threat"?


    SQL Server Database Administrator

    Wednesday, February 22, 2012 7:25 PM

Answers

  • Hi,

    I abide by MS recommendations. There are ways to add groups and users into the SQL instance without even having to restart the instance with the "-m" option. Basically do what you can but if you have Windows admins you can't trust then you have bigger problems IMHO.

    I am a bit reluctant to start disabling accounts when MS states to leave it alone as it may work now but you never know what a SP or CU might require into the future and your change might break the system after that update is applied.

    Follow best practices of POLP (Principal of least privilage) then if you find someone circumventing your security config question them on it to find out why.

    I always think of the mindset of home security since when did a lock on a door keep someone intent on getting in out. "Locks are there to keep honest people honest".


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, February 22, 2012 9:36 PM

All replies

  • Hi,

    I abide by MS recommendations. There are ways to add groups and users into the SQL instance without even having to restart the instance with the "-m" option. Basically do what you can but if you have Windows admins you can't trust then you have bigger problems IMHO.

    I am a bit reluctant to start disabling accounts when MS states to leave it alone as it may work now but you never know what a SP or CU might require into the future and your change might break the system after that update is applied.

    Follow best practices of POLP (Principal of least privilage) then if you find someone circumventing your security config question them on it to find out why.

    I always think of the mindset of home security since when did a lock on a door keep someone intent on getting in out. "Locks are there to keep honest people honest".


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, February 22, 2012 9:36 PM
  • Not much to do in that regard. One way to protect the sysadmin from accessing some data is to encrypt it with a key stored in an EKM device (or simply protected by a password), though this is not really addressing your concern ...
    Friday, February 24, 2012 11:07 AM
  • Thanks Sean and Rudi for your answers.

    SQL Server Database Administrator

    Tuesday, March 06, 2012 1:19 PM