1

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
    This is what [Always Encrypted[(blogs.microsoft.com/next/2015/05/27/…) was created for, but you'll have to wait for SQL Server 2016 (and for the jTDS drivers to catch up). In the meantime, can't your Java app call stored procedures, such that the data is encrypted there? That's really not going to change the fact that the decrypted text is being sent over the wire, but it does mean you can isolate query changes from the app... – Aaron Bertrand Mar 9 '16 at 17:33
  • 1
    Thanks Aaron. That answers it. Have read many of your articles. Always very helpful. – Park_Ranger Mar 9 '16 at 17:42
  • We could implement stored procedures as an interim measure and then upgrade to 2016 when the time comes. Was hoping to avoid that due to a time crunch for a fast approaching deliverable. – Park_Ranger Mar 9 '16 at 17:49
3

This is what Always Encrypted was created for, but you'll have to wait for SQL Server 2016 (and for the jTDS drivers to catch up).

In the meantime, your Java app could call stored procedures, such that the data is encrypted there. That's really not going to change the fact that the decrypted text is being sent over the wire, but it does mean you can isolate query changes from the app...

1

This is definitely possible with your version of SQL Server. You can either create stored procedures to manage the encryption and decryption on the server or you can rename the original table and create a view with the original table name and with instead of triggers for the insert and update operations. Delete works without adding a trigger. You will need to rework the triggers to suit your needs. As far as unencrypted data going over the network, you can install a certificate in the certificate store of the service account on the server and use it to force all connections to be encrypted with a server configuration change using Configuration Manager or configure encryption from the client side by adding "encryption=true" to your connection string.

I've designed an example:

use master
go
create database EncryptedData
go
use EncryptedData
create master key encryption by password='P@ssw0rd!'

create certificate KeyProtection with subject='Key Protection'

create symmetric key ColumnKey 
    with algorithm=AES_256 
    encryption by certificate KeyProtection

create table SecretMessages(Ciphertext varbinary(4000))
go
create view dbo.MessageRecords 
as
select 
    cast(DECRYPTBYKEYAUTOCERT( cert_id('KeyProtection'), null,Ciphertext) as varchar(max)) MessageRecord
from dbo.SecretMessages
go 

open symmetric key ColumnKey decryption by certificate KeyProtection

insert into SecretMessages(Ciphertext) values (ENCRYPTBYKEY(key_guid('ColumnKey'),'Hello world 1'))
insert into SecretMessages(Ciphertext) values (ENCRYPTBYKEY(key_guid('ColumnKey'),'Hello world 2'))
insert into SecretMessages(Ciphertext) values (ENCRYPTBYKEY(key_guid('ColumnKey'),'Hello world 3'))
insert into SecretMessages(Ciphertext) values (ENCRYPTBYKEY(key_guid('ColumnKey'),'Hello world 4'))
insert into SecretMessages(Ciphertext) values (ENCRYPTBYKEY(key_guid('ColumnKey'),'Hello world 5'))
close symmetric key ColumnKey


select * from MessageRecords 
go
create TRIGGER InsMessageRecordsTrg on MessageRecords
INSTEAD OF INSERT
AS
BEGIN

open symmetric key ColumnKey decryption by certificate KeyProtection
INSERT INTO SecretMessages
   SELECT (ENCRYPTBYKEY(key_guid('ColumnKey'),MessageRecord))
   FROM inserted
close symmetric key ColumnKey
END
go
create TRIGGER UpdMessageRecordsTrg on MessageRecords
INSTEAD OF update
AS
BEGIN
IF (UPDATE(MessageRecord)) -- If qualification is updated
BEGIN
    open symmetric key ColumnKey decryption by certificate KeyProtection
    update s
        set Ciphertext=ENCRYPTBYKEY(key_guid('ColumnKey'),i.MessageRecord)
    from SecretMessages s
    join deleted d
        on
        cast(DECRYPTBYKEYAUTOCERT( cert_id('KeyProtection'), null,Ciphertext) as varchar(max)) = d.MessageRecord
    cross join inserted i
    close symmetric key ColumnKey
END
end
go
insert into MessageRecords(MessageRecord) values ('Goodbye cruel world')


select * from MessageRecords 

update MessageRecords set MessageRecord='My new message2' where MessageRecord='Hello world 2'

delete from dbo.MessageRecords where MessageRecord='Hello world 3'

select * from MessageRecords 

use master 
go
drop database EncryptedData

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.