Home Dashboard Directory Help
Search

Encrypt entire database on disk (database encryption) by M. Clark


Status: 

Closed
 as Postponed Help for as Postponed


2
0
Sign in
to vote
Type: Suggestion
ID: 241160
Opened: 11/27/2006 11:54:52 AM
Access Restriction: Public
0
Workaround(s)
view

Description

Would like SQL Server to offer way to natively encrypt and decrypt entire database at the file system level. This would be similar to what is offered by Encryptionizer made by NetLib. If performance to do this is negligible (as they state) then this would be a very valuable addition.
Details
Sign in to post a comment.
Posted by Microsoft on 3/11/2008 at 11:28 AM
Note, that this issue is addressed by the Transparent Data Encryption (TDE) feature in SQL Server 2008 (available in the current CTP).
Posted by Microsoft on 1/4/2007 at 10:28 AM
Closing this issue, as we are tracking such requests internally via VSTS 3867. We are considering similar solutions for a future version of SQL Server.

Thank you
Posted by Microsoft on 12/12/2006 at 5:50 PM
Thank you for your feedback.
Posted by M. Clark on 12/12/2006 at 5:57 AM
I am most definitely interested in this feature. I am not worried about an administrator in my situation, but I can see where others might be. I personally am more concerned about a hacker being able to copy the database and then simply attaching it at another location and having access to the data.

My key requirements would be:
1) Allow user to enter a key at SQL server install time to turn on encryption capability. If it is turned on, any new DBs will be created encrypted. If it is an upgrade, prompt user to select which DBs they want to encrypt (unless you will just force all DBs to be encrypted once encryption is turned on, which would be ok).
2) If user did not choose to encrypt at install time, still allow them to turn on DB encryption in the future. They simply provide a key. Give the option to encrypt any attached DBs at this time (again, unless you will force all DBs to be encrypted if encryption is turned on).
3) Ability to encrypt/decrypt a DB at will. When a DB is attached, give user an option to encrypt it if you will allow the choice of a DB being encrypted or not. However, it is a must that if it is detached you give the user an option to decrypt it before detach (I may want to detach from a test server and attach to a live server). Another option might also be to not decrypt it at detach, but when you try to attach it to the new server you have to provide the key that was used to encrypt it.
4) We've discussed in-depth the ability for someone to get the key. I understand that an administrator can install "spy" programs or packet sniff or whatever to get the key. Aside from that, I would want the key stored in some manner where it would be extremely difficult (impossible would probably not be feasible, but would be nice) for someone to get the key via any other manner.
Posted by Microsoft on 12/11/2006 at 3:33 PM
Encryption of data in the database does not prevent a machine administrator from accessing the data. It would be more like a lock on your door - it will only keep away someone that doesn't have the skill or force to open it, or that doesn't want to break the law. But the difference from the physical world is that attacks on your computer can be mounted from people living on the other side of the planet; against which the laws of the country you reside in might have no power. Also, in real life, you will know when someone steals from you, because you won't have that item anymore, but when database information is involved, you never know if it's been stolen already or not. So what might be viewed as sensible protection in the real world, is not necessarily adequate on a computer.

Protection from a machine administrator is different from protecting from a file loss or laptop loss. You can protect against these scenarios using the SQL Server 2005 encryption features, but they don't protect against an administrator reading the data.

The reason why these scenarios are different is that for a file loss or for a laptop loss, the thief will miss the key or the password protecting the key, so he won't be able to decrypt the data. But an administrator will always be able to get access to a key, because he has access to the live system to which the users submit passwords - a thief does not have any such access - this makes all the difference between these scenarios. The key part in your description is "if the key itself is highly encrypted by the system in some manner" - you can only encrypt a key by another key and at the end of the day you'll have to have something that is provided to the system in clear - either a key or a password - this is what a machine administrator will look for and he can always get this information if he really wants to and has reasonable programming knowledge.

I am not saying there is a difference between file-level encryption and database-level encryption as far as protection against an administrator goes - neither one will help you protect against the machine administrator.

Encrypting the database will help you protect against someone that can only steal the database file. Because the encryption key is not stored with the database, the thief won't be able to decrypt the data. But the encryption key will be stored in the system, so an administrator can get access to it. If you only want to protect against the first threat, which is a valid threat, this type of solution is very useful to you. If you only want to protect against the second threat, this will only provide protection from people that don't think the data you are protecting is worth the time needed to get access to it - it will basically act as a deterrent, which may be worth having, if this is what you want.

This is definitely not a useless feature, but we want to collect your unbiased feedback on what you would use this feature for and what level of protection you expect from it. Are you still interested in this feature if your only goal was to protect against an administrator and this feature can only be used as a deterrent? If yes, then we'll use your feedback for the design of such feature.

Thank you
Posted by M. Clark on 12/11/2006 at 12:56 PM
I am not a security or encryption expert. Having said that, you're saying that any skilled hacker or administrator can eventually get the encryption key and the protection is gone. If this is the case then how does encrypted data in the database itself guard against data loss/theft if the encryption key can be "gotten"? To me, encryption at the file level and encryption in the DB system itself should provide the same level of protection, just in different locations. If I have the encryption key and guard it, and if the system itself is the only other thing that has the key, and if the key itself is highly encrypted by the system in some manner, then the chances of someone getting the encryption key should be little to none. You are losing me in terms of why the file leve encryption (and key) cannot be just as secure as the internal DB encryption.

Furthermore, there must be some benefit to this above and beyond what it seems that I am able to articulate to you, since Encryptionizer and other products like it exist to do this already. If the database file-level encryption does as little as you state, then these products would have no purpose either, it seems.
Posted by Microsoft on 12/11/2006 at 11:33 AM
Note that such a feature (or any other, for that matter) cannot "protect" data from an administrator - it can only make it a little more difficult for the administrator to get the data, but he can eventually get the encryption key, and then the fact that the data is encrypted does not protect it anymore.

If protection from an administrator is the only scenario you are interested in, then this feature will not help much. Also, for the hacker scenario this provides no protection guarantee against someone that can hack their way into your administrator account.

Are you just looking for a "weak" guarantee of protection? Something that would only protect against an unskilled attacker, in which case, how would you define such an attacker?
Posted by M. Clark on 12/11/2006 at 11:19 AM
Yes, your scenario sounds reasonable. Anything that can be done to protect the data from the high-privileged user/hacker is welcomed.
Posted by Microsoft on 12/11/2006 at 11:01 AM
As I mentioned before, it's only the security motivations that we want to collect, so we can use them as a justification for allocating resources to such a feature. Therefore, any other aspects are not relevant at this time. Please only focus on describing scenarios where you think this feature would provide increased security.

You are probably aware that if the hacker breaks into the database server itself, the database encryption will not protect the data from him. Also, if the hacker breaks into the machine as a regular user, he will still not be able to access the database reasons because of the file security.

So, the only threat could come from an attacker that could break into the machine using a high-privilege account. Please confirm if this is the threat you had in mind. Database encryption could help in this scenario, if you could ensure that the high-privilege account cannot access the database server.

Thank you
Posted by M. Clark on 12/8/2006 at 9:40 AM
I forgot to say that the performance issue is moot since the user would make the decision to implement it, and would have the knowledge ahead of time about what the pros and cons of using it are.
Posted by M. Clark on 12/8/2006 at 9:39 AM
If a hacker gets onto your server then the added encryption would be helpful because it would prevent them from copying the file and attaching it at their location.

Additionally, I am not saying that this DB-level encryption is required. I am saying that if a user wants to use it they can set it up at install time, or at any point thereafter. If they don't want to use it they don't have to. It would be another tool that is available, but not mandatory.
Posted by Microsoft on 12/4/2006 at 10:47 AM
Thank you for your comments. The usability motivation is fairly clear. What we would like to validate is the security motivation. Database files and the folder where they are stored are ACLed in such a way that regular users cannot even see those files. Today, a regular user cannot browse the files, cannot copy them, and cannot open them in notepad or any other application. This file system protection already prevents stealing, without any encryption being used. So, what kind of attack do you want to prevent using encryption, which could not be prevented using file system protection?

Encryption is clearly going to lead to some performance hit, so from a usability perspective, if it does not provide any security advantage, there is no point in using it.
Posted by M. Clark on 12/4/2006 at 7:34 AM
Addressing the stealing the database issue -- it is true that OS file system functions (such as EFS) could be used to encrypt databases. However, this requires you to possibly have to change the log in information the SQL service uses, log into the server as the account the SQL service is running as, encrypt the files by hand, etc. It would be much simpler to maintain if you simply supplied a key and encryption method that you wanted to use and the SQL server itself encrypted the database and maintained the encryption.

I would also envision a GUI outside of SQL server that you could use to encrypt/decrypt a database if you wanted (maybe to move the database to another server). What I see here is: Detaching the database on a test server, using the GUI program to decrypt it, copying it to a live server, then attaching it. When the attach is done on the live SQL server, SQL server would re-encrypt the database with its key before completing the attach (if the DB is unencrypted). I would assume that if the DB is already encrypted with the SQL server's key it would just be attached with no problem. Also, you could probably do away with an external GUI program if you had options on detach and attach such as "detach and unencrypt" and just "detatch" (to keep encryption), and "encrypt and attach" or just "attach" (if the DB were already encrypted).

Also, short of someone stealing the database, this would prevent simple "browsing" of the file. In other words, someone could not just open the database file with Notepad or some other text editor and see what's inside the file if it were encrypted. As it stands now you can see a lot of the text that is inside the database by just browsing.

Right now these are the only two actual "security" scenarios I can think of for this, although I think these are pretty big reasons. Also, I don't think you can ignore the performance, ease of use and cost benefits. Anything that adds benefit to the product makes it more marketable and useful.

If I think of any more actual security benefits I will add more comments.
Posted by M. Clark on 12/4/2006 at 6:45 AM
Addressing the stealing the database issue -- it is true that OS file system functions (such as EFS) could be used to encrypt databases. However, this requires you to possibly have to change the log in information the SQL service uses, log into the server as the account the SQL service is running as, encrypt the files by hand, etc. It would be much simpler to maintain if you simply supplied a key and encryption method that you wanted to use and the SQL server itself encrypted the database and maintained the encryption.

I would also envision a GUI outside of SQL server that you could use to encrypt/decrypt a database if you wanted (maybe to move the database to another server). What I see here is: Detaching the database on a test server, using the GUI program to decrypt it, copying it to a live server, then attaching it. When the attach is done on the live SQL server, SQL server would re-encrypt the database with its key before completing the attach (if the DB is unencrypted). I would assume that if the DB is already encrypted with the SQL server's key it would just be attached with no problem. Also, you could probably do away with an external GUI program if you had options on detach and attach such as "detach and unencrypt" and just "detatch" (to keep encryption), and "encrypt and attach" or just "attach" (if the DB were already encrypted).

Also, short of someone stealing the database, this would prevent simple "browsing" of the file. In other words, someone could not just open the database file with Notepad or some other text editor and see what's inside the file if it were encrypted. As it stands now you can see a lot of the text that is inside the database by just browsing.

Right now these are the only two actual "security" scenarios I can think of for this, although I think these are pretty big reasons. Also, I don't think you can ignore the performance, ease of use and cost benefits. Anything that adds benefit to the product makes it more marketable and useful.

If I think of any more actual security benefits I will add more comments.
Posted by Microsoft on 11/30/2006 at 2:41 PM
Thank you for your answer.

Most reasons you provided relate to cost, ease of use, performance. But we would want to drill mainly in the security benefits of such feature. The one security scenario that you described (third motivation) was related to someone "stealing" a database file by copying it to his own server. Can you provide more details about this scenario? Why aren't OS file system security options sufficient to protect the database file from such an attack?

Also, if you have other security scenarios in mind, please include those as well.
Posted by M. Clark on 11/30/2006 at 12:03 PM
First, by adding encryption at the OS level, you could protect your sensitive databases with little to no performance impact.

Second, you would not have to modify existing applications to gain the immediate advantage of encryption.

Third, with the proposal I envision, a person could not simply copy your database to another machine, attach it, and have instant access to the data. If the database were removed from the machine it was encrypted on, it would be useless. This is also something Encryptionizer says it does.

Fourth, as it stands now (if I understand correctly), if you use SQL 2005 encryption you have to deal manually with your certificates/symmetric keys/Service Key, etc. to move a database from server to server (say from live to test). If you had a live server and test server with the same encryption key using this solution, you could copy the database from live to test with no problems and not have to deal with the manual processes related to service keys, etc.

Fifth, you would not have to purchase a third party package to gain these benefits. Additionally, I think this would give more overall value to SQL Server.

I'm sure there are many other reasons, but these are ones I had in
mind when making this suggestion.
Posted by Microsoft on 11/30/2006 at 11:03 AM
What is the motivation behind your request? What do you want to protect against by encrypting the database? Please provide several scenarios in which a similar feature would protect you against a threat. This information will help us determine what such a feature should be designed to address.

Thank you
Sign in to post a workaround.