Encryption and decryption in SQL

Question

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 11-Jan-22 0:35am

Rain Nature

Updated 11-Jan-22 2:59am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2022-01-11T02:26:00

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

_Asif_ · Accepted Answer · 2022-01-11T02:59:00

Solution 2

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.

Posted 11-Jan-22 2:59am

_Asif_

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?

Maciej Los 11-Jan-22 10:31am

Up-vote comments? Never (as long as i'm CP member).

CHill60 11-Jan-22 11:38am

You're possibly thinking of messages in the forums compared to comments in QA