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--
USE master;
GO
SELECT *
FROM sys.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
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
go

Step4:- Create a self-signed certificate

This certificate will be protected by database master key.

CREATE CERTIFICATE Encert
WITH SUBJECT = 'Protect Data';

Step5:- Create a Symmetric key

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

GO
CREATE SYMMETRIC KEY EnKey
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE Encert;
GO


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

Adding this column to view encrypted data in table

Alter table 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

OPEN SYMMETRIC KEY EnKey
DECRYPTION BY CERTIFICATE Encert;
GO

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

CLOSE SYMMETRIC KEY EnKey;



Remove old columns

alter table  Cusomer_Credentials
drop column Creditcard_number


Step7:- Reading the encrypted data

OPEN SYMMETRIC KEY EnKey
DECRYPTION BY CERTIFICATE Encert;

     SELECT Encrypt_Creditcard_number,
     CONVERT(varchar, DecryptByKey(Encrypt_Creditcard_number)) as Encrypt_Creditcard_number
FROM Cusomer_Credentials  ORDER BY 1

CLOSE SYMMETRIC KEY EnKey;
GO



Step8:- Adding records to the table

OPEN SYMMETRIC KEY EnKey
DECRYPTION BY CERTIFICATE Encert;

INSERT INTO dbo.Cusomer_Credentials
VALUES (64124,'MS-BI Tutorails','569',EncryptByKey( Key_GUID('EnKey'), CONVERT(nvarchar,'411111111111119') ))

Step9:- Accessing encrypted data

SELECT
Encrypt_Creditcard_number,
CONVERT(varchar, DecryptByKey(Encrypt_Creditcard_number)) as Decrypted_Creditcard_number
FROM dbo.Cusomer_Credentials  

Step10:- assigning permissions to users

GRANT VIEW DEFINITION ON SYMMETRIC KEY:: EnKey TO test ;
GO
GRANT VIEW DEFINITION ON Certificate:: Encert TO test;
GO

Done