By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,728 Members | 1,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,728 IT Pros & Developers. It's quick & easy.

Encrypting/Decrypting data in SQL Server

Seth Schrock
Expert 2.5K+
P: 2,951
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:
Expand|Select|Wrap|Line Numbers
  1. ID, int PK
  2. OpenString, varchar(50)
  3. OpenNumber, smallint
  4. eCustFN, varbinary(max)
  5. eCustLN, varbinary(max)
  6.  
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.
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION [dbo].[DecryptMax](@value varbinary(max))
  2. RETURNS varchar(max)
  3. AS
  4. BEGIN
  5.     DECLARE @PlainText varchar(max);
  6.  
  7.     SELECT @PlainText = CONVERT(VARCHAR(MAX), DECRYPTBYKEY(@value));
  8.     RETURN @PlainText;
  9. END
  10.  
  11.  
  12. CREATE FUNCTION [dbo].[EncryptMax](@value varchar(max))
  13. RETURNS varbinary(max)
  14. AS
  15. BEGIN
  16.     DECLARE @EncryptedText varbinary(max);
  17.  
  18.     SELECT @EncryptedText = ENCRYPTBYKEY(Key_GUID('PasswordFieldSymmetricKey'), @Value);
  19.     RETURN @EncryptedText;
  20. END
  21. 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.
Expand|Select|Wrap|Line Numbers
  1. OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
  2. 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.
Dec 3 '13 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.