SQL Server 2005 encryption with databound controls

[Finn Stampe Mikkelsen]

Hi

I'm looking for ways to encrypt data in my MDF database (SQL Express) and i
wonder if anybody had some examples on how to use it, especially in
conjunction with databound controls using dataset's..

TIA

/Finn
--
Der er 10 slags mennesker - Dem som forstår binær og dem som ikke gør.
There are 10 kinds of people. Those who understand binary and those who
don't.
Es gibt 10 Arten von Menschen. Die, die Binär verstehen, bzw. die, die es
nicht tuhen.

 

[Alberto Poblacion]

I'm looking for ways to encrypt data in my MDF database (SQL Express) and
i wonder if anybody had some examples on how to use it, especially in
conjunction with databound controls using dataset's..

When you say "databound controls using datasets", I presume that the
controls are bound to a dataset and that you transfer the contents of the
dataset from/to the database using a DataAdapter. If you want the contents
of the data encrypted inside the database (but not inside the dataset or
when travelling from the client to the database), then you can encapsulate
all the encryption and decryption inside stored procedures, and you can
configure the DataAdapter to use those SPs, rather than the sql queries
themselves.
Inside the SPs, you would pass your data through the standard SQL Server
2005 functions for encryption and decryption (EncryptByKey and DecryptByKey)
before writing or reading the actual table.

If you were using Sql Server 2008 I would suggest the much simpler
avenue of using Transparent Data Encryption, but unfortunately this is not
available in the Express version.

 

[Finn Stampe Mikkelsen]

When you say "databound controls using datasets", I presume that the
controls are bound to a dataset and that you transfer the contents of the
dataset from/to the database using a DataAdapter. If you want the contents
of the data encrypted inside the database (but not inside the dataset or
when travelling from the client to the database), then you can encapsulate
all the encryption and decryption inside stored procedures, and you can
configure the DataAdapter to use those SPs, rather than the sql queries
themselves.
Inside the SPs, you would pass your data through the standard SQL
Server 2005 functions for encryption and decryption (EncryptByKey and
DecryptByKey) before writing or reading the actual table.

If you were using Sql Server 2008 I would suggest the much simpler
avenue of using Transparent Data Encryption, but unfortunately this is not
available in the Express version.

Hi

You are quite correct in your assumption. I cannot use SQL 2008, so that is
not an option.

Your solution using SP, would that not leave the data vulnerable?? I mean
the SP would not itself be encrypted and would the possible hacker of the
MDF file be able to decrypt the data by just using this SP??

Pls. excuse me, cause i'm not familiar with the server functions you
mentioned, so i'm not sure how they are used. A code example would really
help me see the light, so to speak.. ;-))

/Finn
--
Der er 10 slags mennesker - Dem som forstår binær og dem som ikke gør.
There are 10 kinds of people. Those who understand binary and those who
don't.
Es gibt 10 Arten von Menschen. Die, die Binär verstehen, bzw. die, die es
nicht tuhen.

 

[Alberto Poblacion]

Your solution using SP, would that not leave the data vulnerable?? I mean
the SP would not itself be encrypted and would the possible hacker of the
MDF file be able to decrypt the data by just using this SP??

No, the actual process is more complicated and requires to know the
cryptography structure in Sql Server. The process is analogous to the
following, but don't take my word as to the accuracy of every single minute
detail: When installing the server, a Server Master Key is automatically
generated and then protected in Windows using the DPAPI. This key is then
used to protect a Database Master Key (which you have to generate manually,
it does not exist by default in new databases). If someone were to grab a
copy of your .mdf file, they would not be able to use the Database Master
Key because it is encrypted with the Server Master Key.
Inside your database, you create one ore more Symmetric or Asymmetric
keys to protect your data. Typically, you would use Symmetric encryption for
the data, because it is faster. You would protect the symmetric key by
encrypting it with an asymmetric key (or a certificate), and the asymmetric
key or certificate would be protected by the database master key. Therefore,
all of your keys are protected, and cannot be retrieved from a copy of the
mdf.

Pls. excuse me, cause i'm not familiar with the server functions you
mentioned, so i'm not sure how they are used. A code example would really
help me see the light, so to speak.. ;-))

This is a sample of the kinds of things that you can do:

--Create the database master key (only needed once)
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'thePassword'

-- Create a certificate (only once)
CREATE CERTIFICATE MyCert
AUTHORIZATION NameOfUser
WITH SUBJECT = 'Name of certificate'
GO

-- Create a symmetric key (only once)
CREATE SYMMETRIC KEY MyKey
AUTHORIZATION NameOfUser
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert
GO

-- Insert encrypted data
-- You would encapsulate this inside a SP
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
INSERT INTO MyTable VALUES
(somevalues, EncryptByKey(Key_GUID('MyKey'),'Some Data'),
someothervalues)
CLOSE ALL SYMMETRIC KEYS

-- Read and decrypt data
-- You would encapsulate this inside a SP
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
SELECT CONVERT(varchar,DecryptByKey(theColumn)) FROM MyTable
CLOSE ALL SYMMETRIC KEYS