locked
How to encypt/decrypt a column? RRS feed

  • Question

  • User-1104215994 posted

    Hello,

    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.

    Tuesday, August 6, 2019 5:14 AM

All replies

  • User-719153870 posted

    Hi cenk1536,

    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.

    For example:

    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.

    implement-column-level-encryption-decryption-in-sql-server-2016

    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
    START_DATE='2012/06/18',--Effective date
    EXPIRY_DATE='2013/06/18' ;--Expire date
    GO
    --Encryption with Certificate
    SELECT *,ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1)) cyberAddress
    FROM Person.Address    
        
    --Add a new column to store encrypted data
    ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX )
    
    --update encrypted data
    UPDATE Person.Address
    SET AddressLine4=ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1))
    
    --Decrypt
    SELECT AddressID,CONVERT(VARCHAR(MAX ),CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('certKey123'),AddressLine4,N'P@ssw0rd'))) DecryAddressu) FROM Person.Address

    5. EncryptByPassPhrase

    Suitable for general data encryption and decryption.

    You can refer to: Here.

    Best Regard,

    Yang Shen

    Tuesday, August 6, 2019 7:22 AM
  • Tuesday, August 6, 2019 7:31 AM