So, I was asked today a question that made me have to think a bit.
Scenario: The network admin would build out our server. The database team (DBA) would take over and install MS SQL Server and maintain it.
Business Expectation: data is sensitive (including no viewing for network admin)
Question: Is it possible to prevent the network admin from accessing our databases? My first thought is NO because the network admin sets the local permissions. But then I thought about it and realized that if MS SQL Server is installed on Mixed Mode, then the SA account can go in and remove/control all Windows Authentication accounts right? Once all users are assigned properly, then only the dedicated Windows Authentication or SQL accounts will have access to our databases.
If I remove the Network Admins from the local admin group, how would they log into the server to do updates or so? I only want to prevent them from getting to the databases on that server. Can I get more of your thoughts? Thanks
Yes, in theory you can secure SQL from Windows admins. And using exactly the methods you stated.
The big problem is you coming in thru a "DBA" AD group. The problem is that any AD admin can add more people, i.e. themselves, to that "DBA" group and thus get "DBA" permissions.
To be really secure, you might need to come in only from a native SQL account, and that causes many other security issues, particularly when trying to access remote resources.
So overall it's a pain to do, but it can probably be done.
The business expectation is unreasonable. It is like asking a chef to make a 4 course fancy meal but he can only use specific set of cooking utensils in a cabinet full of other utensils but he has to keep his eyes away from other utensils in the closet. How about DBAs? Why is the 'network admin' not allowed to see things in the database. there seems to be more to this than meets the eye. probably need to implement some sort of audit trail. people sign papers that they will keep confidentiality. if IT folks have bad intent they can find ways to do it. but most are professionals and you have to work in a healthy trust system
Local Administrators can always start SQL Server in Single User mode and do what they want. (A network admin could even do this with a server backup and no one would be the wiser.)
Even with the databases and backups encrypted I suspect it would not take too much effort to find data in the buffers etc. If you do not trust your own admins you could look at using Azure's platform as a service but then you have to trust Microsoft's admins and security.
I agree with Yosiasz.
The reason why the business is requesting this is because the parent company is a separate entity and therefore the child company is trying to secure client data.
Sounds to me that regardless, a network admin will have access, whether if it is from the parent company or Microsoft Azure or even the child company setup there own network.
If the data is that secure, I figured TDE would be in place. At least then they'd have to restore more things than just the db to be able to read it.
But, yes, ultimately MS has given too much authority to network admins as regards to SQL Server and data in SQL Server. They should correct that, the way Oracle has; Oracle can prevent even DBAs from seeing the data in Oracle while allowing them to administer other aspects of that schema/table ("database" in Oracle is effectively the whole instance, separation is done by schema rather than by "database").
