January 26, 2014 at 4:16 pm
Hello,
I need experts advice on Backup encryption options available in SQL Server 2008 R2 Standard Edition. I know TDE is available, but only in Sql Server 2008 R2 Enterprise edition. But we are using Standard Edition.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 27, 2014 at 12:07 am
With Standard edition you'll need to look at 3rd party backup tools. I believe Red Gate's SQLBackup offers encryption, probably several others do too.
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
January 27, 2014 at 4:58 am
Just about all the mainstream 3rd party backup products (SQLBackup, Litespeed, etc) offer backup encryption and can be used in conjunction with compression. Something which is currently limited natively.
Given the relatively low cost of licences for these products, they make excellent sense and IMHO are more flexible than native backups 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2014 at 6:18 am
Perry Whittle (1/27/2014)
Given the relatively low cost of licences for these products, they make excellent sense and IMHO are more flexible than native backups 😉
other than encryption, what makes you say that Perry?
---------------------------------------------------------------------
January 27, 2014 at 6:30 am
george sibbald (1/27/2014)
Perry Whittle (1/27/2014)
Given the relatively low cost of licences for these products, they make excellent sense and IMHO are more flexible than native backups 😉other than encryption, what makes you say that Perry?
For instance Litespeed has resilience options for disk and network which are extremely useful. The extended stored procs are easily incorporated into a backup script and can provide extra useful output via their own log files. The extended stored procs work across all versions. Its also easy to use command line objects too for backups and restores. I especially like the option to generate a self extracting backup which i can send out to a vendor with an encrypted version of my backup password, the vendor has no need to know the actual password, they supply the encrypted key directly.
For more info, refer to the particular 3rd party manuals (whichever one you use) the level of flexibility will be self evident.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2014 at 6:45 am
ah, I see. Its a personal preference but I don't like having a layer of obfuscation between me and my backups.
having said that we use hyperbac where we need encryption and for compression on pre SQL2008R2 servers. Hyperbac plugged straight in with no backup script changes, but redgate bought it and its been subsumed by redgate SQLbackup. <sigh>
---------------------------------------------------------------------
January 27, 2014 at 7:49 am
george sibbald (1/27/2014)
ah, I see. Its a personal preference but I don't like having a layer of obfuscation between me and my backups.
A personal preference which i'm sure a lot of users share too. There is no level of obfuscation what makes you think this?
george sibbald (1/27/2014)
having said that we use hyperbac where we need encryption and for compression on pre SQL2008R2 servers. Hyperbac plugged straight in with no backup script changes, but redgate bought it and its been subsumed by redgate SQLbackup. <sigh>
Thats because Hyperbac operates outside of sql server and captures the straems at the OS level. Litespeed have a similar tool called Litespeed engine for sql server. You install and configure the engine. You still send standard TSQL backup commands such as
BACKUP DATABASE noddy to DISK = 'Somedrive:\somepath\somefile.bak'
The engine works outside of sql at the OS level and intercepts the OS threads compressing or uncompressing as required.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2014 at 8:19 am
Perry Whittle (1/27/2014)
george sibbald (1/27/2014)
ah, I see. Its a personal preference but I don't like having a layer of obfuscation between me and my backups.
A personal preference which i'm sure a lot of users share too. There is no level of obfuscation what makes you think this?
maybe obfuscation is the wrong word. I inherited some servers once for a while which needed encryption and were previously supported by a non-dba so it had redgate on it and I was stuck with it. Personally I did not need the different look and feel of a gui outside of SSMS, different backup commands and a set of error codes that bore no resemblence to the actual OS error codes I knew and loved. For me it just made it harder to do the core part of my responsibilities. I was forever going to have and look up the password as well!
george sibbald (1/27/2014)
having said that we use hyperbac where we need encryption and for compression on pre SQL2008R2 servers. Hyperbac plugged straight in with no backup script changes, but redgate bought it and its been subsumed by redgate SQLbackup. <sigh>
Thats because Hyperbac operates outside of sql server and captures the straems at the OS level. Litespeed have a similar tool called Litespeed engine for sql server. You install and configure the engine. You still send standard TSQL backup commands such as
BACKUP DATABASE noddy to DISK = 'Somedrive:\somepath\somefile.bak'The engine works outside of sql at the OS level and intercepts the OS threads compressing or uncompressing as required.
interesting about litespeed, thanks. Do you know if the latest incarnations of redgate backup can work in the same way?
EDIT: darn quotes
---------------------------------------------------------------------
January 27, 2014 at 8:58 am
george sibbald (1/27/2014)
I don't like having a layer of obfuscation between me and my backups.
In fact the only level of obfuscation is on the part of a 3rd party as you never actually provide them with the real password used to create the backup. The key is obfuscated to them only.
george sibbald (1/27/2014)
I was forever going to have and look up the password as well!
It's no different to storing the service account passwords, which i have stored together in server\instance level in a password safe
george sibbald (1/27/2014)
interesting about litespeed, thanks. Do you know if the latest incarnations of redgate backup can work in the same way?EDIT: darn quotes
I'm guessing the Hyperbac tool is Redgate's version of Litespeed engine for sql server, i haven't used SQLBackup for a while now as all my previous contracts have been Quest Litespeed orientated.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2014 at 9:06 am
I'm guessing the Hyperbac tool is Redgate's version of Litespeed engine for sql server, i haven't used SQLBackup for a while now as all my previous contracts have been Quest Litespeed orientated.
it was (sort of), but they went and retired it http://www.red-gate.com/products/dba/sql-hyperbac/ :crying:
---------------------------------------------------------------------
January 27, 2014 at 9:51 am
Thanks a lot for the inputs. My initial thought was to use the option MediaPassword while backing up a database and script out the database backup instead of using a maintenance plan. But now I will probably test the SQLBackup/Litespeed as it appears to be the option.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 28, 2014 at 8:57 am
You can also backup to an encrypted drive/directory, be it http://www.vormetric.com/products/encryption (paid), or http://www.truecrypt.org/ (free)[/url], or the old http://sourceforge.net/projects/freeotfe.mirror/ (free)[/url], or an external drive with hardware encryption like the http://www.apricorn.com/[/url] or the http://datalocker.com/[/url].
On the free side, Truecrypt is very commonly used on Windows, and is being audited by third parties now (http://istruecryptauditedyet.com/[/url])
On the paid side, Vormetric is awesome.
On the hardware side, just decide whether you don't care about regulatory compliance, need regulatory compliance (FIPS 140-2, for example), or need regulatory validation (if so, only use what the vendor says to look it up on http://csrc.nist.gov/groups/STM/cmvp/documents/140-1/140val-all.htm yourself!)
January 28, 2014 at 9:07 am
Sapen (1/27/2014)
Thanks a lot for the inputs. My initial thought was to use the option MediaPassword while backing up a database and script out the database backup instead of using a maintenance plan.
MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
Sets the password for the media set. MEDIAPASSWORD is a character string.
Important:
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
If a password is defined for the media set, the password must be supplied before you can create a backup set on that media set. In addition, that media password also must be supplied to perform any restore operation from the media set. Password-protected media may be overwritten only by reformatting. For more information, see the FORMAT option. (For more information about using passwords, see the Permissions section later in this topic.)
Security Note:
The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password. The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply