SQL based encryption performance

Question

I am doing some research for a future project and it requires some data to be encrypted.

In my research today, I have noticed that there are many different encryption algorithms (including AES, Triple-DES, X-DES etc) and I wish to use one of the algorithms in SQL-Server (2008r2 express probs), how would you recommend doing this, like best practices etc?

I am planning to do something like the following:

-- Create the master key. 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Som3Rand0m!3y?na'
-- Create the cert.
CREATE CERTIFICATE someCert WITH SUBJECT = 'c3p009xFR?'
-- Create Symmetric Key
CREATE SYMMETRIC KEY someSymmetricKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE someCert

And use it in the following way:

declare @sql varchar(8000)
set @sql = 'OPEN SYMMETRIC KEY someSymmetricKey DECRYPTION BY CERTIFICATE someCert '
exec (@sql)

-- Check the table
SELECT      col1, Convert(varchar(max), DECRYPTBYKEY(col2)) as col2
FROM        myTable

-- dont forget to close the symmetric key again afterwards.
CLOSE SYMMETRIC KEY someSymmetricKey

I guess this is the correct way to do it (I read up on it ages ago and saved an example, so just double checking :])?

Also, are there really any performance differences (or security differences, like one is easier to break than another) between using the different encryption algorithms? <<- this is the one i was really trying to find out about/most important.

Lastly, if I move the database to a different server, I guess I can just re-create the key/cert in the same way and I will be able to get the data back etc?

Any good blog posts/links/whitepapers would be greatly appreciated :) (most that ive read have been quiet bad tbh)

Thanks for reading :D

score 2 · Accepted Answer · answered Mar 21 '11 at 22:51

2

You have only two options: AES and 3DES. Simple DES and XDES are too weak (56 and 112 bit strength respectively). RC4 is not an option because the SQL Server implementaion is busted (does no properly salt encrypted values).

3DES is clinging to the past. Use AES, is the current NIST recommended algorithm and offers you a decent speed.

answered Mar 21 '11 at 22:51

Remus Rusanu

288,378
40
442
569

What sort of over heads are we talking about when you say decent speed? Thanks for the info so far. – Stuart Blackler Mar 22 '11 at 17:14
You will need to measure yourself for your case. In all the implementation I've seen the encryption overhead was basically undetectebale against the overwhelming IO cost. – Remus Rusanu Mar 22 '11 at 17:33

score 1 · Answer 2 · answered Mar 21 '11 at 23:06

For your choice of encryption algorithm, there isn't really a one size fits all choice (see the link below for advice from Microsoft about the algorithms available in SQL Server).

http://msdn.microsoft.com/en-us/library/ms345262.aspx

As for backing up and restoring your encryption keys:

--Backup the master key   
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Som3Rand0m!3y?na';
BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey' 
ENCRYPTION BY PASSWORD = 's0me0th3rp4$$w0rd';
GO 
--Back up the certificate
BACKUP CERTIFICATE someCert TO FILE = 'c:\temp\someCert.cer'
GO

Then, on your other server

RESTORE MASTER KEY FROM FILE = 'c:\temp\exportedmasterkey' 
    DECRYPTION BY PASSWORD = 's0me0th3rp4$$w0rd'
    ENCRYPTION BY PASSWORD = 'Som3Rand0m!3y?na'
GO

CREATE CERTIFICATE someCert
    FROM FILE = 'c:\temp\someCert.cer'
GO

SQL based encryption performance

2 Answers2

Linked