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;
2 comments:
Hello there!
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?
Post a Comment