Tuesday, February 05, 2008

T-SQL 2005: Encrypting & Decrypting Data

-- Tested and compatible with SQL Server 2005 -- Encrypting a column using symmetric encryption: -- Check for a DB Master Key; it has an ID of 101; if not, create it: IF NOT EXISTS(select * from sys.symmetric_keys where symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SDF*(U$IOJ$#U*(FEIDO'; -- Create a certificate used to encrypt the key: IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'TestCert') CREATE CERTIFICATE TestCert WITH SUBJECT = 'Test Certificate'; -- Create a key: IF NOT EXISTS(select * from sys.symmetric_keys where name = 'TestKey') CREATE SYMMETRIC KEY TestKey WITH ALGORITHM = DES ENCRYPTION BY CERTIFICATE TestCert; -- Open the symmetric key: OPEN SYMMETRIC KEY TestKey DECRYPTION BY CERTIFICATE TestCert; -- Encrypt data DECLARE @EncryptedData VARBINARY(255), @DecryptedData VARCHAR(255); SET @EncryptedData = EncryptByKey(Key_GUID('TestKey'), 'My encypted data!'); SELECT @EncryptedData AS EncryptedData; -- Decrypt encrypted data: SELECT CAST(DecryptByKey(@EncryptedData) AS VARCHAR) AS DecryptedData;


arachnode.net said...

Hello there!

Darrick said...

This is what I'm using to encrypt, but now I see a need to encrypt on the client, send to SQL server and also be able to decrypt/encrypt there. What's the best way to achieve both SQL encryption and client-to-SQL encryption for the same data element?