Encryption in SQL Server 2005

MAK

Encryption is an important method of data security. Sensitive data such as CMT numbers, credit card numbers, passwords . need to be protected against numerous current threats. In SQL Server 2000 you can create your own functions or use external DLLs to encrypt data. In SQL Server 2005, these functions and methods are allowed by default.

SQL Server 2005 provides the following techniques for data encryption

1. Encrypt with password
2. Encrypt symmetric keys
3. Asymmetric key encryption
4. Encryption certificate

In the first part of this series, we will explain how to use password encryption and how to decode it.

SQL Server 2005 provides two functions for encryption: one for encryption and one for encryption.

'Password encryption' is a method of encrypting basic data via a password. Data can be decoded if the correct password is used when encrypting. We will try an example of encrypting and decrypting data using password encryption technology.

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

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now we will execute the Encryptbypassphrase function three times on the following example

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
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093

(1 row (s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99

(1 row (s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD

Note :
'123456789' here may be credit card number and 'MAK' is the password

The results of the Encryptbypassphrase after each function execution are different. However, when you decrypt the data, it still produces the original result before encoding.

Now we will try to decrypt the above encrypted data with 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)

Try decrypting the encrypted data with another password. Execute according to the following statement

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

Result

NULL

(1 row (s) affected)

The result shows you that SQL Server returns NULL if the password is wrong.

Now we will try to create a table containing credit card numbers and CMT numbers, then encrypt this data via password encryption.

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 vào [Customer data] values ​​(3, 1234567812335678, 133451234)
insert into [Customer data] values ​​(4, 1234567813345678, 123351234)
insert into [Customer data] values ​​(5, 1234563812345678, 123431234)
go

Create two columns to save 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

Update the encrypted data into the two columns you just created
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

Trace the table with the following commands (Figure 1)

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

Result

Figure 1

Delete columns that contain unencrypted data

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] đã thoát cột [Social Security Number]
go

Query the table according to the following commands (Figure 2)

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

Result

Figure 2

Decrypt the data on the table via the Decryptbypassphrase function as follows (Figure 3)

use [Customer DB]
go
select
[customer id],
convert (bigint, convert (varchar (100), decryptbypassphrase ('Credit Card', [Encrypted Credit Card Number])))
[Credit Card Number],
convert (bigint, convert (varchar (100), decryptbypassphrase ('Social Security', [Encrypted Social Security Number])))
[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

Figure 3

Conclude

Data encryption is really important. In this article, I have introduced you to one of the four encryption techniques available in SQL Server 2005 - password encryption technology - and how to decode it. In the following article, we will discuss the method of hacking / recovering data encrypted with this password.