Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 5, 2007

SQL Server 2005 Encryption types

By Muthusamy Anantha Kumar aka The MAK

Encryption is the key for data security. Sensitive data such as Social Security numbers, credit card numbers, passwords, etc. should be protected from hacking.

In SQL Server 2000, you have to create your own user-defined functions to encrypt the data or use external DLLs to encrypt the data. In SQL Server 2005, these functions and methods are available by default.

SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.

  • ENCRYPTION by passphrase
  • ENCRYPTION by symmetric keys
  • ENCRYPTION by Asymmetric keys
  • ENCRYPTION by certificates

In part one of this series, I demonstrate how to use ENCRYPTION by password mechanism to encrypt the data and how to decrypt it.

SQL Server 2005 provides two functions regarding encryption: one for Encrypting and another for decrypting.

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.

Let us try to encrypt the data and decrypt it using the ENCRYPTION by passphrase mechanism.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now let us execute the above Encryptbypassphrase function three times as shown below.

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase
	(@password, @SocialSecurityNumber )
set @count=@count+1
end

Result

EncryptedData
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7
	CD598DC56364D106B05D47B930093

(1 row(s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877
	667BAF1DA9BE1E116434842D11B99

(1 row(s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A33841
	7DD685229173684D319334A084CD

Note:

Here “123456789” is the simulated data of a social security number and “MAK” is the password.

The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.

Now let us try to decrypt the above-encrypted data using the DecryptByPassPhrase function.

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

123456789

(1 row(s) affected)


123456789

(1 row(s) affected)


123456789

(1 row(s) affected)

Now let us try to decrypt the encrypted data using a different password. Execute the following command.

select convert(varchar(100),DecryptByPassPhrase('test'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

NULL

(1 row(s) affected)

As you can see, SQL Server generates NULL as the result when the password is wrong.

Now let’s create a table with a few rows of credit card numbers and social security number and then encrypt the data permanently with a passphrase.

USE [master]
GO
/****** Object:  Database [admin]    Script Date: 11/25/2007 10:50:47 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into  [Customer data] values (1, 1234567812345678, 123451234)
insert into  [Customer data] values (2, 1234567812345378, 323451234)
insert into  [Customer data] values (3, 1234567812335678, 133451234)
insert into  [Customer data] values (4, 1234567813345678, 123351234)
insert into  [Customer data] values (5, 1234563812345678, 123431234)
go

Now let us create two columns to hold the encrypted data.

use [Customer DB]
go
alter table [Customer Data] add 
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add 
[Encrypted Social Security Number] varbinary(MAX)
go

Let’s update the two columns with the encrypted data as shown below.

use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Query the table as shown below. [Refer Fig 1.0]

use [Customer DB]
go
select * from [customer data]
go

Result


Fig 1.0

Let’s remove the columns that have clear text data.

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number] 
go

Query the table as shown below. [Refer Fig 1.2]

use [Customer DB]
go
select * from [customer data]
go

Result


Fig 1.2

Let’s decrypt the data on the table using the decryptbypassphrase function as shown below. [Refer Fig 1.3]

use [Customer DB]
go
select 
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

Result

customer id,Credit Card Number,Social Security Number
1, 1234567812345678, 123451234
2, 1234567812345378, 323451234
3, 1234567812335678, 133451234
4, 1234567813345678, 123351234
5, 1234563812345678, 123431234


Fig 1.3

Conclusion

Encryption is the key for data security. Out of the four mechanisms of encryption, we have looked at the ENCRYPTION by passphrase and DECRYPTION by passphrase mechanism. In the next article of this series, we will discuss how to hack/de-cipher the encrypted data that has been encrypted by passphrase.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date