locked
Full Database-Level Encryption That Even Admins Couldn't Circumvent? RRS feed

  • Question

  • Hi all - This is a continuation of a previous post about TDE. During that discussion a different, more fundamental issue arose which I think is best addressed in a separate thread.

    The problem is relatively straightforward. Suppose a single SQL Server instance, with multiple databases containing extremely sensitive information of various fierce competitors. Let's say database A contains the formula for Coca-Cola, while database B contains the formula for Pepsi. (And please, don't tell me that Coke and Pepsi would never both use such a system; just humor me). I want to design this database application so that each database is separately encrypted, with a separate set of users who have the ability to decrypt it, such that it is impossible for anyone - even an admin - to be able to access either database unless the "client" (e.g., Coke or Pepsi, respectively) allowed it. The idea is to protect against hacked dba or system admin accounts and, of course, disgruntled employees. No one, ever, should be able to get the keys to the entire kingdom via one account.

    Is there any way for this to be accomplished in SQL Server (enterprise or otherwise)?

    As far as I can tell, TDE will not work for this. In standard TDE, the Database Master Key is common for all databases on the instance. Even if the DMK is password protected, which was suggested on the other thread, access to a single database still would imply access to all of them.

    Column-level encryption, it appears, might work, but it makes searching incredibly slow and full text indexing impossible.

    At bottom, my problem would be solved if I could use TDE and generate or import the individual Database Encryption Keys myself, and unlock them only with the credentials of an authorized user. But generating the DEK requires either a server certificate generated with the common DMK (defeating the purpose of what I want to do), or an external key management (EKM) system. I cannot find anything about how to create an EKM, nor is it even clear an EKM would not suffer from the same issue.

    What are my options here, and if the answer is there are none, I would really like to know what the rationale is for not having this ability. 

    Thanks a lot.


    Monday, September 19, 2016 12:52 AM

Answers

  • Unfortunately, there is no mechanism that would allow you to have both at this moment.

    Transparency (i.e. keep the ability to perform any query) would give the sysadmin the power to perform such operations by himself/herself; and if the data is decrypted on the same machine, the box administrator can also perform an online attack (i.e. he can simply debug SQL Server & capture the data).

    As Lin mentioned, Always Encrypted is probably the best option for your scenario. Since “Coca-Cola” would never share their master encryption key with “Pepsi”, and vice-versa. Even more, the sysadmin & box admins would have access to the databases & the ciphertext, but will never have access to the keys or the plaintext data.

    Even further, for a scenario like the one you described, I would recommend following the recommendations described on “Enabling Additional Recommendations for a Compromised SQL Server” section @ https://msdn.microsoft.com/en-us/library/mt757097.aspx . This would help reduce the possibility for a sysadmin to fool either “Coca-Cola” or “Pepsi” to disclose the data.

    Hopefully this information will help.

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by pnm655 Monday, September 19, 2016 5:41 PM
    Monday, September 19, 2016 5:25 PM

All replies

  • Hi pnm655,

    If I understand it correctly, you are looking for a solution that protects your database against your clients as well as your DBAs. In this case, I would suggest you implement different levels of encryption in your database: Encrypt all databases at database level with TDE, and encrypt sensitive data with Always Encrypted so only your clients could encrypt them. I would suggest you also take contained database into consideration as it can perform user authentication at database level.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Monday, September 19, 2016 3:31 AM
  • Thanks Lin, but unfortunately TDE does not accomplish what I want as far as being able to cryptographically restrict access to each individual database with a separate key that can only be unlocked with user credentials. 

    Always Encrypted is interesting, but only marginally better than column-level encryption, because it breaks most search capabilities.

    I need transparent data encryption implemented in such a way that decryption is only possible by someone with an authorized login and password. 

    Thanks.

    Monday, September 19, 2016 2:00 PM
  • There is no way to hide anything from a sysadmin on the SQL Server.  The sysadmin role bypasses all security settings in SQL Server.

    You need to trust your DBAs or fire them.   You can audit their activity.  However, as sysadmins, they can circumvent most auditing also.

    Monday, September 19, 2016 5:12 PM
  • Unfortunately, there is no mechanism that would allow you to have both at this moment.

    Transparency (i.e. keep the ability to perform any query) would give the sysadmin the power to perform such operations by himself/herself; and if the data is decrypted on the same machine, the box administrator can also perform an online attack (i.e. he can simply debug SQL Server & capture the data).

    As Lin mentioned, Always Encrypted is probably the best option for your scenario. Since “Coca-Cola” would never share their master encryption key with “Pepsi”, and vice-versa. Even more, the sysadmin & box admins would have access to the databases & the ciphertext, but will never have access to the keys or the plaintext data.

    Even further, for a scenario like the one you described, I would recommend following the recommendations described on “Enabling Additional Recommendations for a Compromised SQL Server” section @ https://msdn.microsoft.com/en-us/library/mt757097.aspx . This would help reduce the possibility for a sysadmin to fool either “Coca-Cola” or “Pepsi” to disclose the data.

    Hopefully this information will help.

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by pnm655 Monday, September 19, 2016 5:41 PM
    Monday, September 19, 2016 5:25 PM
  • Tom, I do not believe that's correct. Again, column level security would hide data even from admins, as would the Always Encrypted feature Lin cited. However, both features make searching difficult or impossible.

    This idea that "you need to trust your DBAs or fire them" is one I hear a lot, but it's a false choice. First off, DBA and IT admin accounts can be hacked, so it's not an issue about trust. Secondly, in my opinion no one, ever, should have the keys to the whole kingdom in a multi-tenant environment like I'm describing. People can be bribed, kidnapped, blackmailed, subpoenaed, or even tortured in extreme cases. If I'm hosting my customers' most sensitive data, and they have competitors who are also on that same system, there should be a way I can assure them that there is literally no one at my company who can see their data, even if our accounts are hacked or even if the government issues a subpoena.

    This is totally possible technologically. We are able to do it, quite simply in fact, for non-SQL data, using an asymmetric key for each user which protects a master symmetric key for each content store. We have also created a custom VFS for SQLite that allows transparent data encryption using the same system - asymmetric key of the user protects the equivalent of the Database Encryption Key for the database. Reading any data from the DB is not possible until some user with the appropriate credentials has unlocked the DEK using his password/secret, and then when the connection is closed, the DEK is discarded until the next connection.

    This is just not a feature of SQL Server, apparently. It should be.

    Monday, September 19, 2016 5:29 PM
  • For such requirements you can install a separate SQL Server instance per customer database, then you can define per instance/database who is member of the SysAdmin role.

    You can install up to 50 named instances per machine.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 19, 2016 5:31 PM
  • Hi Raul -

    Thank you for (a) confirming what I was afraid to be the case; and (b) the additional recommendations. I will check them out.

    I guess I'm looking for something semi-transparent. I think Always Encrypted is moving in the right direction, but it doesn't go far enough because the data is not readily searchable. It wouldn't work for full text indexing, for example.

    I appreciate that in my scenario, while the key is in memory, SQL Server would be subject to a debugging-type attack. This is probably unavoidable. But at least it would protect against the hacked DBA or sysadmin account, or the torture/subpoena (they are one in the same sometimes :)) situation.

    If I could protect the database-level DEK with a strong password that had to be provided with each connection string, the same way that we do with column-level encryption, that would really solve my problem I think. I would strongly request that be a feature. 

    We shouldn't let the perfect be the enemy of the better. The situation could be a lot better than it is. You could have fully searchable encrypted data that is cryptographically isolated by customer/tenant. People are asking for it financial institutions, especially; legal, of course. This isn't going away. I wish I had a better answer to tell them than "you have to trust my DBAs". 

    Monday, September 19, 2016 5:41 PM
  • Your original post was about TDE.

    Yes, Row Level security in SQL 2016 applies to all users.  However, sysadmins can turn off row level security and/or TDE read the data, and turn it back on.  Your best method is to audit that activity. However, sysadmins can also disable the audit and all you will see is audit disabled and audit enabled.

    https://msdn.microsoft.com/en-us/library/dn765131.aspx?f=255&MSPPError=-2147217396

    Security policies apply to all users, including dbo users in the database. Dbo users can alter or drop security policies however their changes to security policies can be audited. If high privileged users (such as sysadmin or db_owner) need to see all rows to troubleshoot or validate data, the security policy must be written to allow that.

    It is extremely difficult to "hack" an Active Directory login.  It is possible to get the password, through many means, and login as that user and do damage.

    Even local system Windows administrators can also get into SQL Server and make themselves sysadmins, then do stuff and remove themselves. Without an audit you would never know. 

    What we did in our environment was no on was a sysadmin.  You had to request a sysadmin login, which had an approval process and a timelimit.


    Monday, September 19, 2016 5:50 PM
  • Thanks, Olaf, that's certainly true and for my most sensitive customers, this is what I am going to recommend. 

    Of course, that ain't gonna be cheap if they want to use TDE.

    Monday, September 19, 2016 5:53 PM

  • It is extremely difficult to "hack" an Active Directory login.  It is possible to get the password, through many means, and login as that user and do damage.

    Even local system Windows administrators can also get into SQL Server and make themselves sysadmins, then do stuff and remove themselves. Without an audit you would never know. 

    What we did in our environment was no on was a sysadmin.  You had to request a sysadmin login, which had an approval process and a timelimit.


    Sorry I meant to say "column level encryption." And yes that's exactly the scenario I'm concerned about - getting the password through some means and then impersonating the sysadmin.

    I like your approach to having no sysadmin. I assume there was some master admin account with a password that was locked in a safe somewhere? 

    I just think fundamentally this is something that should be addressed, and the thing is it's not particularly technologically challenging to address it. I have to scratch my head sometimes about where peoples' priorities are. For example, people will crucify you if you suggest putting a SQL Server on the public Internet, no matter what security controls you put in place. But the idea that, with a few exceptions like column-level encryption which are far from optimal solutions, a single person with a sysadmin account could walk away with every byte of data on a SQL Server instance doesn't seem to keep anyone up at night. Perhaps it's because we fundamentally don't like the idea of protecting data from ourselves, or think it's not really that important. But it really is.

    Monday, September 19, 2016 6:00 PM
  • There are several products which manage passwords and "Password Access Control" and auditing and can create temporary access to SQL Server.

    This is one I found in searching (we used a different one which I can't remember the name of):

    https://www.manageengine.com/products/passwordmanagerpro/features.html

    I have not used it and have no relation with this product.

    Although it can happen, it is a main theory of IT security is that your employees are properly vetted and not going to harm your system or steal, or at least much less likely.  It is much more common for a problem from the outside.  You need to focus on outside threats much more than inside threats.

    Also, keep in mind, the lowest paid people in most companies are the Windows System Administrators, and they have the highest access level of anyone in your company and can do the most harm to your company with a single keystroke.

     



    Monday, September 19, 2016 6:50 PM
  • We shouldn't let the perfect be the enemy of the better. The situation could be a lot better than it is. You could have fully searchable encrypted data that is cryptographically isolated by customer/tenant. People are asking for it financial institutions, especially; legal, of course. This isn't going away. I wish I had a better answer to tell them than "you have to trust my DBAs". 

    But if you have your "send the password in the connection string", your answer would still have to be "you have trust my DBAs". It's only that they need to be more skilled to grab the password in SQL Server's memory. And they might not do it just ouf of curiousity.

    There is also the question with this solution: how would backup work? If the customers host their databases at your site, they probably expect you back up the databases?

    Obviously you can backup the MDF files, you can do SAN replication. But it would be difficult to provide up-to-the point recovery, because you cannot read the transaction log. The customers can intiate the backup themselves. But that begs the question why they should have the database by you - they could just as well have it on their on premises.

    Monday, September 19, 2016 9:38 PM

  • But if you have your "send the password in the connection string", your answer would still have to be "you have trust my DBAs". It's only that they need to be more skilled to grab the password in SQL Server's memory. And they might not do it just ouf of curiousity.

    There is also the question with this solution: how would backup work? If the customers host their databases at your site, they probably expect you back up the databases?

    Obviously you can backup the MDF files, you can do SAN replication. But it would be difficult to provide up-to-the point recovery, because you cannot read the transaction log. The customers can intiate the backup themselves. But that begs the question why they should have the database by you - they could just as well have it on their on premises.

    Well, yeah as I said in-memory theft is probably never going to be preventable any time soon.  Again though it's not so much that I don't trust my DBAs it's that I don't want them to be tricked / hacked / kidnapped / tortured / subpoenaed into opening up the system. 

    And yes, recovery would also have to be initiated by an authorized admin with credentials to the specific database. In my application each "matter" has a separate database, and there are "matter" admins and regular users. The "matter" admins would be the ones expected to do recovery via the application. 

    As for who's hosting it, we have two types of customers. The smaller ones have no expertise or ability to host data themselves, but they nonetheless don't want anyone to be able to see their data other than themselves. Think a solo law practice who would like an online cloud platform but is absolutely opposed to anyone other than himself being able to see his client's data online and probably would not use my platform if his opponent were also on it (unless I could guarantee him no one here could see either party's data). Our other type of customer are big legal service providers who will actually do their own hosting, and offer the platform directly to their own lawyer clients. They have value added services such as managing projects and doing data ingestion and exports, where they make most of their money. If you've ever heard of Relativity by kCura, it is a similar business model.

    Monday, September 19, 2016 10:32 PM
  • Well, yeah as I said in-memory theft is probably never going to be preventable any time soon.  Again though it's not so much that I don't trust my DBAs it's that I don't want them to be tricked / hacked / kidnapped / tortured / subpoenaed into opening up the system. 

    But that would still be possible with password in memory.

    If the password could be something like an RSA device, that is, it changes every two minutes or so, it's getting more difficult to get unauthorised access. Even more so, if the opened database is only available to the user who opened it.

    The application might be a hassle to use though. But security often implies a lot of hassle. Getting both security and off-site administration and all the flexibilty of searching is not easy at all.

    Maybe the best is to use a big cloud service like Windows Azure. As I understand it, they have quite heavy securities when it comes to the physical access. And since they have so many servers there, it's a needle-in-a-haystack problem for an intruder to find the one single server he is looking for.

    Tuesday, September 20, 2016 9:26 PM
  • Erland,

    So do you think the philosophy of MS and other folks is that, look, the attacker with elevated privileges can get to everything while it's in process anyway, so why bother trying to protect it from them while it's at rest? 

    I guess I can see the point, but then by the same logic (as Simon McAuliffe notes), TDE itself seems like a waste of resources and possibly worse than nothing due to the performance hit and the false sense of security.

    But I still think there are good reasons to try to protect the data from a privileged attack while it's at rest, even if you can't 100% protect it in process. For instance, even if the DEK is in memory while the connection is open, yes, the privileged attacker gets to see THAT database's data, but that's it. He'd be at the mercy of chance as far as which databases happen to get connected to while he's in the system and before someone realizes what's happening and can boot him out. At least the damage is relatively contained.

    Also, the subpoena issue is no joke. I'm sure you know about what Apple went through with the FBI and the iPhone of the San Bernadino shooter. In my system the data is of a legal nature, and so subpoenas are even more common. We want to be able to say, in response to a subpoena, "sorry, we have no ability to access the data without an authorized database user." Then it becomes between the user and the requestor, and we don't have to get involved. 

    I like Windows Azure databases a lot and was originally planning to use them for this. The problem at bottom is the same though - whoever owns the Azure account can get to all the data, so if the Azure account is compromised that's the ballgame. 

    Incidentally I did a lot of looking into EKMs. The Azure key store is promising, but ultimately not a viable solution for me, because it requires the "application secret" of the entire key store to be known to the SQL Server, and hence to the DBA. With the application secret, the DBA can assign the keys stored in the EKM to any SQL login, including himself, so that defeats the purpose too. If I could build a custom EKM system similar to the Azure key storage system, I could probably solve the issue, but I cannot find anything by way of APIs or SDKs for doing so. It appears to be a proprietary technology that MS does not open up to any old developer. Though I will probably start a new thread to inquire to find out for sure. 

    But as you suggest, an RSA device could really close the gap almost completely, and if used in conjunction with a custom EKM, user inconvenience could probably be kept to a minimum. 

    Anyway I'm sure we're driving the moderators crazy with this off topic discussion, but it's very interesting. Feel free to PM me if you want to discuss further (peter at mooreusa dot net).

    Wednesday, September 21, 2016 4:45 PM
  • I would not say that it is off-topic. After all, it's about security and it's about SQL Server. And it is illuminating for those who want encryption, but not really understanding why.

    I guess one reason that Microsoft is reluctant to design a feature on the line we discussed is that it is a combination of that it is still only security by obscurity, albeit of a high level, and with the difficulty in manageability. There is also risk with "too" secure solution, that casual sites shoot themselves in the foot and locks themselves out - and then Microsoft gets the flak for giving them the possibility.

    The problem with the Azure account being compromised brings up an interesting observation: obviously, accounts can be breached on the client's side, particularly at a smaller law firm with low IT awareness. Of course, what's important to you is that your access is not compromised, because if it is, you have not lived up to your promise.

    I don't question you on the subpoena thing. That is maybe the biggest threat of them all, because it can be very persistent and much more difficult to walk away from.

    Wednesday, September 21, 2016 9:37 PM
  • By the way, an MVP colleague told me how things where he works - and where they don't trust their DBAs. If they need to access the production box, they need to be two persons in the computer room. Each of them has their half of the password. While they are working, a third person is monitoring them remotely and can cut them out if they would perform improper actions.

    I believe that this is a hedge fund, so they may have the economic muscles to do this.

    In any case, the arrangement is not safe against a subpoena threat. But maybe they have very good lawyers.

    Thursday, September 22, 2016 7:13 AM