Encrypt an MS SQL Backup file

The SQL databasse gets backed up every night, both to the Hard Drive (which gets recorded to DAT tape) and also directly to the tape.
I take "yesterday's" backup DAT tape home in my briefcase every night.
It contains "lead data" and the associated credit card numbers.
Losing a tape would be a security disaster.
What do I do to encrypt the data in the backup?
fbmceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
one option:
use some compressing tools like winrar / winzip to encrypt and compress these bak files , i prefer Winrar
option 2:
there are other tools especially to encrypt the data
Aneesh RetnakaranDatabase AdministratorCommented:
otherwise you can use third party tools

http://www.red-gate.com/products/sql_backup/index.htm
ptjcbCommented:
You will probably be looking at 3rd party tools like SQLLiteSpeed or RedGate. They can compress and encrypt backups. There are no native solutions that I know of for SQL Server.

http://www.quest.com/litespeed_for_sql_server/

http://www.sql-server-performance.com/da_redgate_sql_backup_spotlight.asp
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ptjcbCommented:
I agree with anneshattingal's suggestion of winrar - it is a very handy tool for compression and encryption.
fbmceAuthor Commented:
But SQL is doing the backup at 10:30pm (after the last data entry technician leaves) and the Tape records at midnight.
I need an automated method.
Aneesh RetnakaranDatabase AdministratorCommented:
Either use the third party tools or schedule a job to encrrypt the backed up file using rar.. u can use windows scheduled tasks for this
ptjcbCommented:
The 3rd party tools (SQLLiteSpeed, RedGate, etc) can be automated. You can also create a command line WinRar that will do everything (it is not the simplest method, but it can be done), and then run that as a job.
bbaoIT ConsultantCommented:
two approaches are recommended:

1. DISK based encryption: it must be an automatic solution because you do not need to manually encrypt the file. encrypting happens while you or your application, such as SQL, actually writes (create/save/update/backup/copy) to the encrypted disk. MS officially provides EFS for this kind of requirements on 2K/XP/2K3 platforms; some 3rd party vendors also provide similar solutions, such as virtual encrypted volume. in case of losing your backups, other people can not access the data without importing the correct key. of course, you need to protect the key very well by yourself. it is suitable if only you or a few specific authorized people are allowed to access the encrypted backups. it sounds like your scenario.

2. FILE based encryption: most of tools and utilities mentioned above belong to this approach. you need to enable OS's task scheduler to invoke a piece of codes (command/batch/script) to activate encrypting. some utilities have built-in automatic feature. it is suitable if you need to (timely) exchange the encrypted data with your partners, especially over the internet. personally, i suppose this is not your scenario.

here are more optional tools and utilities:
http://www.eurodownload.com/download-category/11/130/Security-&%3B-Privacy/Encryption-Tools.html

hope it helps,
bbao
bbaoIT ConsultantCommented:
BTW, if you prefer FILE base solution, you'd better choose those utilities which support AES, at least 128-bit, 256-bit or higher is recommended.
bbaoIT ConsultantCommented:
FYI:

Free file encryption with the Windows XP Encrypted File System (EFS)
Tutorial, Usage, Security, and Trouble-Shooting.
http://www.iopus.com/guides/efs.htm

MS official documents: Encrypting File System overview
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/encrypt_overview.mspx?mfr=true

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fbmceAuthor Commented:
Ok, thanks to all.  I will research these options and return to close the thread by Tuesday 4/18
fbmceAuthor Commented:
I met w/ our network geek, and he's under the impression (not knowing a lot about  Encrypted File System himself) that any file that's taken out of the encrypted directory will be un-encrypted, including, he surmises, the SQL backup as it is copied to tape.  In other words, it will not be encrypted when it gets to the tape.

According to Microsoft's web site (that bbao referenced above):
"Encrypted files can become decrypted if you copy or move the file to a volume that is not an NTFS volume."
But then goes on to say:
"Moving unencrypted files into an encrypted folder will automatically encrypt those files in the new folder. However, the reverse operation will not automatically decrypt files. Files must be explicitly decrypted"
and then:
"if you open the encrypted file over the network, the data that is transmitted over the network by this process is not encrypted...... WebDAV, however, is able to encrypt the file locally and transmit it in encrypted form"

Another question qualifies as a possible "stupid question":  is there such a thing as "double-encryption"?  In other words, if I restore an encrypted file into this EFS directory, will it get another encryption (encrypting an encrypted file)?

Does anyone know the answers to these?  As you know, I need a solution that will enable the backup file to stay encrypted on the backup tape.  I'm doubling the points for this.
Thanks.
bbaoIT ConsultantCommented:
> "Encrypted files can become decrypted if you copy or move the file to a volume that is not an NTFS volume."

you need to be the right local user, who can access the file locally, for doing such copying or moving operation. in other words, you must have been authorized before the operation (obtained the token) and have been authenticated (checked the token) at the operation. so it is safe.

> "Moving unencrypted files into an encrypted folder will automatically encrypt those files in the new folder. However, the reverse operation will not automatically decrypt files. Files must be explicitly decrypted"

it is due to safety considerations.

> "if you open the encrypted file over the network, the data that is transmitted over the network by this process is not encrypted...... WebDAV, however, is able to encrypt the file locally and transmit it in encrypted form"

yes. EFS intends to protect illegal and physical access to the raw data on the disk, so it is an effective  way to protect local data, for example, to keep privacy even the disk is lost or moved. it is NOT for secure network transferring, that is the matter of other OS protocols, such as VPN or IPSec.

> is there such a thing as "double-encryption"?  In other words, if I restore an encrypted file into this EFS directory, will it get another encryption (encrypting an encrypted file)?

yes. if you copy an encrypted RAR file to an EFS volume, double-encryption will actually happen. combining the two approaches (DISK+FILE based) is also a solution, in order to keep file transferring and storing secure at the same time if necessary.

hope it helps,
bbao
fbmceAuthor Commented:
Thanks to all for this guidance.
bbaoIT ConsultantCommented:
glad to help. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.