Encrypting/Decrypting data in SQL Server

[Seth Schrock]

I'm trying to create an encryption/decryption system in SQL server that makes it so that my MS Access projects don't have to know that it is encrypted. I don't have the Enterprise edition of SQL Server, so I can't use TDE. My production server is running SQL Server 2005, but my test system is running SQL Server 2012 and we will (hopefully) soon be upgrading our production server to 2012 Standard edition in about a year. The encryption that I'm needing is only the data at rest, not the data going over the network.

Anyway, here is what I'm attempting to do. I want a View that shows the decrypted information from the table that only stores the encrypted data. The View would then have Update and Insert triggers that would encrypt the necessary fields as they are changed/added.

What I have so far... I have created a testing database that I plan on filling with junk data to test my design. So I have the table Encrypted:

ID, int PK
OpenString, varchar(50)
OpenNumber, smallint
eCustFN, varbinary(max)
eCustLN, varbinary(max)
 

The only fields that I'm going encrypt is the eCustFN and eCustLN. I also have two UDFs. One to encrypt and one to decrypt the data.

CREATE FUNCTION [dbo].[DecryptMax](@value varbinary(max))
RETURNS varchar(max)
AS
BEGIN
    DECLARE @PlainText varchar(max);
 
    SELECT @PlainText = CONVERT(VARCHAR(MAX), DECRYPTBYKEY(@value));
    RETURN @PlainText;
END
 
 
CREATE FUNCTION [dbo].[EncryptMax](@value varchar(max))
RETURNS varbinary(max)
AS
BEGIN
    DECLARE @EncryptedText varbinary(max);
 
    SELECT @EncryptedText = ENCRYPTBYKEY(Key_GUID('PasswordFieldSymmetricKey'), @Value);
    RETURN @EncryptedText;
END
GO

I then have a View that pulls the ID, OpenString and OpenNumber fields as well as using the the DecryptMax function on the eCustFN and eCustLN fields to display the data decrypted. This view has two triggers connected to it on the Insert and Update events that use the EncryptMax UDF to encrypt the data back into the table.

My problem is that I need to be able to open symmetric key for the encryption process to work.

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

I can't put this in my UDFs because they aren't allowed. I thought that I could trick it by creating a stored proc that would open it up, but that didn't work either. I read something online about using a stored proc that returned a value instead of the UDFs, but you can't run a store proc inside a query like you can with a function, so I'm not sure how to do that either. I don't have a lot of experience doing this type of stuff in SQL Server so I'm not sure what to even search for online.