Issue with column Encryption

Question

Issue with column Encryption

vsamantha35

SSChampion

Points: 11551
More actions
November 30, 2017 at 11:29 am

#330175

Hi All,
I want to encrypting a column but when I am trying to decrypt the value, I am not getting the original value.
Can anyone tell me if I have done anything wrong or missing anyhting??
select @@version
--Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 15063: )
create database Testdb
go
use testdb
go
use TestDB
go
-- new created database master key - DMK
create master key
encryption by password='test#123'
go
select is_master_key_encrypted_by_server,* from sys.databases
where is_master_key_encrypted_by_server = 1
go
create certificate MyCert
with subject = 'Dummy Certificate for TestDB created by Mahesh'
,expiry_date = '10/31/2020'
go
select * from sys.certificates -- no rows
go
use TestDB
go
----Create a symmetric key and encrypt it by using above created certificate
create symmetric key MySymKey
with algorithm=AES_256
encryption by certificate MyCert
go
select * from sys.symmetric_keys -- 1 row , observer create_date , modify_date
go
----You need to open\decrypt symmetric key before you it available   for use.
open symmetric key MySymKey
decryption by certificate MyCert
go
select * from sys.openkeys -- no rows
go
--src or stg table
create table t1
( c1 [nvarchar](50)
)
insert into t1
select '7157604000580201'
union all
select '7157604000580202'
union all
select '7157604000580203'
--destination table
CREATE TABLE [test](
   [c1] [nvarchar](50) NOT NULL,
   [c1_encrypt_val] [varbinary](300) NOT NULL    -- stores the encrypted value of c1

)
-- do the encryption store it in test tbl
insert into [test](c1,c1_encrypt_val)
select c1,ENCRYPTBYKEY(key_guid('MySymKey'),c1) from t1
select * from test;
--decrypt is not working
select
   c1 as ActualValue,
   c1_encrypt_val as EncryptedVal,
   CONVERT(varchar(50),decryptbykey(c1_encrypt_val)) as DecryptVal
from test
go

-- cleanup script
/*
-- close the keys
close all symmetric keys
go
select * from sys.openkeys
go

drop table test
drop table t1
drop symmetric key MyKey
drop certificate mycert
drop master key
go

use master
go
drop database TestDB
go
*/
Thanks,
Sam

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

I've not looked too far, but one thing I notice is that your original column is an nvarchar, and you're converting it back to a varchar. You should be converting to the same type; especially when using varbinary. As an example:

SELECT CONVERT(nvarchar(5),CONVERT(varbinary,'Hello'))

Notice how the result is:N'??o'. That isn't what the original value was,
Flipping it around:

SELECT CONVERT(varchar(5),CONVERT(varbinary,N'Hello'))

The result is: 'Hel'.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jasona.work SSC Guru Points: 50217 More actions · Answer 2

I fairly sure that when you're storing encrypted values, you need to use a VARBINARY(MAX) to do it.

I don't have access to my test server right now, or I'd try running your test setup and swap out the VARBINARY(300) with a (MAX)

Thom A SSC Guru Points: 99143 More actions · Answer 3

Just gave your script a testand apart from having to amend the date format this ran fine, and even returned the correct value (without changing the nvarchar). That was, however, on SQL Server 2017.

I'd still suggest following my and Jasona's advice though.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jasona.work SSC Guru Points: 50217 More actions · Answer 4

Thom A - Thursday, November 30, 2017 11:53 AM
Just gave your script a testand apart from having to amend the date format this ran fine, and even returned the correct value (without changing the nvarchar). That was, however, on SQL Server 2017.
I'd still suggest following my and Jasona's advice though.

I just tested this on a SQL2016 install, and Thom is more correct than my answer. You need to make sure your datatypes stay the same when decrypting and you don't need to make the varbinary a MAX.