Encryption in SQL Server 2005

The sections below explain encryption in SQL server 2005 in a clear and practical way. Review the key details, examples, and considerations before applying the information.

MAK

How Encryption in SQL Server 2005 Works

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

• Encrypt with password
• Encrypt symmetric keys
• Asymmetric key encryption
• 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

EncryptedData0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

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

Declare @count intDeclare @SocialSecurityNumber varchar (500)Declare @password varchar (12)Set @count = 1While @count <= 3BeginSet @SocialSecurityNumber = '123456789'Set @Password = 'MAK'Select EncryptedData = EncryptByPassPhrase (@password, @SocialSecurityNumber)Set @ count = @ count + 1End

Result

EncryptedData0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093(1 row (s) affected)EncryptedData0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99(1 row (s) affected)EncryptedData0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD

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]goCreate database [Customer DB]goUse [Customer DB]goCreate table [Customer data]([customer id] int,[Credit Card Number] bigint,[Social Security Number] bigint)goInsert 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]goAlter table [Customer Data] add[Encrypted Credit Card Number] varbinary (MAX)goAlter table [Customer Data] add[Encrypted Social Security Number] varbinary (MAX)goUpdate the encrypted data into the two columns you just createdUse [Customer DB]goUpdate [Customer Data] set [Encrypted Credit Card Number] =EncryptByPassPhrase ('Credit Card', convert (varchar (100), [Credit Card Number]))goUpdate [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]goSelect * from [customer data]go

Result

Figure 1

Delete columns that contain unencrypted data

Use [Customer DB]goAlter table [Customer Data] drop column [Credit Card Number]goAlter table [Customer Data] ?ã thoát c?t [Social Security Number]go

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

Use [Customer DB]goSelect * from [customer data]go

Result

Figure 2

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

Use [Customer DB]goSelect[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 Number1, 1234567812345678, 1234512342, 1234567812345378, 3234512343, 1234567812335678, 1334512344, 1234567813345678, 1233512345, 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.