Cipher Blowfish enctyption, decryption

Question

Cipher Blowfish enctyption, decryption

raknel

Old Hand

Points: 339
More actions
October 18, 2007 at 3:18 am

#179507

Hi all,
I have some encrypted data stored in db. The data was encrypted in Java with methods from Cypher class. It can be decrypted in the same way in java (with cipher.init, cipher.doFinal), but I need to decrypt it in sql. So there is way to decrypt the data in sql? For example in stored procedures?
thanks

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

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

iain lastname SSC Eights! Points: 912 More actions · Answer 1

as far as i know, there isn't a native way to do this for SQL Server, unless you count the CLR in 2005. You'd either use that to decrypt it - write something in C#/VB that decrypts using Blowfish - or you could pull it out to a small app or webpage and do it there and then update the rows.

Perhaps someone else knows a better way?

Lowell SSC Guru Points: 323518 More actions · Answer 2

The DBA Toolkit found here on SqlServerCentral includes an extended stored procedure for the blowfish encryption method:

http://www.sqlservercentral.com/articles/Security/sql2000dbatoolkitpart1/2361/

you could give that a try; it may or may not work; for example, My company was using an AES encryption method for certain columns, and even with the same encryption seed, when i was testing the vb6 version we were using to encrypt/decrypt did not procude the same results as the extended stored procedure. named the same, but different in the details, so we ended up centralizing to always use the stored proc instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

raknel Old Hand Points: 339 More actions · Answer 3

Thanks Lowell,

I tried with the DBA Toolkit, but you're right...The encryption in Java uses the blowfish encryption, I tried the same data to encrypt with the extended stored procedure for blowfish from the toolkit, but the results are not the same...Maybe I'm missing something?

Lowell SSC Guru Points: 323518 More actions · Answer 4

no i don't think your missing anything; an encyption method is a style to do it back and forth, but the actual implementation is not going to be the same between programmers;

That's what we are seeing here. someone elses suggestion to call the java elements that do this for you seems to be the best bet.

Maybe you can use sp_OACreate and the associated methods in order to use the java to encrypt and decrypt instead;

the otehr option is to get the Java to start calling the method in the dba toolkit instead of natively;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

raknel Old Hand Points: 339 More actions · Answer 5

Unfortunately I can't change the way how the data is encrypted in java, they use it for some time and it is very complex...They use a lot of keys to get a final key, and with this final key the data is encrypted with the methods for blowfish algorithm from Cipher class, which is in an extension for java jdk.

So I got this final key and tried to decrypt the data with the extended stored procedures. But it seems that it doesn't work. Maybe I should search or ask in java forums how this methods from Cipher class really work...

iain lastname SSC Eights! Points: 912 More actions · Answer 6

they probably use a key to encrypt the data (datakey), which is stored with the encrypted data, and a master key to encrypt the datakey. They probably used a data-iv and master-iv too?

To decrypt you would use the masterkey (with master-iv) and decrypt the datakey. then apply the datakey and data-iv to the encrypted data.