Here's my case: I have a column in my DB that contains encrypted data. This column is regular VARCHAR column. This data was encrypted by Java app, using DES/ECB/PKCS5Padding algorithm. I have access to the key file that was used to encrypt this data.
What I need is to decrypt this data, using T-SQL. Is it possible?
I'm aware of SQL Server encryption/decryption mechanism but it seems that they are for "new" data (I'm creating table and starting putting data into it) that is encrypted by SQL Server itself, not already encrypted by other app.
Actually, it's similar to this one Encrypt In .NET / Decrypt in SQL Server but answer is related to C#, so not my case...
I found that it can probably be done using:
CREATE SYMMETRIC KEY TestSymmKey
WITH
ALGORITHM = DES,
KEY_SOURCE = 'initial_bytes'
ENCRYPTION BY PASSWORD = 'windowspass';
GO
DECLARE @KeyGUID UNIQUEIDENTIFIER;
SET @KeyGUID = KEY_GUID('TestSymmKey');
OPEN SYMMETRIC KEY TestSymmKey DECRYPTION BY PASSWORD = 'windowspass';
SELECT ENCRYPTBYKEY(@KeyGUID, 'Lorem ipsum')
I can see that returned values are different for same input:
0x0082099FAF76A74581FAA53B7FE26B8001000000DF8C5134EF5FD25C89E221C1443810AC325F28AC083C48FBEFF337D4C247B83A39294A077E54E8A2
0x0082099FAF76A74581FAA53B7FE26B800100000087FF86D86DFFD6C32F7696DCA13BF4E85A032910B85E92731D493AC4FCE9756C07DA82F5AA0F4313
0x0082099FAF76A74581FAA53B7FE26B80010000003CDA110EB854563BAB73305A52AC4B78C5A613E167E7BBC8467195832C8810683CB19CF080FCC520
This is probably cause of some kind of salt mechanism. So I cannot simply get DES result generated by some other app and try to decrypt it as SQL Server will assume that salt is in it?
The first problem is that I don't see option to set padding and mode (e.g. ECB). The second one is that result is in binary and I need it as string so probably some Base64 encoder is needed?
My goal is that I can decrypt in T-SQL some string that was encrypted in Java or even online tool (e.g. http://www.tools4noobs.com/online_tools/encrypt/)
