11

As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows administrator rights?

1

4 Answers 4

15

No.

If a user is a Windows Administrator of a box, assume that they own everything on the box (including SQL Server). With Windows Administrator rights it is trivial to bypass any targeted protection you apply (such as a logon trigger that identifies their user name), by impersonating someone else (including NT AUTHORITY\SYSTEM, which gets de facto admin rights on all local SQL Server instances). Auditing won't help much either, because they can easily turn that off, but you should have it just in case.

If you don't trust someone, don't give them Windows Administrator rights, period.

1
  • 2
    +1 anything claiming to deny builtin\administrators is snake oil Feb 13, 2013 at 17:50
1

No, it's not possible to completely prevent local administrators from gaining sysadmin access to a SQL Server instance.

If the instance is restarted in single-user mode, SQL Server is hard-coded to allow local administrators sysadmin privileges, even though there may not be an explicit login. The reason this exists is for recovery purposes because it's possible to lock yourself out of an instance.

That said, restricting access while the instance is running in multi-user mode (with no service interruptions) is not as difficult. As Aaron mentioned, local administrators can impersonate NT AUTHORITY\SYSTEM, which by default has a sysadmin-level login created in SQL Server 2008. This can be exploited to recover sysadmin access while the server is running. (Note: in SQL Server 2012, this login is no longer a sysadmin.) I don't know exactly what this login is used for (upgrades/hot fixes/etc., presumably), but I think it's safe to disable it except during those events. If there are no other impersonatable logins, that should be sufficient to deny access. Again, only if the instance is running, uninterrupted.

0

By default in SQL 2008 and 2012, there is no default access for Windows administrators to a SQL Server. For a Windows administrator (i.e., someone who is either a Domain Administrator or a Local Administrator) to have access, their login needs to be explicitly granted access or the group they belong to granted access along with rights within SQL Server itself. You are required, when setting up the instance, to specify one Active Directory login or group as an administrator, but that login/group can be anyone in your domain.

In SQL 2005, there was a BUILTIN\Administrators group with sysadmin access rights. This group would allow local administrators sysadmin access to SQL Server. This group can be removed from SQL Server and it was considered best practices to do so.

That being said, there's no way to prevent Windows (local or domain) from affecting the server that SQL Server lives on. This means the administrators can still affect services and OS level configurations, change directory security, and other OS level tasks. This is, after all, why they are Windows administrators.

In general, you should trust your administrators, both SQL Server and Windows. While the Windows administrators can not perform tasks or access data (by default) within the SQL Server itself, they still control the environment in which your SQL Server lives. You need to take care with who you assign to those roles.

3
  • 3
    Do not take too much comfort in this - while the changes Microsoft made make it a little more secure, it is nowhere near bulletproof yet. It is trivial for a local administrator impersonate and do their bidding directly to any local SQL Server instance - so you need to be worried about more than just indirect influence at the OS level. Feb 13, 2013 at 17:26
  • I've got to ask - what circumstances justify granting a non-trusted user administrator access to a server?
    – Mark Allen
    Feb 14, 2013 at 5:26
  • Personally, I don't think any. As many other people have stated in other answers (and not so clearly here), you should trust your administrators. And if you don't trust them, don't make them administrators.
    – Mike Fal
    Feb 14, 2013 at 16:06
0

Enable Sql authentication and then disable user login. Security => logins right click on sa click on properties then click on status then restart the sql server services. before you move on to the next step make sure you can login using sa. then go and disable the user the same way you enable the sa account

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.