在MSSQL中加密Varbinary

Question

由于将来我们将在数据库中存储一些敏感文件，因此我们希望对它们进行加密。我们仍然在Filestream和Varbinary之间犹豫不决，尽管我们倾向于使用Varbinary。我发现这个链接很有帮助：file stream vs local save in sql server?

我现在看到的唯一方法是将Varbinary转换为字符串，并使用以下函数对其进行加密：Encrypting & Decrypting a String in C#

有没有更好的可能性？

Answer 1

VARBINARY类型类似于VARCHAR类型，但它存储二进制字节字符串，而不是非二进制字符串。

尝试使用SQL Server证书和非对称密钥，这是使用证书以及私钥和公钥加密数据的最安全方法。如需进一步说明，请访问以下链接：

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

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

Answer 2

--选项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];

--选项2(包括证书级别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];