SQL Server 2005 Encryption types

December 5, 2007

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

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

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

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