How to decrypt column and show results in SSRS table

Question

Hi Team,

My team storing data in encryption format in SQL database. I want to decrypt the field and want to show SSRS reports. Is their any option to archive this requirement.
Please help on this.

Thanks

Answer 1

Hi @Bala Narasimha Challa ,
Encrypt column data in SQL Server, you need to write a stored procedure to execute a statement and query.
There are 3 major factors to encrypt data at the column level, as below.

• Master Key-a key which is used to protect the keys of certificates and symmetric keys in the database
• Certificates-used to encrypt the data in the database
• Symmetric Key-can be encrypted by using many options, like certificate, password, symmetric key. There are different algorithms available for encrypting a key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

The following are my encryption steps：

Use AdventureWorksDW2019     
Create master key encryption by password ='abc123' --Create a Master Key .  
Create certificate C1 with subject = 'Credit Card Data' --Create a Certificate  
Create symmetric key SK1 with algorithm = AES_256 encryption by certificate C1--Now with the help of certificate and master key create SYMMETRIC KEY  

I have a table like this :

--Encrypt the data in the column

Open symmetric key SK1  
Decryption by certificate C1  
insert into TestEncryption(Id, EncryptedCCNumber, CCNumber) values (1, ENCRYPTBYKEY(key_guid('SK1'),'5000'), '5000')  
Close symmetric key SK1  
SELECT * From TestEncryption  

Output：

--DECRYPT the data

Open symmetric key SK1  
Decryption by certificate C1  
select *, convert(varchar, DECRYPTBYKEY(EncryptedCCNumber)) as 'Decrypted CC Number' from TestEncryption --You will get an extra column named "Decrypted CC Number"  
Close symmetric key SK1  
SELECT * From TestEncryption  

Output:

Create query results as a new table TestEncryption2

Open symmetric key SK1  
Decryption by certificate C1  
;with cte as  
(  
select *, convert(varchar, DECRYPTBYKEY(EncryptedCCNumber)) as'Decrypted CC Number' from TestEncryption --You will get an extra column named "Decrypted CC Number"  
)  
  
select * into TestEncryption2 from cte  
Close symmetric key SK1  

We will retrieve a decrypted table TestEncryption2 for use in SSRS.
The data in the example comes from: Encrypt And Decrypt Column Data In SQL Server.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Joyzhao-MSFT ,

I'm using the menthod you've described above, but when I come to use

convert(varchar, DECRYPTBYKEY(EncryptedData))

my values are showing a null.

I've seen in alot of forums comments where people have mistakenly truncated data and resulting in null values returned, so I've been using varchar(max) / varchar(8000) and varbinary(8000) to define columns. My decrypt values are still returning null.

I've also tried using nvarchars.

Are there any other setting I should be looking at away from the encrypt / decrypt query?

Using your example, if I did the subquery senario below, I get a decrypted value back:

Open symmetric key SK1
Decryption by certificate C1

select *, convert(varchar, DECRYPTBYKEY(EncryptedCCNumber)) as'Decrypted CC Number'
from
(
select *, ENCRYPTBYKEY(key_guid('SK1'), CCNumber) as EncryptedCCNumber from TestEncryption
)

Close symmetric key SK1

But if I create a table with encrypted values then select from with OPEN/ CLOSE, I get NULL values returned.

Thanks.