3

When installing SQL Server, you get to specify SQL Server administrators, i.e., the list of users initially in the sysadmin role. By default, this list contains the current user.

Database Engine Configuration

(Image quoted from https://msdn.microsoft.com/en-us/library/dd578652.aspx.)

This default value strikes me as odd. Wouldn't BUILTIN\Administrators be a more sensible choice than the current user? After all, isn't the whole point of Windows authentication not having to manage two separate user/group directories? And local administrators can gain sysadmin rights anyway, if they want to, so it's not really a security feature either.

On the other hand, SQL Server was written by people much smarter than I am, so there might actually be a very good reason for this default value. What is it?

Heinzi
  • 3,145
  • 2
  • 29
  • 42
  • It's not the worst default value in SQL Server... – James Anderson Aug 24 '15 at 13:47
  • Definitely not the worst default, by far. Also, I think it is safer to give admin to a single user than all the potential admins on that machine. In any case, are you often installing SQL Server on a machine that really is shared by a bunch of users, all of whom are admins? – Aaron Bertrand Aug 24 '15 at 13:49
  • 1
    If BUILTIN\Administrators is disabled and still Windows admin try to login they have to add there login into SQL Server then only they can access, leaving a good trail to catch – Shanky Aug 24 '15 at 13:52

3 Answers3

4

You're right: system administrators can gain sysadmin access, but you will find entries in the default trace for that action. Moreover, it requires restarting the service at least twice, which would hardly go unnoticed.

Not granting sysadmin privileges to BUILTIN\Administrators is a change introduced in SQL Server 2008. In my opinion it's a good idea: it allows the separation of duties between server administrators and SQL Server administrators.

The main undesired effect of having BUILTIN\Administrators in your sysadmin role is the loss of control, which can manifest with one or more of the following annoying things:

  1. New databases appearing on your servers
  2. Unwanted connections from windows admins that end up competing with legitimate connections
  3. Implied permissions to windows admins on databases with sensitive data
  4. Seemingly innocuous but disrupting "self service" operations, such as taking full "one-off" backups on databases with differential backup strategies.

Long story short, windows admins have a different skill set and a different mindset, not to mention different responsibilities: letting them manage your SQL Server instances is not a good idea.

My suggestion is to create a windows group for your DBAs and add that group to the sysadmin role upon each new SQL Server installation.

spaghettidba
  • 11,266
  • 30
  • 42
2

Using the local administrators group has been the default for a long time. But mainly in large enterprise environments you have an operations team for windows and a seperate operations team for SQL Server. Allowing everone to easily(!) gain access can be considered as security problem. Allowing only the current user can be explained with "secure by default".

As you said the local administrators can gain sysadmin rights. But in most cases they will leave traces of doing that.

Jens W.
  • 1,219
  • 1
  • 8
  • 16
1

Just to add on, the removal of BUILTIN\Administrators and NT AUTHORITY\SYSTEM from being default sysadmin was for the change in security with Microsoft products; going to the secure by default methodology. I am sure some portion of compliance had influence in Microsoft and SQL Server team choosing to do this as well.

Which actually, the main one that allowed a backdoor into your instance was use of the SYSTEM account as the service account. This backdoor was possible with no restart of the SQL Server service, and would go unnoticed unless monitoring was involved on the server. Which in SQL Server 2005 it was not supported if you to remove this account from the sysadmin role.

Fast forward now to Window Server 2012, an additional step for security that also removed the backdoor was with virtual accounts by default for each service, or configuring Managed Service Accounts. It is difficult, if not impossible, to use psexec to "impersonate" a virtual or MSA account that I know of now.

Community
  • 1
  • 1
    There is a way to impersonate a service SID. Take a look at this: http://sqlblog.com/blogs/argenis_fernandez/archive/2012/01/12/leveraging-service-sids-to-logon-to-sql-server-2012-instances-with-sysadmin-privileges.aspx – spaghettidba Aug 24 '15 at 15:40
  • I would say that is necessarily impersonating a service SID in that example, he is altering what is called by the service. So you are simply what the service is calling. That is a cool way of doing it though. –  Aug 24 '15 at 16:20