Code Chronicles

Analyzing SQL AES/RSA Encryption Values

Analyzing SQL AES/RSA Encryption Values

Jeff Tong's photo
Jeff Tong
·

4 min read

In relation to SQL RSA/AES Encryption .

Preliminary Analysis of Binary DataType

SELECT CAST(123456 AS BINARY(4));

0x0001E240

The hexadecimal and binary equivalent (broken down to 4 bytes):

0x0001E240 = 00000000 00000001 11100010 01000000

Analysis of SQL AES Encrypted Value

OPEN SYMMETRIC KEY SOME_KEY DECRYPTION BY CERTIFICATE SOME_CERT
DECLARE @ciphertext VARBINARY(256) 
SET @ciphertext = EncryptByKey(Key_GUID('SOME_KEY'), 'I''m a string to encrypt!')
SELECT @ciphertext
SELECT CONVERT(NVARCHAR(max), @ciphertext , 2);

Yields the following Hexadecimal:

0x0050B63F362F9A1C9222474C0605CD7B020000009DE0ED6C1A9D9CD91622EF7820FA0D2812FDD492C5B51E08EDE471BA8663B2A4AAFF6919132C950244FAE054AD34B14DF383F08B73EED0B4D2777EA26A83E48D

Length is 84 bytes.

Note: Any one byte can be represented in two hexadecimal characters.

Split a hexadecimal into pairs for easy reading:

($hex -split "([0-9,A-F]{2})" | WHERE-OBJECT { $_ }) -join " "

00 50 B6 3F 36 2F 9A 1C 92 22 47 4C 06 05 CD 7B 02 00 00 00 9D E0 ED 6C 1A 9D 9C D9 16 22 EF 78 20 FA 0D 28 12 FD D4 92 C5 B5 1E 08 ED E4 71 BA 86 63 B2 A4 AA FF 69 19 13 2C 95 02 44 FA E0 54 AD 34 B1 4D F3 83 F0 8B 73 EE D0 B4 D2 77 7E A2 6A 83 E4 8D

Which converts to binary :

000000000101000010110110001111110011011000101111100110100001110010010010001000100100011101001100000001100000010111001101011110110000001000000000000000000000000010011101111000001110110101101100000110101001110110011100110110010001011000100010111011110111100000100000111110100000110100101000000100101111110111010100100100101100010110110101000111100000100011101101111001000111000110111010100001100110001110110010101001001010101011111111011010010001100100010011001011001001010100000010010001001111101011100000010101001010110100110100101100010100110111110011100000111111000010001011011100111110111011010000101101001101001001110111011111101010001001101010100000111110010010001101

Split a binary number into octets for easy reading:

 ($bin -split "([01]{8})" | ? { $_ }) -join " "

From Pro T-SQL 2005 Programmer's Guide:

When SQL Server encrypts by symmetric key, it adds metadata to the encrypted result, as well as padding, making the encrypted result larger (sometimes significantly larger) than the unencrypted plain text. The format for the encrypted result with metadata follows this format:

  • The first 16 bytes of the encrypted result represent the GUID of the symmetric key used to encrypt the data.
00 50 B6 3F 36 2F 9A 1C 92 22 47 4C 06 05 CD 7B

00000000 01010000 10110110 00111111 00110110 00101111 10011010 00011100
10010010 00100010 01000111 01001100 00000110 00000101 11001101 01111011
  • The next 4 bytes represent the version number, currently hard-coded as "01000000".

02 00 00 00

00000010 00000000 00000000 00000000
  • The next 8 bytes for DES encryption (16 bytes for AES encryption) represent the randomly generated initialization vector.
9D E0 ED 6C 1A 9D 9C D9 16 22 EF 78 20 FA 0D 28

10011101 11100000 11101101 01101100 00011010 10011101 10011100 11011001
00010110 00100010 11101111 01111000 00100000 11111010 00001101 00101000
  • The next 8 bytes are header information representing the options used to encrypt the data. If the authenticator option is used, this header information includes a 20-byte SHA1 hash of the authenticator, making the header information 28 bytes in length.
12 FD D4 92 C5 B5 1E 08 ED E4 71 BA 86 63 B2 A4 AA FF 69 19 13 2C 95 02 44 FA E0 54

00010010 11111101 11010100 10010010 11000101 10110101 00011110 00001000
11101101 11100100 01110001 10111010 10000110 01100011 10110010 10100100
10101010 11111111 01101001 00011001 00010011 00101100 10010101 00000010
01000100 11111010 11100000 01010100
  • The last part of the encrypted data is the actual data and padding itself. For DES algorithms, the length of this encrypted data will be a multiple of 8 bytes. For AES algorithms, the length will be a multiple of 16 bytes.
AD 34 B1 4D F3 83 F0 8B 73 EE D0 B4 D2 77 7E A2 6A 83 E4 8D

10101101 00110100 10110001 01001101 11110011 10000011 11110000 10001011
01110011 11101110 11010000 10110100 11010010 01110111 01111110 10100010
01101010 10000011 11100100 10001101

The last remaining bytes should be the encrypted payload in multiples of 16 bytes, but the above is consistently 20 bytes...

Total length is 84 bytes.

// TODO:  Convert binary back to hexadecimal.  Store as varbinary and convert to string.  Attempt to read string to byte array and decrypt using AES and same string key.

To Be Continued...

Summary

I wanted to see if I can take the encrypted payload with the same symmetric key and decrypt it using the same AES-256 algorithm in C#. It may be easier if the aim is to encrypt/decrypt column values that were encrypted by a C# application to reproduce the encryption/decryption logic in T-SQL.

References

SQL ServerencryptionCryptography