hi ,
i tried below to explain what i am doing and issue. please go through below description and let me know if anything required.
STEP 1:
create table abc
(
column1 varbinary(2000),
column2 varbinary(2000),
column3 varbinary(2000),
column4 varbinary(2000)
)
STEP 2:
insert into abc values (12345,4567,3333,3217)
go
insert into abc values (5432,4444,2136,9765)
go
insert into abc values (2222,6547,3459,4521)
output must be like below if table doesn't have encryption functionality
select * from abc
tablename column1 column2 column3 column4
abc 12345 4567 3333
3217
abc 5432 4444 2136
9765
abc 2222 6547 3459
4521
step 3:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
CREATE CERTIFICATE certificatename
WITH SUBJECT = 'certificate'
CREATE SYMMETRIC KEY keyname
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE certificatename;
--below mentioned procedure encrypt the table
STEP 4:
--Encryption process:
create procedure procedurename
as
OPEN SYMMETRIC KEY keyname
DECRYPTION BY CERTIFICATE certificatename;
UPDATE abc
SET column1 = EncryptByKey(Key_GUID('keyname'), cast(column1 as varchar))
, column2= EncryptByKey(Key_GUID('keyname'), cast(column2 as varchar))
,column3 = EncryptByKey(Key_GUID('keyname'), cast(column3 as varchar))
, column4= EncryptByKey(Key_GUID('keyname'), cast(column4 as varchar))
from abc
STEP 5:
--Decryption process: this script i am using to read data from table
OPEN SYMMETRIC KEY keyname
DECRYPTION BY CERTIFICATE certificatename;
SELECT
column,
CONVERT(DECIMAL(18),DecryptByKey((column1))
AS 'column1',
CONVERT(DECIMAL(18),DecryptByKey((column2))
AS'column2',
CONVERT(DECIMAL(18),DecryptByKey((column3))
AS'column3',
CONVERT(DECIMAL(18),DecryptByKey((column4))
AS'column4'
FROM abc
output must be like same as insert values by me above STEP 2 after decryption.
actuval o/p must be like this:
tablename column1 column2 column3 column4
abc 12345 4567 3333
3217
abc 5432 4444 2136
9765
abc 2222 6547
3459 4521
but i am getting below output for consecutive numbers
tablename column1 column2 column3 column4
abc 12345 4567 11
3217
abc 5432 7
2136 9765
abc 5 6547
3459 4521
Thanks in advance!!!
mastanvali shaik
