Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This Blog is About SQL Server Technolgy
Browse by Tags · View All
BRH 12
#WHITEPAPERS 10
whitepapers 10
webcast 10
Training 9
MSBI 9
BI 8
SQL-Server 8
SSAS 7
T-SQL 7

Archive · View All
August 2010 9
July 2010 7
May 2009 6
September 2010 4
June 2009 4
July 2009 3
October 2010 2
April 2010 2
March 2010 1

Ashish's Blog On SQL Server

Encryption & Decryption in SQL SERVER 2005

May 30 2009 8:44AM by Ashish Gilhotra   

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…

ENCDEC1

 

 

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….

 

encdec2

 

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…

Tags: SQLSERVER-2005, Decryption, Encryption,


Ashish Gilhotra
31 · 6% · 1776
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • Hello Ashish, Is there an easier way to encrypt all the columns of a tables with out mentioning eash and evey column seperately with the encryptby function? Please let us know.. if any.

    Thanks in advance.

    commented on Aug 5 2010 6:47PM
    bkanneganti
    3079 · 0% · 2
  • Why do you need it? It creates big overhead on server to decrypt data while showing it and encrypt all data before any insert/update etc. it creates an issue if you take backup of your database and directly restore it somewhere else. to know more about this issue and other feature, do read following article:

    http://www.sqlhub.com/2009/10/backup-database-with-encrypted-data-and.html

    if you have any other query, concern, do ask without hesitation......

    commented on Jun 30 2011 6:09AM
    Ritesh Shah
    75 · 2% · 747
  • Hi Ashish,

    It was a wonderfull blog on encription and depription with symetric and by cerificate. Can we have more ENCRYPTION by passphrase ENCRYPTION by asymmetric key .

    Thank You! Pushkar

    commented on Nov 23 2011 11:53PM
    Pushkar
    2770 · 0% · 4
  • Ashish ,

    Please provide theorytical description with codes as well. That makes more easy to understand the concept.

    Thank you!

    commented on Nov 24 2011 12:04AM
    Pushkar
    2770 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]