We are trying to encrypt/decrypt a SQL Server 2005 database file.
It is my understanding that you can encrypt the main database, but not
its log file. The database file was successfully encrypted, but SQL
Server failed to decrypt it on opening after a many minutes delay. The
database was subsequently decrypted with a manual command, but the
database had been damaged and couldn't be re-opened. It had to be
deleted and restored.
It appears that there is no practical way to use an encrypted SQL
database because of apparent glitches and the extremely slow decryption
process.
We have considered backing up the database, encrypting the backup copy,
deleting the database from the SQL directory on shutdown and then
restoring it on startup. Another alternative is to store the data on
removable media.
I would greatly appreciate a suggestion as to how to best protect the
data. We use SecuriKey to protect OS system startup. This works, but it
doesn't protect the data if, for example, the hard drive is moved to
another computer.
I have read the following article:
[url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/url
]
Thank you very much.
Robert RobinsonHave you looked at using Windows Encrypted File System? That's the supported
way of protecting your data at the filesystem level. There are a few things
to be careful with paticularly with login/permissions management when
encrypting the folder but it's not rocket science (and well document in
msdn/technet).
As for losing the drive, well, not much you can do there really. Even if you
encrypt the filesystem, that generally just delays the would-be thief. When
you lose the hardware, pretty much all bets are off. If you're thinking of
notebooks, you can implement both EFS and secure the hard disk with a
password (go to setup when you boot). That makes is REALLY hard to get
through and will probably buy you enough time to initiate all kinds of
remedial defense actions (e.g. place credit alerts, cancel credit cards,
update resume & post on monster.com, etc...) before they get to your data.
joe.
"Robert Robinson" <robbiex@.bellsouth.net> wrote in message
news:e9fdHUdDHHA.3660@.TK2MSFTNGP06.phx.gbl...
> We are trying to encrypt/decrypt a SQL Server 2005 database file.
> It is my understanding that you can encrypt the main database, but not its
> log file. The database file was successfully encrypted, but SQL Server
> failed to decrypt it on opening after a many minutes delay. The database
> was subsequently decrypted with a manual command, but the database had
> been damaged and couldn't be re-opened. It had to be deleted and restored.
> It appears that there is no practical way to use an encrypted SQL database
> because of apparent glitches and the extremely slow decryption process.
> We have considered backing up the database, encrypting the backup copy,
> deleting the database from the SQL directory on shutdown and then
> restoring it on startup. Another alternative is to store the data on
> removable media.
> I would greatly appreciate a suggestion as to how to best protect the
> data. We use SecuriKey to protect OS system startup. This works, but it
> doesn't protect the data if, for example, the hard drive is moved to
> another computer.
> I have read the following article:
> [url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/u
rl]
> Thank you very much.
> Robert Robinson|||Hi Joe,
Thank you very much for the reply. EFS is what we tried. There are two
unfortunate limitations. First, according to Microsoft, you cannot use
SQL if the log file is encrypted. Second, decrypt takes many minutes and
the long required time makes the technology impractical to use.
I agree that there is no absolute way to prevent access to data once an
expert has physical possession of a computer or a hard drive.
SecuriKey does work as advertised. There are ways to circumvent the
technology, but it provides some protection.
Robbie|||> Thank you very much for the reply. EFS is what we tried. There are two
> unfortunate limitations. First, according to Microsoft, you cannot use SQL
> if the log file is encrypted. Second, decrypt takes many minutes and the
> long required time makes the technology impractical to use.
> I agree that there is no absolute way to prevent access to data once an
> expert has physical possession of a computer or a hard drive.
> SecuriKey does work as advertised. There are ways to circumvent the
> technology, but it provides some protection.
Maybe you can encrypt just the snsitive part of the data? Try to look at the
EncryptByKey and other encryption functions in BOL. Together with carefully
set NTFS permissions and encrypted backup you might get what you need.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Hi Dejan,
Thank you very much for the suggestions.
Robbie
Dejan Sarka wrote:
> Maybe you can encrypt just the snsitive part of the data? Try to look at t
he
> EncryptByKey and other encryption functions in BOL. Together with carefull
y
> set NTFS permissions and encrypted backup you might get what you need.
>|||We decided on the following to provide a reasonable level of protection.
First, computer access is limited by using the SecuriKey.
SQL database files are protected as follows:
Setup
1. The SQL databases to be protected are backed up and are then deleted
from SQL Server.
2. PGP Desktop 9.5 is used to create a new Virtual Disk.
3. This disk is mounted.
4. A new SQL database is created with its data and log files assigned to
be resident on the virtual disk.
5. The data are restored from the backup file.
Start-Up
1. The virtual disk is mounted automatically on start-up or under manual
or programmatic control.
2. A PGP passphrase is entered manually.
3. The SQL database is attached.
Shut-Down
1. The SQL database is detached.
2. The virtual disk is unmounted under manual or programmatic control.
Note that the attach/detach steps are required because SQL Server locks
access to the Log files and the virtual disk cannot not be unmounted
until this lock is released.|||"Robert Robinson" <robbiex@.bellsouth.net> wrote in message
news:uibht4IEHHA.3600@.TK2MSFTNGP06.phx.gbl...
> We decided on the following to provide a reasonable level of protection.
> First, computer access is limited by using the SecuriKey.
> SQL database files are protected as follows:
> Setup
> 1. The SQL databases to be protected are backed up and are then deleted
> from SQL Server.
Are you concerned that fragments of unencrypted data might be lying around
on the storage device even after deletion? Just curious. Thanks.|||Hi Mike,
We are interested in providing a reasonable level of protection for
laptop data. The backup file is created on a server and doesn't have to
be installed on a laptop. The data can be transferred by LAN or
removable media. Your point is, however, well taken. There is no way to
absolutely delete data from a hard drive short of physical destruction
of the platters.
On a slightly different subject, we have run into some interesting
issues involved in using SQL Server data files that are resident in an
encrypted disk volume.
SQL Server locks a database's log file and it is not possible to unmount
a "secure" volume without first releasing this lock. The lock can be
released by an ALTER DATABASE <its name> SET OFFLINE followed by
sp_detach_db.
The database is attached by a SQL script as follows:
Use Master
GO
EXEC sp_attach_db @.dbname = N'database name',
@.filename1 = N'S:\SQLServerData\database name.mdf',
@.filename2 = N'S:\SQLServerData\database name_log.ldf'
GO
The script is executed by:
Shell("sqlcmd -i C:\AttachDetach.sql -U <owner name> -P
<password> -s <server name>")
One interesting glitch is that the above command fails if the owner
name/password precedes the command file.
Another issue is that one needs to know what is shutting down the
application program that is accessing the database. For example, it
might be a normal program exit, a logoff, a battery low warning, or a
system suspend or shutdown.
We had to do some hunting to find the appropriate events. The following
are helpful: Microsoft.Win32.SystemEvents.SessionEnding,
Microsoft.Win32.SystemEvents.PowerModeChanged
and an interesting control called sysinfo.ocx.
Robbie
