Database and columns used as you have your sample data
------- Step 1 View existing keys only one key for one database instance
SELECT * FROM sys.symmetric_keys
/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '\@awe!E$w#$128as'
GO
-- DROP MASTER KEY
------- Step 2 View existing certificates
SELECT * FROM sys.certificates
/*************** CREATE CERTIFICATE *********************************/
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = N'SSNCertificate')
CREATE CERTIFICATE SSNCertificate WITH SUBJECT = 'SSN Fields';
GO
-- DROP CERTIFICATE SSNCertificate
Microsoft SQL Server can use the following algorithms in encryption sensitive data. DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 and AES_256 As system administrators or database admins, one important note for AES_128, AES_192 and AES_256 is AES encryption algorithm can not be used on Microsoft Windows 2000 Servers and Windows XP operating systems.
If you have a MS SQL Server instance running on a Win2k server, then it is better to create the symmetric keys using the TRIPLE_DES algorithm, for instance. Otherwise, your script will fail when it is run on a sql server which is installed on Windows 2000 servers and Windows XP computers since AES is not supported on those operating systems.
You should consider this point while choosing an encryption algorithm for your SQL Server database applications.
*/
/*************** CREATE SYMMETRIC KEY *********************************/
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SSNFieldSymmetricKey')
CREATE SYMMETRIC KEY SSNFieldSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE SSNCertificate;
-- DROP SYMMETRIC KEY SSNFieldSymmetricKey
/*********** Preparing Database Tables to Store Encrypted Data **********/
ALTER TABLE Registration
ADD vbinEncryptedSSN varbinary(256);
GO
Select vchSocialSecurityNumber,vbinEncryptedSSN from TE_Registration
An encryption can be done on a string or binary value (in nvarchar, varchar, varbinary, nchar, char, binary sql data types) in SQL Server using the EncryptByKey t-sql function.EncryptByKey encrypts a given data by using a symmetric key.The necessary symmetric key information can be passed to the EncryptByKey function using the Key_GUID Transact-SQL function. Key_GUID returns the uniqueidentifier (GUID) of the symmetric key whose key name is specified in the Key_GUID function.
The output of the encryption function EncryptByKey is a varbinary with a maximum lenth 8000 bytes. Since EncryptByKey t-sql command requires a symmetric key, if you execute the EncryptByKey command without openning the symmetric key, the EncryptByKey function will return NULL values during the encrypting calls.
/*************** ENCRYPT SENSITIVE DATA *********************************/
OPEN SYMMETRIC KEY SSNFieldSymmetricKey
DECRYPTION BY CERTIFICATE SSNCertificate;
UPDATE TE_Registration SET vbinEncryptedSSN = EncryptByKey(Key_GUID('SSNFieldSymmetricKey'), vchSocialSecurityNumber);
Select vchSocialSecurityNumber,vbinEncryptedSSN from TE_Registration
/*************** DECRYPT SENSITIVE DATA *********************************/
OPEN SYMMETRIC KEY SSNFieldSymmetricKey
DECRYPTION BY CERTIFICATE SSNCertificate;
/*************** DECRYPT *********************************/
Select vchSocialSecurityNumber,vbinEncryptedSSN,
CONVERT(nvarchar, DecryptByKey(vbinEncryptedSSN)) AS 'Decrypted SSN', -- comparing with orignal data type
CONVERT(varchar, DecryptByKey(vbinEncryptedSSN)) AS 'Decrypted SSN'-- comparing with different data type
from TE_Registration
GO
We have to remember one more feature that we must convert decrypted data using same database as we use during or before encryption. As we see in above example we might found different result for nvarchar and varchar conversion.
for more detail please visit:http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx
