SQL Server encryption and decryption

Time:2021-3-4

1、 Passphase encryption

1.1 summary

1) For emergency data encryption without certificate and key, the data can be encrypted and decrypted directly based on the password provided by the user.

2) A pass phrase is a password that allows spaces. This passphase will not be stored in the database, which means that it will not be “cracked” by the system data stored. At the same time, space can be used to create a long, easy to remember sentence to encrypt and decrypt sensitive data.

3) The data column types supported by passphase are nvarchar, varchar, nchar, char, varbinary and binary.

1.2 encryption function

CREATE FUNCTION dbo.EncryptPass(@EnPass VARCHAR(32))
    RETURNS VARBINARY(MAX)
AS  
BEGIN
    DECLARE @Result VARBINARY(MAX)
    SET @Result=EncryptByPassPhrase('HelloWorld',@EnPass)
    RETURN @Result
END

1.3 decryption function

CREATE FUNCTION dbo.DecryptPass(@DePass VARBINARY(MAX))
    RETURNS VARCHAR(32)
AS  
BEGIN 
    DECLARE @Result VARCHAR(32)
    SET @Result=CONVERT(VARCHAR,DecryptByPassPhrase('HelloWorld',@DePass))
    RETURN @Result
END

1.4 result test

SELECT  dbo.EncryptPass ('test string ') as result
SELECT dbo.DecryptPass(0x0100000045FE8873D07BFB747D33D81D1C1E06CA3EC10D718CF603E395D4493BFBFC5683) AS RESULT
SELECT  dbo.DecryptPass ( dbo.EncryptPass ('test string ')) as result

1.5 implementation results

2、 Certificate encryption

2.1 summary

1) Certificates can encrypt and decrypt data in the database.

2) The certificate contains the key pair, the information of the certificate owner, the available start and end expiration dates of the certificate, as well as the public key and the key. The former is used for encryption and the latter is used for decryption.

3) SQL server can generate its own certificate or load it from an external file or assembly. Certificates are easier to transplant than asymmetric keys and can be easily reused in databases.

2.2. Create certificate

CREATE CERTIFICATE TestCert
ENCRYPTION BY PASSWORD='HelloWorld'
WITH SUBJECT='Hello World', START_DATE='2021-01-20', EXPIRY_DATE='2021-12-31'

2.3 encryption function

CREATE FUNCTION dbo.EncryptCert(@EnPass VARCHAR(32))
    RETURNS VARBINARY(MAX)
AS  
BEGIN
    DECLARE @Result VARBINARY(MAX)
    SET @Result=EncryptByCert(Cert_ID('TestCert'),@EnPass)
    RETURN @Result
END

2.4 decryption function

CREATE FUNCTION dbo.DecryptCert(@DePass VARBINARY(MAX))
    RETURNS VARCHAR(32)
AS  
BEGIN 
    DECLARE @Result VARCHAR(32)
    SET @Result=CONVERT(VARCHAR,DECRYPTBYCERT(Cert_ID('TestCert'),@DePass,N'HelloWorld'))
    RETURN @Result
END

2.5 result test

SELECT  dbo.EncryptCert ('test string ') as result
SELECT dbo.DecryptCert(0x836021834D8D25E4DAD0CD26767A77A7FFF9CE9BDCA7BDE72E418118DB58DFB8AF6325B7A9D1D6C2D5D43800120A5C5D212BC4A645D10A065A147C5E97B419DAF6DC203598E376AE4A520B45D145F2C2A6BAE23B49AD3EA45BDAAC1FBD78E8402195DDA0842CA5A89942D03C5D7B82F7E8241D8BD698115C78CEBD5AC0066C6C) AS RESULT
SELECT  dbo.DecryptCert ( dbo.EncryptCert ('test string ')) as result

2.6 implementation results