Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sample SQL 2005 Encryption Code

Encryption

Sample SQL 2005 Encryption Code

by  mrdenny  Posted    (Edited  )
This is based off of the CTP edition of SQL 2005.
This sample code will create a encryption key, open the key, and use the key to encrypt some data, then decrypt the data, then close the key.
Code: 
OPEN SYMMETRIC KEY PrivateData DECRYPTION  BY PASSWORD ='Password'

CREATE TABLE UserData
(Data VARBINARY(255))

DECLARE @Key_GUID UNIQUEIDENTIFIER

SELECT @Key_GUID = Key_GUID
FROM sys.symmetric_keys
WHERE Name = 'PrivateData'
INSERT INTO UserData
(Data)
VALUES
(EncryptByKey(@Key_Guid, 'RawData', 1))

SELECT Data
FROM UserData

SELECT CONVERT(VARCHAR(20), DecryptByKey(UserData.Data, 1)) AS Data
FROM UserData

DROP TABLE UserData

CLOSE SYMMETRIC KEY PrivateData
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top