Last post Aug 06, 2019 07:31 AM by PatriceSc
Aug 06, 2019 05:14 AM|cenk1536|LINK
I am using SQL Server 2016 and I wonder if there is a way to encrypt/decrypt a column in a table? Let's say I have a table and in this table, I have serials column. I would like to encrypt serials when I save it into this table and I want to decrypt serials
when I query this column.
Thanks in advance.
Aug 06, 2019 07:22 AM|Yang Shen|LINK
Threr are many ways in SQL Server to encrypt and decrypt data. Here are some of them:
1. Use CONVERT(VARBINARY(512), column name)
This function is used to convert column data into VARBINARY. However, this method can't actually protect data and only avoids the role of directly seeing sensitive data in the process of browsing data.
SELECT CONVERT(VARBINARY(512), IName) as VARBINARYName FROM Items
The result of this query:
2. Symmetric key
This the popular method used in SQL to encrypt and decrypt data, thus you can find many articles about it.
This method is more suitable for large data volume. Because the symmetric key needs fewer resources.
3. ASYMMETRIC KEY
Encrypted and decrypted data for higher security level. It consumes more resources.
For this method, you can refer to this article
4. Use CERTIFICATE and EncryptByCert
Similar to asymmetric key. Below demo is searched from online:
CREATE CERTIFICATE certKey123--CERTIFICATE
ENCRYPTION BY PASSWORD='P@ssw0rd'--PASSWORD
WITH SUBJECT='Address Certificate',--Describtion
EXPIRY_DATE='2013/06/18' ;--Expire date
--Encryption with Certificate
SELECT *,ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1)) cyberAddress
--Add a new column to store encrypted data
ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX )
--update encrypted data
SET AddressLine4=ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1))
SELECT AddressID,CONVERT(VARCHAR(MAX ),CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('certKey123'),AddressLine4,N'P@ssw0rd'))) DecryAddressu) FROM Person.Address
Suitable for general data encryption and decryption.
You can refer to: Here.
Aug 06, 2019 07:31 AM|PatriceSc|LINK
You have multiple ways to do that. See maybe https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017