3

I can encrypt and decrypt varchar, nvarchar,char columns using symmetric key but cannot do the same on int,datetime,float,bigint data types columns

Sample table: Mytable

| Id  Code  percentage  name    activity   |  
+------------------------------------------+
| 1   ad     43.43    James    Running     |  
| 3   Pr     70.43    Sam      Cooking     |  
| 5   nt     90.34    Lisa     Walking     |  
| 4   ash    0.00     James    Stealing    |  
| 2   han    0.00     James    Lacking     |  
| 8   ant    73       Sam      Cooking     |

I want to encrypt and decrypt ID column and Percentage which are of integer and float data types respectively.

I am using this code to encrypt:

OPEN SYMMETRIC KEY SymKey DECRYPTION BY CERTIFICATE data  

ALTER TABLE Mytable 
SET ADD idencry VARBINARY(128) NULL  


UPDATE Mytable 
SET idencry = ENCRYPTBYKEY(KEY_GUID('datamSymKey'), CONVERT(varbinary, ID))

To decrypt I am using this code: 

SELECT 
    id,
    CONVERT(NVARCHAR(60), DECRYPTBYKEY(idencry )), * 
FROM 
    Mytable

But its not returning the correct result... same goes with float and datetime date type

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
adams
  • 181
  • 3
  • 16
  • Please feel free to add the related code, by doing so the users will get an idea what you are doing and what is the problem in the current code! See [Example](https://stackoverflow.com/questions/53372997/pivot-query-on-distinct-records) – Prashant Pimpale Dec 31 '18 at 07:35
  • 1
    This post might help you https://www.c-sharpcorner.com/UploadFile/chinnasrihari/data-encryption-and-decryption-in-sql-server-2008/ – Suraj Kumar Dec 31 '18 at 08:41
  • This post can explain and show example too : https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017 – Sanpas Dec 31 '18 at 09:28

1 Answers1

1

Hi i have tested thoses code on SQL Server 2016 and it's work : 

CREATE CERTIFICATE Test04   
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
   WITH SUBJECT = 'Sammamish Shipping Records',   
   EXPIRY_DATE = '20201031';  
GO  

CREATE SYMMETRIC KEY #SymKey   
WITH ALGORITHM = AES_128 
ENCRYPTION BY CERTIFICATE Test04;  
GO  

create Table #TestData
(
    Id int,
    Code varchar(10)
)

Insert into #TestData
(
    Id,
    Code
)
SELECT 1,'AA'
UNION 
SELECT 2,'BB'
UNION 
SELECT 3,'CC'
UNION 
SELECT 4,'DD'
UNION 
SELECT 5,'EE'
UNION 
SELECT 6,'FF'

SELECT * FROM #TestData

OPEN SYMMETRIC KEY #SymKey DECRYPTION BY CERTIFICATE Test04 WITH PASSWORD='pGFD4bb925DGvbd2439587y'

ALTER TABLE #TestData 
 ADD idencry VARBINARY(128) NULL  


UPDATE #TestData 
SET idencry = ENCRYPTBYKEY(KEY_GUID('#SymKey'), CONVERT(varbinary, Id))



SELECT * FROM #TestData


OPEN SYMMETRIC KEY #SymKey DECRYPTION BY CERTIFICATE Test04 WITH PASSWORD='pGFD4bb925DGvbd2439587y'

SELECT 
    Id,
    CONVERT(int, DECRYPTBYKEY(idencry)) AS 'IdDecrypted', * 
FROM 
    #TestData


DROP TABLE  #TestData 

DROP SYMMETRIC KEY #SymKey   

DROP CERTIFICATE Test04

I think you have to CAST to the source type when you using decryptbykey

 CONVERT(NVARCHAR(60), DECRYPTBYKEY(idencry )), *

What is your "Id" column type ?

Can you try : 

CONVERT(INT, DECRYPTBYKEY(idencry )), *
Sanpas
  • 1,170
  • 10
  • 29