-1

My application flow is as follow.

JAVA APP ----> MS SQL (Tables)---> MS SQL (Stored procedure) ---> Another DB

Here from Java application i am pushing certain Sensitive information that i even want to hide from DBA. So i want to encrypt those column values from java and before the Stored Procedure pushes it to Another DB i want to Decrypt it in SQL code.

But the Problem is that MS SQL do not allow Custom Keys to be passed for Encryption or Decryption .It generates those keys itself and we cant see those key values. So i cant decrypt them as i cant pass on my keys (Which i used to encrypt values in Java code) in SQL DecryptByKey Mehtod. i am using 3DES encryption Scheme. Can any one help me with this issue.

Now the core Question is. How can i decrypt any 3DES based Encrypted Value (i am encrypting this value in Java Code) in MS SQL server 2008 when i know all the details about of Encryption value such as salt and other things. Is there any method in SQL where i can pass my custom key and decrypt the values.

Any help will be deeply appreciated.

Nimble Fungus
  • 513
  • 3
  • 22
  • Use a standard encryption/decryption scheme on both ends and it'll work: http://stackoverflow.com/questions/17113113/interop-encryption-decryption-between-java-net-with-aes-and-specifying-iv-and-ke – duffymo Jun 10 '15 at 17:25
  • DuffyMo. The problem is that SQL server is generating its own keys and not supporting the key that i may pass. it is generating keys from PassPhrases(That we pass) and Algorithm by which it generates the Keys are not in Open. So even I am using 3DES in both Java and SQL .. the Key that will be generated in SQL Will not be known to me. [LINK]https://social.msdn.microsoft.com/forums/sqlserver/en-US/4b56da88-1166-4a96-bfb5-d2694e03d7b5/recreate-symmetric-key-on-different-server-database – Nimble Fungus Jun 10 '15 at 17:38
  • Key has to be shared between the two: http://stackoverflow.com/questions/20227/how-do-i-use-3des-encryption-decryption-in-java – duffymo Jun 10 '15 at 17:45
  • Duffymo. I can Encrypt and Decrypt in Java. But how can i get the Encryption key of SQL that is creating. Using Java i can Encrypt and Decrypt the Code. – Nimble Fungus Jun 10 '15 at 18:15
  • Have Java encrypt when it writes and decrypt when it reads. That's how I do it. – duffymo Jun 10 '15 at 18:35
  • @duffy Its a multi level Application. Java is pushing data into MS SQL tables. Then from SQL server Stored Procedure is pushing data to another Database. The Stored Procedure is Encrypting Values (before it is pushing to another DB )Using EncryptByPassPhrase. I need to Encrypt the Data from java and need to Decrypt the value in Store procedure before it is Encrypted by EncryptByPassPhrase. – Nimble Fungus Jun 10 '15 at 18:40

2 Answers2

1

You can encrypt whole jdbc traffic with SSL. Look at https://msdn.microsoft.com/en-us/library/bb879935%28v=sql.110%29.aspx

  • We are already using the SSL. But we dont want the DBA to see those column values. So we want to Encrypt the values of some column also. – Nimble Fungus Jun 10 '15 at 18:23
1

It is not possible to view the SQL server keys, and hence we can't decrypt anything encrypted by SQL server.

I had to change my approach for this scenario.

Nathaniel Ford
  • 20,545
  • 20
  • 91
  • 102
Nimble Fungus
  • 513
  • 3
  • 22
  • can you provide more information and tell me the best way to do that ? I've encrypted some columns in my SQL Server... can you please tell how can i decryp them in java ? – Ilya Budu Apr 14 '21 at 16:43