Data Encryption and Decryption in SQL Server 2008

 The strongly encrypted in the Filed use database table used to AES_256 Algorithm certificate " SYMMETRIC KEY".
 The AES_256 encryption standard , and in reviewing the encrypted result,
 to used  same value in different rows will have a different encrypted result.


Symmetric Key - Symmetric cryptography system in which the sender and receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy
to implement and the sender and receiver either can encrypt or decrypt the messages.

use northwind

SELECT * FROM sys.symmetric_keys

IF NOT EXISTS (

SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'

)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'

GO

SELECT * FROM sys.certificates

IF NOT EXISTS (

SELECT * FROM sys.certificates WHERE name = N'PasswordFieldCertificate'

)

CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';

GO

CREATE SYMMETRIC KEY PasswordFieldSymmetricKey

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE PasswordFieldCertificate

create table emp1(username varchar(20),passwor varbinary)

ALTER TABLE emp1

ADD EncryptedEmailAddress varbinary(256);

GO

select * from emp1

insert into emp1 values('mm',567657,5675)

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

UPDATE emp1  SET EncryptedEmailAddress = EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),EncryptedEmailAddress);

SELECT * FROM  emp1

GO

 

Browse More Articles

Tweet

Written by Test

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.