[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Encrypt an MS SQL Backup file

Posted on 2006-04-07
15
Medium Priority
?
575 Views
Last Modified: 2008-03-17
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?
0
Comment
Question by:fbmce
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16403563
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
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 16403576
otherwise you can use third party tools

http://www.red-gate.com/products/sql_backup/index.htm
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 400 total points
ID: 16403609
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:ptjcb
ID: 16403624
I agree with anneshattingal's suggestion of winrar - it is a very handy tool for compression and encryption.
0
 

Author Comment

by:fbmce
ID: 16403642
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.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16403682
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
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16403692
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.
0
 
LVL 37

Expert Comment

by:bbao
ID: 16422487
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
0
 
LVL 37

Expert Comment

by:bbao
ID: 16422494
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.
0
 
LVL 37

Accepted Solution

by:
bbao earned 1200 total points
ID: 16422510
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
0
 

Author Comment

by:fbmce
ID: 16428667
Ok, thanks to all.  I will research these options and return to close the thread by Tuesday 4/18
0
 

Author Comment

by:fbmce
ID: 16482923
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.
0
 
LVL 37

Expert Comment

by:bbao
ID: 16483385
> "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
0
 

Author Comment

by:fbmce
ID: 16507655
Thanks to all for this guidance.
0
 
LVL 37

Expert Comment

by:bbao
ID: 16518394
glad to help. :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question