SQL Server 2005 Encryption types

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles