December 28, 2010 at 10:51 am
I am new to Encryption methodology and would like to know thought's from other DBA' s on this for sql server 2008. I would like to implement encryption to tighten security. I know there is table encryption as well as database encryption. I need to the following:
i) What are the differences between table encryption and database encryption
ii) If i am not wrong db encryption is only available in enterprise edition for sql 2008?
iii) What are the performance impacts if any if db or table encryption is used?
iv) which one of these table or db encryption is complex to implement and manage?
Thanks
December 28, 2010 at 10:55 am
Column encryption and database encryption are completely different things, with different purposes.
Column encryption protects against unauthorised people querying the data
Database encryption protects the database files and backups from being copied and restored/attached on a different server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2010 at 7:46 am
If i enable column and database encryption are there any performance hits?
December 29, 2010 at 7:49 am
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2010 at 7:55 am
I would imagine someone has blogged about perf hits for each type of encryption by now. Simple web search should get you some numbers. However, it should be noted that those numbers may not translate to YOUR particular application(s).
Some points:
1) column encryption voids the use of an index seek if the column is indexed, meaning you must scan/decrypt all values in the indexed column for seeks. This is often helped by using a partial column that is indexed to narrow the lookup first (such as last 5 of credit card number or social security number for example).
2) database encryption also encrypts tempdb, so EVERY database on the server will be affected by this action in as much as those other database also use tempdb.
3) if all you need to do is obfuscate a value and are not required to have true encryption, check out using some form of HASH which is repeatable. This gets those index seeks back and is thus very efficient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 29, 2010 at 8:05 am
TheSQLGuru (12/29/2010)
1) column encryption voids the use of an index seek if the column is indexed, meaning you must scan/decrypt all values in the indexed column for seeks. This is often helped by using a partial column that is indexed to narrow the lookup first (such as last 5 of credit card number or social security number for example).
A very good reference on this is the Encryption chapter of the book "Expert SQl Server Development". Strongly recommend if you're looking at column encryption.
In my opinion, the biggest mistake people make when starting with column encryption (not TDE) is trying to encrypt everything. Probably the worst thing you can do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2010 at 8:13 am
Can you please give me some details like what performance hits will we come across or if you can point me to a article that would also be helpful. Thanks
December 30, 2010 at 9:57 am
I have done some research on this very topic. Here are my views.
The other poster is correct in that they are for different things.
Column level encryption is available on most editions. It actually works on the Express Editions of 2005 and 2008!
CLE encrypts only the columns that need encrypting. There is no performance overhead for dealing with columns that are not encrypted. There is overhead for the encrypted columns. Use Symmetric keys to directly encrypt the data (it is faster than Asymmetric or Certificates). Use Certificates and/or Asymmetric keys to protect the Symmetric keys (utilizing the encryption hierarchy).
Drawbacks to it include the following:
1) All encryption and decryption of data is done manually, using function calls.
2) all keys must be opened and closed manually in order to make the function calls
3) only one datatype can be used for the encrypted columns: varbinary
4) no indexes can be created on the encrypted column
5) doesn't provide as much protection as TDE for theft of physical files (can be mitigated by using the WITH PASSWORD= clause of the backup statement, since restoring a database on another instance allows the user to run the SPROCs as a db_owner or sa. Password clause stops them from being able to restore the database.)
6) applications can be impacted by the manual steps (mitigated by using SPROCs)
Stored procedures mitigate some of the complexity. They are executed on the server, so no keys have to be transmitted across the network. (Note that the decrypted data is sent across the network. This can be mitigated by requiring SSL connections to SQL Server, through the use of certificates.) Stored procedures can be encrypted themselves with the WITH ENCRYPTION clause, which will hide any pass phrases used to protect the keys.
Can be easier to move to another server than TDE (Transparent Data Encryption - database encryption), according to the encryption infrastructure used. Key management is important with either encryption method.
CLE is difficult to add to existing databases because of the datatype required, and the opening of keys and making function calls. Best to do CLE when starting a database from scratch.
TDE is only in the Enterprise Edition. It is more hands-off once implemented, however, you have to make sure you have very strong encryption key management in place in case you have to move the database to another server. In both encryption methods, data is stored in the buffer pool unencrypted. TDE is much easier to add to an existing database, since no programming changes need to be made. TDE allows the use of the normal data types, and allows normal indexes on the columns. It doesn't require any programming changes to implement it.
Disadvantages of TDE:
1) Estimated 3%-5% performance hit (constant, since everything in the database is encrypted)
2) Requires the encryption of Tempdb (even if no other database on the instance is using TDE) This can be a downside in itself.
3) Does not encrypt data that bypasses the buffer pool - for example, FILESTREAM
I suggest getting the book "Expert SQL Server 2008 Encryption", 2009, Apress, Michael Coles and Rodney Landrum. Very informative.
A good whitepaper is "SQL Server 2008 Security Overview For Database Administrators", Microsoft, Jan. 2007, updated July 2008.
Hope this helps.
BobL
December 30, 2010 at 12:26 pm
Full database encryption is usually what I refer to as lazy encryption. It only protects data at rest and doesn't protect against someone who has access to the data. A previous poster mentioned using the password for backups, in previous versions MS commented that wasn't a particularly robust protection method. CLE encryption is generally the best because it only encrypts the data that MUST be encrypted. And if we are honest with ourselves there is probably only a few columns in any secured database that contain data that needs secured.
CEWII
December 30, 2010 at 12:45 pm
sqldba_icon (12/29/2010)
Can you please give me some details like what performance hits will we come across or if you can point me to a article that would also be helpful. Thanks
There are a couple good posts in there, but the performance hit will be CPU. It takes CPU power to encrypt/decrypt the data. If you are CPU bound, then this might be an issue for you.
There is also the possibility that this will lower compression ratios you get from backup products, so this could impact disk space, backup time, etc.
December 30, 2010 at 7:13 pm
Thanks everyone. I will have to check back with developers to see if they have the capability to implement CLE. After reading all the responses i do not think it is worth going for Enterprise Edition just for db level encryption( other features in EE is not required for us)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply