Pages

Wednesday, February 17, 2016

Encrypting column data in sql server

Sometimes we need to store sensitive information like User passwords, address, and credit card details In such situation we use data encryption cell level, here I will elaborate how to encrypt data and how to decrypt and insert with examples. Sql server has an encryption hierarchy in order to encrypt the data.

Step 1:- Create a table to perform encryption operation

USE Encrypt_DB;

GO

-- Create Table

CREATE TABLE dbo.Cusomer_Credentials

(

Customer_id int constraint Pkey3 Primary Key NOT NULL,

Customer_Name varchar(100) NOT NULL,

Creditcard_number varchar(25) NOT NULL

CVV varchar(25) NOT NULL

)

-- insert data in table

INSERT INTO dbo.Cusomer_Credentials

VALUES (64112,'MS-BI Tutorails','411111111111111','562')

GO

INSERT INTO dbo.Cusomer_Credentials

VALUES (64113,'MS-BI Tutorails','411111111111112','563')

GO

INSERT INTO dbo.Cusomer_Credentials

VALUES (64114,'MS-BI Tutorails','411111111111113','564')

GO

INSERT INTO dbo.Cusomer_Credentials

VALUES (64115,'MS-BI Tutorails','411111111111114','565')

GO

-- Check the data in table

SELECT *

FROM dbo.Cusomer_Credentials

GO

Step2:- Check whether already master key available or not.

Master key is the root for the encryption hierarchy. It is created by sql server default, if not present

Create new key.

---Creation of master Key--

USEmaster;

GO

SELECT*

FROMsys.symmetric_keys

WHERE name ='##MS_ServiceMasterKey##';

GO

Step3:- Create database master key with the password

This key used to encrypt the certificate with password

use Encrypt_DB

go

CREATEMASTERKEYENCRYPTIONBYPASSWORD='Password123';

go

Step4:- Create a self-signed certificate

This certificate will be protected by database master key.

CREATECERTIFICATE Encert

WITHSUBJECT='Protect Data';

Step5:- Create a Symmetric key

Symmetric key is used to encrypt and decrypt the data in table

GO

CREATESYMMETRICKEY EnKey

WITHALGORITHM=AES_128

ENCRYPTIONBYCERTIFICATE Encert;

GO

Step5:- Now add the column (or) change schema

Adding this column to view encrypted data in table

Altertable Cusomer_Credentials

add Encrypt_Creditcard_number varbinary(max)null

Step6:-Encrypt the newly created the column

Before the encrypting the column open the certificate which you created earlier

OPENSYMMETRICKEY EnKey

DECRYPTIONBYCERTIFICATE Encert;

GO

UPDATE Cusomer_Credentials

SET Encrypt_Creditcard_number =EncryptByKey(Key_GUID('EnKey'),[Creditcard_number])