Click here to Skip to main content
15,569,774 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to encrypt and decrypt a integer field in microsoft sql server 2018
using store procedure of encrypt while inserting and decrypt while selecting

What I have tried:

Select num From test Where Uid=1
SELECT
CONVERT( sql_variant, DecryptByKey(num)) AS 'Limit'
FROM test;
Posted
Updated 11-Jan-22 3:59am
v2

If you look at the documentation at ENCRYPTBYKEY (Transact-SQL) - SQL Server | Microsoft Docs[^] section B it gives this example
SQL
USE AdventureWorks2012;  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
    DECRYPTION BY CERTIFICATE Sales09;  
  
-- Encrypt the value in column CardNumber with symmetric   
-- key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'),   
    CardNumber, 1, CONVERT( varbinary, CreditCardID) );  
GO
What is not clear without looking at the database is that CreditCardID is an int (see Sales.CreditCard - AdventureWorks[^] )

I.e. convert your int to a varbinary in order to Encrypt it. Convert the varbinary returned from DECRYPTBYKEY back to an int on select
 
Share this answer
 
Comments
Rain Nature 11-Jan-22 8:51am    
thankyou sir, this coding good but i want to encrypt while inserting
CHill60 11-Jan-22 9:08am    
This is only an example, the actual encryption bit is the same whether you are inserting or updating!
Maciej Los 11-Jan-22 9:22am    
5
You can find a very detailed Article with a good example on how to use EncryptionByKey and DecryptionByKey below

https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/[^]

The article also explains how to use system views to verify the key presences and access rights to do Encryption.
 
Share this answer
 
Comments
CHill60 11-Jan-22 9:09am    
Good article - unfortunately doesn't tell the OP how to encrypt an integer field
_Asif_ 11-Jan-22 9:18am    
I don't think type is an issue here, Check this link. https://stackoverflow.com/questions/53984800/how-to-encrypt-and-decrypt-integer-data-type-column-in-sql-server-using-symmetri
CHill60 11-Jan-22 12:41pm    
"I don't think type is an issue here" - it's not an 'issue' per se. The OP just needs to cast or convert the int to a varbinary in order to Encrypt it
Maciej Los 11-Jan-22 9:22am    
5
_Asif_ 11-Jan-22 9:26am    
Thanks. If I remember correctly we could have upvoted the comments previously, now we can't?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900