There are two kinds of keys used in encryption

Symmetric key encryptionIn symmetric-key schemes,the encryption and decryption keys are the same. Communicating parties must have the same key before they can achieve secure communication.Public key encryptionIllustration of how encryption is used within servers Public key encryption.In public-key encryption schemes, the encryption key is published for anyone to use and encrypt messages. However, only the receiving party has access to the decryption key that enables messages to be read.

There are two different kinds of encryptions available in SQL Server

Database Level – This level secures all the data in a database. Performance is bad is not and not a practical solution.Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted

/*** Create Master key **/

USE TESTDB

GO

CREATEMASTERKEYENCRYPTION

BYPASSWORD='AmlMigration098'

GO

/** Create encryption certificate **/

USETESTDB

GO

CREATECERTIFICATE TestCertificate

WITHSUBJECT='AmlMigration098'

GO

/** Create Symmetric Key */

USETESTDB

GO

CREATESYMMETRICKEY TestKey

WITHALGORITHM=TRIPLE_DESENCRYPTION

BYCERTIFICATE

TestCertificate

GO

/* Update binary column with encrypted data
created by certificate and key */