Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
6th Immutable Law of Security: "A computer is only as secure as the administrator is trustworthy." Laws 2 and 10 are also relevant here. technet.microsoft.com/library/cc722487.aspx –  Dan Neely Feb 13 '13 at 22:23

3 Answers 3

up vote 12 down vote accepted

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.

share|improve this answer
1  
+1 anything claiming to deny builtin\administrators is snake oil –  Remus Rusanu Feb 13 '13 at 17:50
    
+1 Prudent advice. –  Thomas Stringer Feb 13 '13 at 18:03

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.

share|improve this answer

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.

share|improve this answer
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. –  Aaron Bertrand Feb 13 '13 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 '13 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 '13 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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