Encryption is used to ensure the security of sensitive data, such as user passwords and bank card passwords.

Encryption is used to ensure the security of sensitive data, such as user passwords and bank card passwords.

1. The simplest encryption method is to encrypt a single character or column and use HashBytes to return the input hash value. Syntax: HashBytes ('encryption type', 'encrypted text') returns a maximum value of varbinary (8000)

The following example assumes that the AdventureWorks sample database has been installed.
-- Create a sample table and fill in data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t1 from Sales. CreditCard;
Go
-- Add a column to store the encrypted Hash value of the card number.
Alter table dbo. t1 add md2Number varbinary (20) null;
Alter table dbo. t1 add md4Number varbinary (20) null;
Alter table dbo. t1 add md5Number varbinary (20) null;
Alter table dbo. t1 add shaNumber varbinary (20) null;
Alter table dbo. t1 add sha1Number varbinary (20) null;
Go
-- Encryption
Update dbo. t1 set
Md2Number = HashBytes ('md2', CardNumber ),
Md4Number = HashBytes ('md4', CardNumber ),
Md5Number = HashBytes ('md5', CardNumber ),
ShaNumber = HashBytes ('sha', CardNumber ),
Sha1Number = HashBytes ('sha1', CardNumber );
Go
Select top (10) * from dbo. t1;

Point: the sha and sha1 encryption results are the same in the result. HashBytes encryption is irreversible, that is, it can only be modified, but cannot retrieve the pre-encryption value.

A varchar character. It is different from the Hash value returned by a nvarchar character (reason: it occupies different bytes), for example:
Select HashBytes ('md5', 'SQL Server 100') as md5varchar, HashBytes ('md5', n' SQL Server 100') as md5nvarchar; 2. the advantages of phrase encryption can be decrypted, and the security is higher than Hash encryption. If you forget the phrase, it will not be saved.
-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t2 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t2 add PhraseNumber varbinary (256) null;
Go
-- Encryption
Update dbo. t2 set PhraseNumber = EncryptByPassPhrase ('phrasetext ', CardNumber, 1, convert (varbinary (30), CreditCardID ));
Go
-- View the encrypted result
Select * from dbo. t2;
Go
-- Decryption
Select CreditCardID, CardNumber, PhraseNumber,
Convert (nvarchar, DecryptByPassphrase ('phrasetext ', PhraseNumber, 1, convert (varbinary (30), CreditCardID) as "Decrypted card number"
From dbo. t2;
Go 3: Certificate public key encryption advantages: the password is more complex and secure. Disadvantages: high overhead, and the certificate must be maintained.
-- Create a self-signed certificate. This example describes encryption. For more information about other certificate types, see related articles.
Create certificate cer001
Encryption by password = n '! @ # $ % ^ &*'
With subject = 'cer _ encryption ',
Start_date = '123 ',
Expiry_date = '20140901 ';
Go

-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t3 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t3 add CerEncryptionENumber varbinary (256) null;
Go
-- View unencrypted results
Select * from dbo. t3;
Go
-- Encryption
Update dbo. t3 set CerEncryptionENumber = EncryptByCert (Cert_ID ('cer001'), CardNumber );
Go
-- View the encrypted result
Select * from dbo. t3;
Go
-- View the decrypted result
Select top (10) CardNumber as 'original card No ',
Convert (nvarchar (30), DecryptByCert (Cert_Id ('cer001'), CerEncryptionENumber, n '! @ # $ % ^ & * ') As 'decrypted'
From dbo. t3;
Go 4 asymmetric key encryption, decryption disadvantage: high overhead, not used for processing large datasets
Create asypolicric key asym_key_001
With algorithm = RSA_2048
Encryption by password = n '! @ # $ % ^ &*';
Go
-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t4 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t4 add AsymKeyEncryptionENumber varbinary (256) null;
Go
-- View unencrypted results
Select * from dbo. t4;
Go
-- Encryption
Update dbo. t4 set AsymKeyEncryptionENumber = EncryptByAsymKey (AsymKey_ID ('asym _ key_001 '), CardNumber );
Go
-- View the encrypted result
Select * from dbo. t4;
Go
-- View decrypted data
Select CardNumber, convert (nvarchar (30), DecryptByAsymKey (AsymKey_Id ('asym _ key_001 '), AsymKeyEncryptionENumber, n '! @ # $ % ^ &*'

) As DecryptedData
From dbo. t4;
Go 5 symmetric key encryption: the decryption algorithm must be in the operating system. The encryption speed is fast and the decryption syntax is simple.
-- Use the DESX algorithm to create a symmetric key named sym_key_001, and then use the certificate cer001 to encrypt the new key.
Create policric key sym_key_001
With algorithm = DESX
Encryption by certificate cer001;
Go
-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t5 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t5 add SymKeyEncryptionENumber varbinary (256) null;
Go
-- View unencrypted results
Select * from dbo. t5;
Go
-- Encryption
Open encryption Ric key sym_key_001 decryption by certificate cer001 with password = n '! @ # $ % ^ &*';
Update dbo. t5 set SymKeyEncryptionENumber = EncryptByKey (Key_GUID ('sym _ key_001 '), CardNumber );
Go
-- View the encrypted result
Select * from dbo. t5;
Go
-- View decrypted data
Open encryption Ric key sym_key_001 decryption by certificate cer001 with password = n '! @ # $ % ^ &*';
Select CardNumber, SymKeyEncryptionENumber, convert (nvarchar (30), DecryptByKey (SymKeyEncryptionENumber) as DecryptedData
From dbo. t5;
Go