Error when use diff key to decrypt than was used to encrypt

Question

Error when use diff key to decrypt than was used to encrypt

Valued Member

Points: 60

February 14, 2006 at 8:36 am

I am getting the following error, if the key for decrypting is different from the one used for encrypting. I am using v. 0.9. and running SQL 2k with SP3. If I add the following code to your sample for using udf_aesencrypt/udf_aesdecrypt, I will get an error. The only way to recover is to stop and restart SQL Server. In some case it will even cause SQL Server to stop. Is there something that I am doing wrong?

-- Error message

[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

-- Code

UPDATE @AES_Fake_SSN_Test
SET Success_Flag =
 CASE WHEN Fake_SSN = Dec_Fake_SSN
 THEN 'Y'
 ELSE 'N' END

SELECT Fake_SSN 
 ,Enc_Fake_SSN 
 ,CAST(master.dbo.udf_aesdecrypt(Enc_Fake_SSN, @bkey) AS VARCHAR(15))
 ,Dec_Fake_SSN 
 ,Success_Flag 
FROM  @AES_Fake_SSN_Test

-- will run fine up to here then gets a connection broken and have 
-- to stop and restart SQL Server

SET @key = 'This is a 32 byte (256 bit) key?' -- Changed the key from ! to ?
SET @bkey = CAST(@key AS VARBINARY(64))

SELECT Fake_SSN 
 ,Enc_Fake_SSN 
 ,CAST(master.dbo.udf_aesdecrypt(Enc_Fake_SSN, @bkey) AS VARCHAR(15))
 ,Dec_Fake_SSN 
 ,Success_Flag 
FROM  @AES_Fake_SSN_Test

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

These are symmetric encryption algorithms, so you have to use the same key to decrypt that you used to encrypt.

mwcarey Valued Member Points: 60 More actions · Answer 2

Thank you for you reply and the work you have put out there. I do understand that the same key is required to encrypt and decrypt. What I was looking at the was the possiblility of someone trying to guess keys and what would happen. When the wrong key is entered, it seams to break the dll, and the only way to recover is to stop and restart SQL Server.

Mike C SSC-Insane Points: 23224 More actions · Answer 3

You're right, an invalid key throws an exception in the code. Because it's running in the same process space, it requires a server re-start. I left out extra exception handling code in the interests of speed, but it would be pretty easy to add code to handle exceptions caused by invalid keys being passed.

For now I've been looking at creating some tools that encrypt using the DP/API, since it also offers integrated key management features.

mwcarey Valued Member Points: 60 More actions · Answer 4

mwcarey

Valued Member

Points: 60

March 1, 2006 at 11:25 am

#624079

Thanks for the info.

Mike C SSC-Insane Points: 23224 More actions · Answer 5

When I have time to get the DP/API XP interfaces worked up, I'll ask the good folks here at SSC to post an article for general distribution. Since it will be built using the DP/API, it will be very robust by default.

Mike C SSC-Insane Points: 23224 More actions · Answer 6

Hi mwcarey, the new version is up at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp.

Thanks,

Michael Coles

mwcarey Valued Member Points: 60 More actions · Answer 7

mwcarey

Valued Member

Points: 60

August 21, 2006 at 11:55 am

#655954

Thanks for the update.