Ashish's Blog On Sql Server

Encryption & Decryption in SQL SERVER 2005

Hi all,

Here is a very powerful feature of SQL Server 2005 is Data Encryption & Decryption, in Sql Server 2000 if we like to Encrypt Data then we have to write our own functions to encrypt the data. In SQL Server 2005 and SQL Server 2008, these  functions are available by default.

SQL Server provides the following method to encrypt your data….

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric key
• ENCRYPTION by asymmetric key
• ENCRYPTION by certificate

So here we try to encrypt the data by using Symmetric keys.

So in this we firstly create a database and create master key ,certificate ,symmetric key table and do encryption so here we go…

Create Database ..

USE MASTER

GO

-- First Create a DATABASE

CREATE DATABASE ENCRYPTDECRYPTTEST ON PRIMARY

(NAME=N'ENCRYPTDECRYPTTEST' ,FILENAME=N'D:\ENCRYPTDECRYPTTEST.mdf')

LOG ON 

(NAME=N'ENCRYPTDECRYPTTEST_LOG' ,FILENAME=N'D:\ENCRYPTDECRYPTTEST.ldf')

Use database and Create master key, certificate and symmetric key

-- Use database

USE ENCRYPTDECRYPTTEST

GO

-- Create Master Key

CREATE MASTER KEY ENCRYPTION BY 

    PASSWORD ='ASHISH'

GO

-- Create Certificate

CREATE CERTIFICATE ENCDECTEST 

    WITH SUBJECT='ASHISH'

GO

-- Check it

SELECT * FROM SYS.CERTIFICATES

-- Create Symmetric Key

CREATE SYMMETRIC KEY TESTKEY

    WITH ALGORITHM = TRIPLE_DES

    ENCRYPTION BY CERTIFICATE ENCDECTEST

GO 

In the above create symmetric key T-Sql statement the algorithm parameter is the most important part…

SQL SERVER can use the following algorithms in encryption:

DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 AND AES_256.

Now create a table …

-- Create a Sample Table

CREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),UPASSWORD VARBINARY(256))

So now its turn to insert data in USERS table and we encrypt the UPASSWORD field using our symmetric key this can be done by a Store Procedure so we create a Insert Procedure for this.. so here is the procedure….

-- Create a Procedure to Insert Data in Table

CREATE PROC [InsertUSER]

  @UFNAME VARCHAR(100),

  @ULNAME VARCHAR(100),

  @ULOGINDI VARCHAR(12),

  @UPASSWORD VARCHAR(20)

AS

BEGIN

-- you must open the key as it is not already

    OPEN SYMMETRIC KEY TESTKEY 

        DECRYPTION BY CERTIFICATE ENCDECTEST;

-- Insert statement

    INSERT INTO [USERS] 

    (UFNAME, ULNAME, ULOGINDI, UPASSWORD) 

    VALUES 

    (@UFNAME, @ULNAME, @ULOGINDI, 

     EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));

END;

So after creating procedure you can run these procedures by following parameters and check the data in table…

-- Run Insert Procedure

EXEC INSERTUSER 'ASHISH','GILHOTRA','ASHISH','MYPASSWORD'

-- Check the Data

SELECT * FROM USERS

and the results will be like this…

Now we create a Store Procedure to extract the decrypted data so here we go and create a store procedure to extract data in Decrypted form…

and run that procedure…

-- Procedure to Retrive Users

CREATE PROC [RetrieveUSER]

    @UID INT

AS

BEGIN

-- you must open the key as it is not already

    OPEN SYMMETRIC KEY TESTKEY 

        DECRYPTION BY CERTIFICATE ENCDECTEST;

-- Select statement

  SELECT UID, UFNAME, ULNAME, ULOGINDI, CONVERT(VARCHAR(20),DECRYPTBYKEY(UPASSWORD)) AS PASSWORD

  FROM USERS

  WHERE UID = @UID;

END;

-- Execute Retrive Procedure

EXEC RetrieveUSER 100

and the results will be like this….

So in this way you can protect your sensitive data like passwords, credit cars numbers and many more,,,,
there also some others way to do encryption in SQL Server 2005 and SQL Server 2008 .

Thanks & Happy Querying…