Encryption key closed during UPDATE statement

i'm having strange issue update statement appears closing encryption key, resulting in being unavailable further use same stored procedure. i've added debug code shows key open prior update , appears closed after. code i'm using along lines of shown below:

--  -- encryption key opened @ point  --    declare @keynamebefore sysname  declare @keycountbefore int	  declare @keynameafter sysname  declare @keycountafter int  			  -- name of open key  select @keynamebefore = key_name sys.openkeys  print @keynamebefore    -- count of open keys  select @keycountbefore = count(*) sys.openkeys  print @keycountbefore    update table1  set 	  	col1 = encryptbykey(key_guid(@enc_key), rtrim(@data1), 1, hashbytes('sha1', convert(varbinary, @custkey)))  	col2 = encryptbykey(key_guid(@enc_key), rtrim(@data1), 1, hashbytes('sha1', convert(varbinary, @custkey))),  table_key = @custkey  	  -- name of open key  select @keynameafter = key_name sys.openkeys  print @keynameafter  		  -- count of open keys  select @keycountafter = count(*) sys.openkeys  print @keycountafter

@keyname & @keycount show name of key , 1 respectively.

@keyname & @keycount show blank , 0 respectively.

any ideas may going wrong? 

hi biqchris747,

according said, wrote down following code testing, can return right values. think code wrote down correct, there other reasons that.

the opened key bound session, not execution context (including module frame) , remain opened until key explicitly closed (using close symmetric key) or session terminated.

please refer test code:

create database encrypttest go use encrypttest go create table testtable (firstcol int, secondcol varchar(50)) go insert testtable values(1, 'first'); insert testtable values(2, 'second'); insert testtable values(3, 'third'); insert testtable values(4, 'four'); insert testtable values(5, 'five'); go use encrypttest go select * testtable go use encrypttest go create master key encryption password = 'lm8%jfmf' go use encrypttest go create certificate encrypttestcert subject = 'sqlauthority' go use encrypttest go create symmetric key testtablekey algorithm = triple_des encryption certificate encrypttestcert go use encrypttest go alter table testtable add encryptsecondcol varbinary(256) go use encrypttest go open symmetric key testtablekey decryption certificate encrypttestcert update testtable set encryptsecondcol = encryptbykey(key_guid('testtablekey'),secondcol) go use encrypttest go alter table testtable drop column secondcol go use encrypttest go select * testtable go use encrypttest go open symmetric key testtablekey decryption certificate encrypttestcert select convert(varchar(50),decryptbykey(encryptsecondcol)) decryptsecondcol testtable go -- attempt: select * testtable; declare @keynamebefore sysname declare @keycountbefore int    declare @keynameafter sysname declare @keycountafter int -- name of open key select @keynamebefore = key_name sys.openkeys print @keynamebefore -- count of open keys select @keycountbefore = count(*) sys.openkeys print @keycountbefore      update testtable set encryptsecondcol = encryptbykey(key_guid('testtablekey'),'changed')  firstcol = 2; -- name of open key select @keynameafter = key_name sys.openkeys print @keynameafter              -- count of open keys select @keycountafter = count(*) sys.openkeys print @keycountafter -- close use encrypttest go close symmetric key testtablekey go drop symmetric key testtablekey go drop certificate encrypttestcert go drop master key go use [master] go drop database [encrypttest] go

the result below：

 

please refer to:
encryptbykey: http://msdn.microsoft.com/zh-cn/library/ms174361.aspx.

regards,
amy peng

---

amy peng

technet community support

SQL Server  >  SQL Server Security