February 22, 2012 at 7:30 am
Hi All
My client want to encrypt the database backup set. Is there any possibility to do it? I'm not speaking about TDE option.
Thanks.
February 22, 2012 at 7:31 am
TDE or a 3rd party backup tool.
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
February 22, 2012 at 10:46 am
Gail is correct. There are no other options in SQL Server to do this right now. You can password protect your backup, but that is not very strong protection. You could encrypt the data inside SQL Server before backup, but that creates other issues.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 22, 2012 at 10:52 am
g.korot (2/22/2012)
Hi AllMy client want to encrypt the database backup set. Is there any possibility to do it? I'm not speaking about TDE option.
Thanks.
Any reason that TDE is not an option? It does exactly what you want, It is a part of SQL Server and there is no need to add another tool for the backup.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2012 at 11:48 am
Steve Jones - SSC Editor (2/22/2012)
Gail is correct. There are no other options in SQL Server to do this right now. You can password protect your backup, but that is not very strong protection. You could encrypt the data inside SQL Server before backup, but that creates other issues.
I know that using of the TDE option can occur some slowness in the database activity. Because of it customer requested from me to find the another SQL option to encrypt backup files.
February 22, 2012 at 11:54 am
You've tested and found an unacceptable performance degradation from using TDE?
http://msdn.microsoft.com/en-us/library/cc278098%28v=sql.100%29.aspx
The performance impact of TDE is minor. Because the encryption occurs at the database level, the database can leverage indexes and keys for query optimization. This allows for full range and equality scans. In tests using sample data and TPC-C runs, the overall performance impact was estimated to be around 3-5% and can be much lower if most of the data accessed is stored in memory. Encryption is CPU intensive and is performed at I/O. Therefore, servers with low I/O and a low CPU load will have the least performance impact.
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
February 22, 2012 at 12:18 pm
GilaMonster (2/22/2012)
You've tested and found an unacceptable performance degradation from using TDE?http://msdn.microsoft.com/en-us/library/cc278098%28v=sql.100%29.aspx
The performance impact of TDE is minor. Because the encryption occurs at the database level, the database can leverage indexes and keys for query optimization. This allows for full range and equality scans. In tests using sample data and TPC-C runs, the overall performance impact was estimated to be around 3-5% and can be much lower if most of the data accessed is stored in memory. Encryption is CPU intensive and is performed at I/O. Therefore, servers with low I/O and a low CPU load will have the least performance impact.
No, I've not tested it, we are looking for the best option to secure the backup files. When customer will decide which option is better we will start to test it. It is sad but this is reality. Anyway thank you very much for the link above, it will help me to convince the customer that TDE option can be used or, as minimum, should be tested before the final decision will be taken.
February 22, 2012 at 7:16 pm
We are using TDE for one of our servers and have noticed only a minor impact, but it is not a heavily transacted database either. One of the impacts for TDE is with TEMPDB. When TDE is turned on for any database TEMPDB will always be encrypted. Also, keep in mind that TDE works as the disk level and will more than likely work for your needs.
There are some free tools, lite versions of the full blown from both Idera and Red Gate. might want to look at those.
February 23, 2012 at 3:33 am
You should test TDE before completely ruling it out, it might be within acceptable performance metrics AND its free (assuming you have the right version of sql of course 🙂 )
February 24, 2012 at 8:48 am
You should ask for further business requirements before proceeding. Why they want to encrypt their backups is a critical question.
If they want to check an internal checkbox some manager made up without any real care, then they probably don't care much, and any "encryption" will do... until it's broken.
If they want to prevent certain types of attacks or data leaks, then a more extensive analysis will be required. Do they want to protect the backups from network admins? The off-site storage facility? From their own DBA's? From one of the CxO's nosy child and their $500 laptop? From another CxO's nosy child and their $9000 dedicated cracking rig? From professional espionage agents because your company is bidding on sensitive defense contracts?
If they're trying to get into compliance with a regulatory rule or national law, then someone needs to read the rules and laws related, and translate them into technical requirements (i.e. "You must have a process that complies with Rule X, which means you must use algorithm Y or Z at a strength of Q").
If they're trying to prepare for certain types of (usually government) business, then it's like the above, but moreso - you may be required to use only products and versions of those products that a national agency has certified. Note: Even certification does not mean the product is actually secure; Schneier has an article on an example of this from 2010.
Further, ask what the rules are for the passphrase; note that however strong the encryption, a passphrase of "Password123" is not going to protect it from so much as a $200 netbook used in a hybrid dictionary attack for a few seconds (more likely, for a tiny fraction of a second).
There's many places you can encrypt backups.
TDE encrypts the data in its usable state, and that encryption carries over into the backups.
The third party SQL Server backup products typically encrypt the data on the path between the SQL server and the actual backup file; the data is in the clear at point A on the path, and encrypted at point B.
Truecrypt and other disk encryption products typically encrypt the data at the point the file is being written to the drive.
Likewise, hardware encrypted drive products like Datalocker typically encrypt the data as it's being written to the drive.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply