SQL Server 2005 - Hack Encrypted Data by Password

Understanding SQL server 2005 - hack encrypted data by password is easier when the main points are organized clearly. This guide brings together the essential details and practical considerations.

InPart 1Of this series, we introduced a password encryption and decoding method.This part 2 will go into how to hack that data back. As you know, password encryption is a basic data encryption method that only uses passwords and can decrypt with the same password. Now let's suppose we forgot the password we set and need to restore the data as it was. Step 1 Encrypt data by password encryption method

How SQL Server 2005 - Hack Encrypted Data by Password Works

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

Result

EncryptedData-------------------------------------------------- ------------------------0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC

Step 2 Create user procedures to restore chained data. This procedure will use the DecryptByPassPhrase function to decrypt the data and display it on the password.

USE [Master]GO/ ****** Object: StoredProcedure [dbo]. [Hack_encryption] Script Date: 12/18/2007 18:18:36 ****** /IF EXISTS (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (N '[dbo]. [Hack_encryption]')AND type in (N'P ', N'PC'))DROP PROCEDURE [dbo]. [Hack_encryption]GO??t nocount trênSET CONCAT_NULL_YIELDS_NULL OFFgoUSE [Master]GO/ ****** Object: StoredProcedure [dbo]. [Hack_encryption] Script Date: 12/18/2007 18:18:55 ****** /SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo]. [Hack_encryption] @encryptedtext varbinary (max)asDeclare @password varchar (6)Declare @i intDeclare @j intDeclare @k intDeclare @l intDeclare @m intDeclare @n intSet @ i = -1Set @ j = -1Set @ k = -1Set @ l = -1Set @ m = -1Set @ n = -1Set @password = ''While @i

Step 3 Assume that you forgot the password used to encrypt the data to ' 0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC '. We can retrieve the password and encrypted data using the following procedure

Use mastergoSelect getdate () as StartingTimegoDeclare @myencryptedtext varbinary (max)Set @ myencryptedtext = 0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CCPrint @myencryptedtextExec hack_encryption @ encryptedtext = @ myencryptedtextgoSelect getdate () as EndingTimego

Result

StartingTime-----------------------2007-12-18 18:24: 10.8430x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CCThis is the Encrypted text: MAKT?p tin d? li?u là: 123456789EndingTime-----------------------2007-12-18 18:26: 36.080

Figure 1

As you can see in the result (Figure 1), it only takes 2 minutes to retrieve the data and password. Basically, this procedure repeats all possible probabilities of ASCII characters over 6 characters long to find the password and use it to decrypt the data. Creating a procedure will not help much when the encrypted data is in a table. You have now to change this procedure to a scalar function as shown below Step 1 Create the procedure as follows

USE [master]GO/ ****** Object: UserDefinedFunction [dbo]. [Hack_encryption_password] Script Date: 12/18/2007 18:36:29 ****** /IF EXISTS (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (N '[dbo]. [Hack_encryption_password]')AND type in (N'FN ', N'IF', N'TF ', N'FS', N'FT '))DROP FUNCTION [dbo]. [Hack_encryption_password]GOUse [Master]goCREATE function [dbo]. [Hack_encryption_password] (@encryptedtext varbinary (max))Returns varchar (6)V?i th?c hi?n nh? m?t callerasBeginDeclare @password varchar (6)Declare @i intDeclare @j intDeclare @k intDeclare @l intDeclare @m intDeclare @n intSet @ i = -1Set @ j = -1Set @ k = -1Set @ l = -1Set @ m = -1Set @ n = -1Set @password = ''While @i

Step 2 Create a table with encrypted data

USE [tempdb]GO/ ****** Object: Table [dbo]. [MyTable] Script Date: 12/18/2007 18:44:40 ****** /IF EXISTS (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (N '[dbo]. [MyTable]') AND type in (N'U '))DROP TABLE [dbo]. [MyTable]GOCreate table MyTable (int, encrypteddata varbinary (max))goInsert into MyTable select 1, EncryptByPassPhrase ('Do', '1112228333')Insert into MyTable select 2, EncryptByPassPhrase ('Re', '1212223833')Insert into MyTable select 3, EncryptByPassPhrase ('Me', '1132223393')Insert into MyTable select 4, EncryptByPassPhrase ('Fa', '1114223383')Insert into MyTable select 5, EncryptByPassPhrase ('So', '1112523333')Insert into MyTable select 6, EncryptByPassPhrase ('La', '1112263373')Insert into MyTable select 7, EncryptByPassPhrase ('Si', '1112227338')go

Step 3 Query data using the following SQL statement

Select * from MyTable

You will see the data displayed as follows (Figure 2)

1 0x01000000D8ED1498BEA4023D541C6EA9766A6B7B0585FAE91B942C88C23677550C6FD7FA2 0x01000000F0725A52501A41D125F049011BE87C5C4A42263E7538B837B8278ADEE5FC26783 0x01000000C8804D8516B944B0AE35C71F79130DA415CED5CCF58E522692AC749115EEF0D94 0x010000007A91A24638C0E0354336AE5682805312CCB0B1E6BBACB6D9E65DC5D9DA73906E5 0x010000008FB6BDD91C3D1A8C94FAF647DE1F931CEE5104045BD03DE4E809565E74604DF36 0x01000000C3A41428A21EDE8D8579AF9C42132678448A9113A31A869276A7631A58A32BE37 0x01000000BD829E12D3EAAF96BB66930301BA1D9CD3748946F354301922A03AE49047FE00

Figure 2

Step 4 Use Hack_encryption_password Function to recover all passwords from the encrypted data in MyTable table. Execute the following SQL statement

Select ID, master. [dbo]. [hack_encryption_password] (encrypteddata) as Password from MyTable

You will see the following result (Figure 3)

1 Do2 Re3 Me4 Fa5 So6 La7 Si

Figure 3

The above function can be edited to return the encrypted data, as follows Step 1 Create the following function

USE [master]GO/ ****** Object: UserDefinedFunction [dbo]. [Hack_encryption_password] Script Date: 12/18/2007 18:36:29 ****** /IF EXISTS (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (N '[dbo]. [Hack_encryption_data]')AND type in (N'FN ', N'IF', N'TF ', N'FS', N'FT '))DROP FUNCTION [dbo]. [Hack_encryption_data]GOUse [Master]goCREATE function [dbo]. [Hack_encryption_data] (@encryptedtext varbinary (max))Returns varchar (8000)V?i th?c hi?n nh? m?t callerasBeginDeclare @data varchar (8000)Declare @password varchar (6)Declare @i intDeclare @j intDeclare @k intDeclare @l intDeclare @m intDeclare @n intSet @ i = -1Set @ j = -1Set @ k = -1Set @ l = -1Set @ m = -1Set @ n = -1Set @password = ''While @i

Step 2 Decrypt the data using the created function

Select ID, master. [dbo]. [hack_encryption_data] (encrypteddata) as Password from MyTable

The result is shown in Figure 4

Figure 4

Note :

• Procedures and functions can only hack for 6-character passwords.
• This procedure and function can take up many CPU to retrieve data and retrieve the password