SQL server encryption... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL server encryption...

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2011-07-18 : 04:33:21

Hi,

I have a table in my 2008 DB in which 1 column is encrypted by symmetric keys.

Now, I have to insert data form the same table into itself. I earlier felt I would need to decrypt the data, then again encrypt it to insert it into the column..eg:

OPEN SYMMETRIC KEY key1 DECRYPTION BY CERTIFICATE cert1;
insert into table (enccol)
select 
EncryptByKey(key_guid('key1'),CONVERT(VARBINARY(MAX),CONVERT(NVARCHAR(MAX),DecryptByKey([enccol])))) from table where id = 2

CLOSE SYMMETRIC KEY key1

But it seems the below seems to work too..

insert into table (enccol)
select enccol from table where id = 2

And I am able to decrypt the insert values successfully..

Does SQL server automatically takes care of this? datatype for enccol is varbinary(max)

Thanks
Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-18 : 07:43:38

quote:
Does SQL server automatically takes care of this?

If by "take care of this" you mean copy the encrypted values directly, then yes. There's no need for it to decrypt-encrypt.

xpandre
Posting Yak Master

212 Posts

Posted - 2011-07-18 : 08:01:37

yes sir. Thats what I meant. Thanks a lot..:-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources