Encrypting with certificate and symmetric key

I posted the following question in the programming section on 3/19 and did
not get any responses. Can anyone here help me out?
--
I can avoid opening a symmetric key when I decrypt data by using the new
function "decryptbykeyautocert."
But there does not seem to be anything compareable for encrypting.
So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
close the key.
Is this correct? Why isn't there a comparable function for encrypt? What
is the danger of inadvertantly leaving the key open? Will it close on
rollback?
Listed below is some code that provides and example of the issue:
USE master
--DROP DATABASE test
CREATE DATABASE test
USE test
IF object_ID('CreditCards') IS NOT NULL
DROP TABLE creditCards
GO
create table CreditCards (
Id int IDENTITY,
ccno varchar(20),
ccnoe varbinary(2000)
)
GO
INSERT CreditCards (ccno) VALUES ('1234567890')
GO
SELECT * FROM creditcards
GO
--
--Keys
--create database master key
CREATE master key
ENCRYPTION BY password = 'TestKey(123)'
--create the certificates that protects the data encryption keys
CREATE certificate CCE_Cert
authorization dbo with subject = 'CCE_Cert '
-- View certificates in database
select * from sys.certificates
-- Create symmetric key
CREATE symmetric key CCE_Key
with algorithm = AES_256
ENCRYPTION BY certificate CCE_Cert
select * from sys.symmetric_keys
open symmetric key CCE_Key
decryption by certificate CCE_Cert
--
--Encryption
--encrypt dat with key
UPDATE creditcards
SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
SELECT * FROM creditcards
--confirm key is open
select * from sys.openkeys
--view as raw data
SELECT * FROM creditcards
--Id ccno ccno
--1 1234567890 0x00506334876D334AB3EA195508AC73E601000000BE92F21E05800531482DE328AB76E15576D029C289F09F577F09BDEA1F2A027C
--view as decrypted
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 1234567890
close all symmetric keys
--view after key is closed
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 NULL
--use decryptbykeyautocert to avoid opening key
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now encrypt
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
--but this does not work, it is encrypted as NULL
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--open the key then INSERT
open symmetric key CCE_Key
decryption by certificate CCE_Cert
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now it works
--but there is no encryptbykeyautocert, only decryptHi Dave
See inline:
"Dave" wrote:
> I posted the following question in the programming section on 3/19 and did
> not get any responses. Can anyone here help me out?
> --
> I can avoid opening a symmetric key when I decrypt data by using the new
> function "decryptbykeyautocert."
> But there does not seem to be anything compareable for encrypting.
> So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
> an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
> close the key.
> Is this correct? Why isn't there a comparable function for encrypt? What
> is the danger of inadvertantly leaving the key open? Will it close on
> rollback?
> Listed below is some code that provides and example of the issue:
> USE master
> --DROP DATABASE test
> CREATE DATABASE test
> USE test
> IF object_ID('CreditCards') IS NOT NULL
> DROP TABLE creditCards
> GO
> create table CreditCards (
> Id int IDENTITY,
> ccno varchar(20),
> ccnoe varbinary(2000)
> )
> GO
> INSERT CreditCards (ccno) VALUES ('1234567890')
> GO
> SELECT * FROM creditcards
> GO
> --
> --Keys
> --create database master key
> CREATE master key
> ENCRYPTION BY password = 'TestKey(123)'
> --create the certificates that protects the data encryption keys
> CREATE certificate CCE_Cert
> authorization dbo with subject = 'CCE_Cert '
> -- View certificates in database
> select * from sys.certificates
> -- Create symmetric key
> CREATE symmetric key CCE_Key
> with algorithm = AES_256
> ENCRYPTION BY certificate CCE_Cert
> select * from sys.symmetric_keys
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> --
> --Encryption
> --encrypt dat with key
> UPDATE creditcards
> SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
> SELECT * FROM creditcards
> --confirm key is open
> select * from sys.openkeys
> --view as raw data
> SELECT * FROM creditcards
> --Id ccno ccnoe
> --1 1234567890 0x00506334876D334AB3EA195508AC73E601000000BE92F21E05800531482DE328AB76E15576D029C289F09F577F09BDEA1F2A027C
> --view as decrypted
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 1234567890
> close all symmetric keys
> --view after key is closed
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 NULL
>
> --use decryptbykeyautocert to avoid opening key
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now encrypt
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
>
If you did a select * from CreditCards you would get
Id ccno ccnoe
-- --
------1
1234567890
0x00599BE28153D949881C25E2DFCDCB3A0100000018FAD0F3E302CA5591F1F4B9AEE8CAE969D538149C1C774DF278DE987A7990EDC50917BB2E98106C4D8C357C1C2D2FD2
2 1234567890 NULL
(2 row(s) affected)
i.e. the underlying value is NULL and the encryption has not worked.
Therefore decrypting a NULL value does not make sense!
> --but this does not work, it is encrypted as NULL
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --open the key then INSERT
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now it works
That is because the key has been re-opened!
> --but there is no encryptbykeyautocert, only decrypt
>
I am not sure why there is not one, but I can see that if you have a one
key/one certificate mapping then this may be something hat would be useful,
but if you have (say) 10 keys encrypted by the one certificate do you encrypt
with all the keys, the first key or one key at random? The first option would
be very expensive, the second option would not have great value and would
potentially be very dangerous if it was used without understanding what was
happening, and the third option depends on the meaning and implementation of
random! I would probably not advise the use of decryptbykeyautocert either if
you want quicker decryptions!
You may want to put in a request at
https://connect.microsoft.com/SQLServer/Feedback
John