Shulou(Shulou.com)05/31 Report--

This article mainly explains "the usage of SQL Server encryption function". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the usage of SQL Server encryption function.

Encryption in SQL Server is hierarchical, and each upper layer provides protection for the lower layer.

Example: / * * SMK (Service Master Key) is generated during SQL Server installation and protected by Windows DPAPI (Data Protection API) * * /

/ * * create database-level DMK (Database Master Key) protected by SMK * * / create master key encryption by password=N'Passw0rd' go

/ * * symmetric encryption algorithms supported by DMK protection for encrypted objects in the database: DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256 asymmetric encryption algorithm: RSA_512 | RSA_1024 | RSA_2048 avoid using RC, DESX algorithms, which will be deleted after 2014 * /

-1. Create an asymmetric key.

Create asymmetric key asyc_key_enc with algorithm=RSA_1024 encryption by password=N'Pass@word' go

-- 2. Create a symmetric key.

Create symmetric key symc_key_enc with algorithm=Triple_DES encryption by password=N'Pass@word' go

-3. Create a certificate. Certificates can also be protected in other ways

Create certificate cert_ENC with subject='certificate for ENC',expiry_date='20990101' go

-- 4. Symmetric keys can be protected by encryption in the above three ways

-4.1 encrypted by asymmetric key

Create symmetric key symc_key_enc_byAsyc with algorithm=AES_128 encryption by asymmetric key asyc_key_enc go

4.2 encrypted by symmetric key

Open symmetric key symc_key_enc decryption by password=N'Pass@word'; create symmetric key symc_key_enc_bySymc with algorithm = DES encryption by symmetric key symc_key_enc go

-4.3 encrypted by certificate

Create symmetric key symc_key_enc_byCert with algorithm = AES_128 encryption by certificate cert_ENC go

/ * * column-level data encryption and decryption. MSSQL provides the following 4 pairs of encryption / decryption functions to encrypt column data EncryptByCert () and DecryptByCert ()-encrypt and decrypt data using certificates EncryptByAsymKey () and DecryptByAsymKey ()-encrypt and decrypt data with asymmetric keys EncryptByKey () and DecryptByKey ()-encrypt and decrypt data with symmetric keys EncryptByPassphrase () and DecryptByPassphrase ()-produce with password fields Generate symmetric keys to encrypt and decrypt data Note: encrypted and decrypted data Must be varbinary type * * /

Take ENCRYPTBYKEY as an example, others are more or less the same.

-- encrypt and decrypt the IDN

Create table tb (IDN int,Name varchar (20)); insert into tb values (123456789), (090807001)), (336655789) go

-- A new column, Ency_IDN, stores encrypted data, which is encrypted using a symmetric key symc_key_enc_byAsyc previously encrypted by an asymmetric key

Alter table tb add Ency_IDN varbinary; go open symmetric key symc_key_enc_byAsyc decryption by asymmetric key asyc_key_enc with password=N'Pass@word'; update tb set Ency_IDN=ENCRYPTBYKEY (KEY_GUID ('symc_key_enc_byAsyc'), CONVERT (Varbinary,IDN));-- convert to varbinary close symmetric key symc_key_enc_byAsyc before encryption-- explicitly turn off symmetric key go

-- decrypt encrypted column data

Open symmetric key symc_key_enc_byAsyc decryption by asymmetric key asyc_key_enc with password=N'Pass@word'; select IDN,Ency_IDN,convert (int,DECRYPTBYKEY (Ency_IDN)) as Decr_IDN from tb; close symmetric key symc_key_enc_byAsyc-explicitly closes symmetric key go1

At this point, I believe you have a deeper understanding of "the use of SQL Server encryption function". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.