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?
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
If you don't trust someone, don't give them Windows Administrator rights, period.
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
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.
No, it's not possible to completely prevent local administrators from gaining
If the instance is restarted in single-user mode, SQL Server is hard-coded to allow local administrators
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