First thoughts said this was not really possible. Seems like Domain Admins (DA) could access the SQL Server regardless of what I did to lock them out. I did find one way that will satisfy an auditor.
- Create a Domain group and add the Domain Admins you wish to lockout.
- Add the Domain group as a user to your SQL Server
- On the General tab under Security Access click Deny access.
- Make sure your
Yeah I agree that seems too simple. One major flaw with this is that a DA could just remove himself/herself from the group you specified. Another flaw is that the DA could just access the server itself and stop services, delete databases, reign down terror etc.
I searched and found some sites that suggested removing the BUILTIN\Administrators group from SQL. NOTE: if you do this make sure you know what you are doing. If you do this in the wrong order you can remove all Sysadmin access from your instance of SQL. Make sure you have an account that has sysadmin privs before you drop the BUILTIN\Administrators group from your SQL.
During testing I did not see a need to remove the BUILTIN\Administrators group.
- Add Domain Admins to Domain Group
- Deny the Group access to SQL using steps listed above
- Have Domain Admin try connecting to Enterprise Manager/SQL Analyzer/ODBC from their workstation using Windows Auth. Make sure the login fails
- Have Domain Admin try connecting to the server and using Enterprise Manager/SQL Analyzer/ODBC from the server itself.
If you have a better plan then this, then by all means please share it. I am probably missing something here.