First off, if you’ve got SQL Server 2008 Enterprise Edition I’d recommend that you look at TDE. SQL Server 2008 Express Edition doesn’t support TDE so instead we need to find another way to encrypt our data. The method that I’m talking about here is asymmetric encryption using a password (instead of certificate). I’m just going to drop the code below any maybe elaborate at a later date.
Found a slight problem, my original code worked great in the Management Studio but failed on the web with a
Cannot insert NULL message. The problem was that the user that the user account listed in my web.config didn’t have permission to access the private key. The solution was simple, just
CONTROL permission on they key. The code below reflects this. I’m not 100% sure that there isn’t a permission with less privileges than
CONTROL but it does work at least.
--Create a test database CREATE DATABASE [Test] GO --Select the test database USE [Test] GO --Create the database master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'really awesome password here' GO --Create a single asymmetric key, this password is one that we're going to be using later actually CREATE ASYMMETRIC KEY KEY_SuperAwesomeKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = N'some other really awesome password' GO --Give the user account "YourUserAccountHere" permission to use the key GRANT CONTROL ON ASYMMETRIC KEY::[KEY_SuperAwesomeKey] TO YourUserAccountHere --Create a test table. --Very important, the encryption and decryption functions max out at 8000 bytes CREATE TABLE SampleTable ( PlainText varChar(8000), EncText varBinary(8000) ) GO DECLARE @PlainText varChar(8000) DECLARE @EncText varBinary(8000) --Plain text variable SET @PlainText = 'Hello world' --EncryptByAsymKey(KeyId, PlainText), use AsymKey_Id(KeyName) to find the Id of the key SET @EncText = ENCRYPTBYASYMKEY(ASYMKEY_ID('KEY_SuperAwesomeKey'), @PlainText) --Insert into our table INSERT INTO SampleTable (PlainText, EncText) VALUES (@PlainText, @EncText) --Show what the encrypted form looks like SELECT * FROM SampleTable --DecryptByAsymKey(KeyId, EncryptedText, AsymetricKeyPassword), returns byte array that needs to be re-cast to original type --Also, very important, the password MUST be passed in as nvarchar SELECT *, CONVERT(varchar, DECRYPTBYASYMKEY(ASYMKEY_ID('KEY_SuperAwesomeKey'), EncText, N'some other really awesome password')) FROM SampleTable