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

How to use certificate to encrypt data in SQLServer2005, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

How to encrypt data with certificates in SQLServer2005

In MySQL database, encode and decode functions can encrypt some columns of the database table with the key set by themselves to achieve the purpose of data security. It can also be implemented with certificates in SQLServer2005.

The following article describes how to implement:

-- create a master key

CREATEMASTERKEYENCRYPTIONBYPASSWORD='fengjicai'

-- create a certificate boyi with password fengjicai

CREATECERTIFICATEboyi

ENCRYPTIONBYPASSWORD='fengjicai'

WITHSUBJECT='boyi55testcertificate'

Start_date='02/08/2008'

EXPIRY_DATE='02/08/2009'

GO

-- create a test table. The name field is the column to be encrypted, and the data type is varbinary.

-- because the encrypted data is binary

CreatetabletestB (idintidentity (1), namevarbinary (5000))

Write a piece of test data to the test table

InsertintotestB (name)

Selectencryptbycert (cert_id ('boyi'),' boyi55')

/ *

Attached: usage of encryptbycert function

EncryptByCert (certificate_ID, {'cleartext' | @ cleartext})

Certificate_ID

ID of the certificate in the database.

Cleartext

The data string that will be encrypted with a certificate.

@ cleartext

A variable of type nvarchar, char, wchar, varchar, or nchar that contains data that will be encrypted using the public key of the certificate.

How to encrypt data with certificates in SQLServer2005

Return type

Varbinary with a maximum size of 8000 bytes.

* /

-- extract encrypted data

SELECTid,cast (DecryptByCert (Cert_Id ('boyi'))

Name,N'fengjicai') asvarchar (20)) fromtestb

/ *

Note: the return type of the decryptbycert function is varbinary, so convert the binary to the original type.

Name is the field name, and it must also be a binary type here. If you don't want to make the corresponding conversion.

Attached: usage of decryptbycert function

DecryptByCert (

Certificate_ID

{'ciphertext' | @ ciphertext}

[, {'cert_password' | @ cert_password}]

)

Certificate_ID

ID of the certificate in the database.

Ciphertext

A string of data encrypted with the public key of the certificate.

@ ciphertext

A variable of type varbinary that contains data that has been encrypted with a certificate.

Cert_password

The password used to encrypt the private key of the certificate. Must be a Unicode character.

@ cert_password

A variable that contains the password used to encrypt the private key of the certificate. Must be a Unicode character.

* /

Summary:

This encryption is relatively secure, and the binary data after each encryption is not the same. The only disadvantage is that it consumes a lot of resources and is not suitable for deployment in an environment with a large amount of data. In addition, the amount of encrypted data is relatively large, if the encrypted content is too much, the growth of the database will be more obvious.

This is the answer to the question about how to use certificates to encrypt data in SQLServer2005. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.