How to secure a SQL database from domain admins?

Question

I've got a database that I'd like to keep on the main domain, but prevent domain admins from writing to it. Read access is not a concern. To do this, it looks like I'd have to ensure any accounts with write permissions use only SQL logins (since domain admin could reset password of any other domain account). I do realize this has other security implications. Another concern is a scenario where an admin on the windows server takes the database mdf file and attaches it to another instance and modifies the database contents, then replaces the mdf on the original server. I'm first wondering if this is even possible, or will it need to be reattached through sql server?

Since the server is still on the domain, domain admins will obviously be admins on the server. Are there other ways for them to obtain write access to the database that I should be worried about (such as deleting the master database mdf)?

Radbourne

The short answer is you can't. Domain Admins have special privs across the domain. You can revoke access in SQL Server for the local admins, but they can still gain access if they really wanted to (technet.microsoft.com/en-us/library/dd207004.aspx). However, for them to gain access after access has been removed, it does involved quite a bit of effort and does require a reboot. You could probably setup a server level trigger to email you when SQL Server logins are changed and then you would know if someone was trying to gain access using this method. – Roux

Answer 1

Wow. Big question....can I assume that you are the dba? Before proceeding, a quick disclaimer: be extremely careful about changing login permissions. Especiallly logins that are part of the sysadmin fixed server role. It could be possible to remove all logins from that role. If your server uses Windows Authentication only (or no one knows the sa password), you could lock yourself out of the sql instance. That is bad. Very bad...

Server-level logins Domain admins and sql server sysadmins are not equivalent. If you're in SSMS, delve down into Security|Logins and expand. There may be a "BUILTIN\Administrators" login account. Double-click it to check the properties of that login. Select the "Server Roles". If the "sysadmin" server role is checked, then all domain admins have "sysadmin" privileges on your sql instance. If you uncheck it, you take that privilege away. For a domain admin (or local machine administrator) to have sysadmin privileges, they have to be explicitly added to the sysadmin server role. In earlier versions of SQL (2000 and maybe 2005), BUILTIN\Administrators was part of sysadmin by default. MS changed this in later versions because it was a security problem. If the domain admins each have their own login (ie YourDomain\JSmith), then you'd have to remove those individual logins from the sysadmin role. This will show you all the logins that are part of sysadmin: SELECT * FROM sys.syslogins WHERE sysadmin = 1

Database users Next you need to identify which local database user is associated with the sql server login. Sysadmin logins connect as the database owner (db_owner) automatically, so you may not see any database users (other than the default ones). From SSMS, delve down into YourDbName|Security|Users and expand. Double-click a user to open the Properties dialog. The "General" page will show the login for that user. "Membership" will show the database-level roles the user belongs to. If there's a user that maps back to the network admin's sql server login, great. However, hypothetically, if you removed the BUILTIN\Administrators sql login from the sysadmin fixed server role, that login would no longer be able to connect to the database as dbo. You'd have to create a new database user and associate it with the login. Something like CREATE USER [DomainAdminUser] FOR LOGIN [BUILTIN\Administrators] Then you could start layering on permissions for the user. You only want to allow read access, right? Add the db user to fixed db role db_datareader: EXEC sp_addrolemember N'db_datawriter', 'DomainAdminUser'. If they need more permissions, add as necessary.

Something else you mentioned was the possibility of a domain admin detaching the database, attaching it to another SQL Server instance where they have sysadmin permissions, changing data within the db, then re-attaching it back to the original SQL Server instance. I'm not sure there's much you could do to safeguard against this. As a domain admin, he/she would have the ability to shut down the SQL Server NT service and grab the mdf/ldf files and do with them as they please. This seems a bit of an extreme measure to take on the domain admin's part. If this happened, it's grounds for termination, I would expect. Would they really go this far?

Lastly, the SQL Server security/permissions heirarchy is rather complex. Please don't rely on this post for all the answers. Start with some of these links and learn as much as you can.

Permissions Hierarchy (Database Engine)

Server-Level Roles

Database-Level Roles

Nies