How to Create an SQL Database that Takes Weekly Backup and Encrypt/Decrypts Passwords?

SQL Server 2012, Database

Create a database that backup weekly and have size 100gb and create symmetric key and a column to encrypt name and decrypt so tasks involves encryption and decryption of data using password in SQL Server 2012.

Solution:

Step 1

Screenshot of SQL Server Management Studio

SQL Server 2012, Database

Step 2

Creating Database Master Key and certificate of the database.

CREATE

MASTER KEY

ENCRYPTION

BY PASSWORD = ‘pass123’

 

 

CREATE

CERTIFICATE CertificateName_HandsOnOne

WITH SUBJECT = ‘Subject of Certificate with Name CertificateName_HandsOnOne’

SQL Server 2012, Database

 

Step 3

Creating Symmetric Key

CREATE

SYMMETRIC KEY SymmetricKey_HandsOnOne

WITH

IDENTITY_VALUE = ‘IdentityValue_HandsOnOne’,

ALGORITHM = AES_256,

KEY_SOURCE = ‘KeySourse_HandsOnOne’

ENCRYPTION BY CERTIFICATE CertificateName_HandsOnOne;

SQL Server 2012, Database

Step4

Adding new Column to customer table

ALTER

TABLE TBL_Customer

ADD CustomerNameEncrypted varbinary(128)

SQL Server 2012, Database

Step5

Encrypting Data and saving in next column

 

OPEN

SYMMETRIC KEY SymmetricKey_HandsOnOne

DECRYPTION

BY CERTIFICATE CertificateName_HandsOnOne

 

UPDATE tbl_customer

SET

CustomerNameEncrypted = EncryptByKey(Key_GUID(‘SymmetricKey_HandsOnOne’), CustomerName)

SQL Server 2012, Database

Step6

Select Encrypted Data

SELECT CustomerNameEncrypted

 

FROM tbl_customer

SQL Server 2012, Database

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here