Encrypt Varbinary in MSSQL

Question

Since we will store some sensitive files in the database in the future, we would like to encrypt them. We are still indecisive between Filestream and Varbinary, although we tend to go with Varbinary. I found this link helpful: file stream vs local save in sql server?

The only way I can see right now, is to convert the Varbinary into a string and encrypt it for instance with the following function: Encrypting & Decrypting a String in C#

Are there any better possibilities?

No, there is absolutely *no* need to convert the binary data into a string to encrypt and decrypt. Anything string-based is going to have a "convert the string to binary data first" step - just remove that. — Jon Skeet, Oct 11 '17 at 06:40
Encryption isn't a magic wand that you wave and it makes things secure. *who/what* are you trying to protect this data from? — Damien_The_Unbeliever, Oct 11 '17 at 06:42

score 0 · Answer 1 · answered Oct 11 '17 at 06:43

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings.

Try to use SQL Server Certificates and Asymmetric Keys, It is most secure way to encrypt your data with certificate and private and public keys. For further clarification, You can visit below links:

https://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

https://learn.microsoft.com/en-us/sql/relational-databases/security/sql-server-certificates-and-asymmetric-keys

Wilco · Answer 2 · 2021-05-27T22:34:00.340

-- Option 1

    
    -- add varbinary field to table
    ALTER TABLE [dbo].[enc_test]
        ADD encryptedCol varbinary(128);
        GO  
    
    -- Create cert
    CREATE CERTIFICATE testCert01
       WITH SUBJECT = 'Test',   
       EXPIRY_DATE = '20251031';  
    GO  

    -- Create key
    CREATE SYMMETRIC KEY testKey01   
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE testCert01;  
    GO      

    -- Update table with encrypted value 
    OPEN SYMMETRIC KEY testKey01  
       DECRYPTION BY CERTIFICATE testCert01;    

    UPDATE [dbo].[enc_test]
    SET encryptedCol  
        = EncryptByKey(Key_GUID('testKey01'), 'plain text test');  
    GO      

    -- view Encrypted Column
    SELECT * FROM [dbo].[enc_test];     
    
    -- View Decrypted Column
    OPEN SYMMETRIC KEY testKey01  
      DECRYPTION BY CERTIFICATE testCert01; 

    SELECT *, Convert(varchar, (DECRYPTBYKEY(encryptedCol))) 
    FROM [dbo].[enc_test];

-- Option 2 (include cert level pw)


    -- add varbinary field to table
    ALTER TABLE [dbo].[enc_test]
        ADD encryptedCol varbinary(128);
        GO  

    -- Create PW protected cert
    CREATE CERTIFICATE testCert01
        ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
       WITH SUBJECT = 'Test',   
       EXPIRY_DATE = '20251031';  
    GO  

    -- Create KEY
    CREATE SYMMETRIC KEY testKey01   
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE testCert01;  
    GO      

    -- Update table with encrypted value 
    OPEN SYMMETRIC KEY testKey01  
       DECRYPTION BY CERTIFICATE testCert01 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  

    UPDATE [dbo].[enc_test]
    SET encryptedCol  
        = EncryptByKey(Key_GUID('testKey01'), 'plain text test');  
    GO      

    -- view Encrypted Column
    SELECT * FROM [dbo].[enc_test];     
    
    -- View Decrypted Column
    OPEN SYMMETRIC KEY testKey01  
      DECRYPTION BY CERTIFICATE testCert01 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';   

    SELECT *, Convert(varchar, (DECRYPTBYKEY(encryptedCol))) 
    FROM [dbo].[enc_test];

Encrypt Varbinary in MSSQL

2 Answers2