2 removed thanks and formatted code
source | link

Is it possible to encrypt a column (with Symmetric Key and Triple DES encryption) and then set up a login for an application such that the query code in the app does not have to be changed to used the DECRYPTBYKEYDECRYPTBYKEY function?

Right now the app is essentially using straight ANSI SQL. Pseudo code would looks like:

String query = "select EncryptedCol from TestTable";

String query = "select EncryptedCol from TestTable";

To my knowledge the above has to be changed to:

String query = "OPEN SYMMETRIC KEY TestTableKey DECRYPTION " + "BY CERTIFICATE EncryptionTestCert " + "SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedCol)) AS EncryptedCol " + "FROM TestTable ";

String query = 
"OPEN SYMMETRIC KEY TestTableKey DECRYPTION " +
"BY CERTIFICATE EncryptionTestCert " +
"SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedCol)) AS EncryptedCol " +
"FROM TestTable ";

SQL Server version is 2012 EE SP3-CU1 and the app is written in Java using jTDS.

Hope this quesionsquestion makes sense and appreciate any feedback.

Thanks!

Is it possible to encrypt a column (with Symmetric Key and Triple DES encryption) and then set up a login for an application such that the query code in the app does not have to be changed to used the DECRYPTBYKEY function?

Right now the app is essentially using straight ANSI SQL. Pseudo code would looks like:

String query = "select EncryptedCol from TestTable";

To my knowledge the above has to be changed to:

String query = "OPEN SYMMETRIC KEY TestTableKey DECRYPTION " + "BY CERTIFICATE EncryptionTestCert " + "SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedCol)) AS EncryptedCol " + "FROM TestTable ";

SQL Server version is 2012 EE SP3-CU1 and the app is written in Java using jTDS.

Hope this quesions makes sense and appreciate any feedback.

Thanks!

Is it possible to encrypt a column (with Symmetric Key and Triple DES encryption) and then set up a login for an application such that the query code in the app does not have to be changed to used the DECRYPTBYKEY function?

Right now the app is essentially using straight ANSI SQL. Pseudo code would looks like:

String query = "select EncryptedCol from TestTable";

To my knowledge the above has to be changed to:

String query = 
"OPEN SYMMETRIC KEY TestTableKey DECRYPTION " +
"BY CERTIFICATE EncryptionTestCert " +
"SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedCol)) AS EncryptedCol " +
"FROM TestTable ";

SQL Server version is 2012 EE SP3-CU1 and the app is written in Java using jTDS.

Hope this question makes sense and appreciate any feedback.

1
source | link

Is it possible to encrypt a column and NOT have to change app code?

Is it possible to encrypt a column (with Symmetric Key and Triple DES encryption) and then set up a login for an application such that the query code in the app does not have to be changed to used the DECRYPTBYKEY function?

Right now the app is essentially using straight ANSI SQL. Pseudo code would looks like:

String query = "select EncryptedCol from TestTable";

To my knowledge the above has to be changed to:

String query = "OPEN SYMMETRIC KEY TestTableKey DECRYPTION " + "BY CERTIFICATE EncryptionTestCert " + "SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedCol)) AS EncryptedCol " + "FROM TestTable ";

SQL Server version is 2012 EE SP3-CU1 and the app is written in Java using jTDS.

Hope this quesions makes sense and appreciate any feedback.

Thanks!