SQL Server simple encryption and decryption "go"

Objective:

Before SQL Server 2005 and SQL Server 2008. If you want to encrypt sensitive data, such as financial information, wages, or social Security numbers, you must use an external application or algorithm. SQL Server 2005 introduces the ability to encrypt in-built data, using a combination of certificates, keys, and system functions to complete.

Similar to digital certificates. SQL Server certificates include both public and private keys, both of which are used to encrypt and decrypt data. SQL Server also has the ability to create asymmetric keys and symmetric key objects. Asymmetric keys (asymmetric key) are similar to certificates, public keys are used to encrypt databases, and private keys are used to decrypt data. Both asymmetric keys and certificates provide strong encryption strength. However, there is more performance overhead in completing the complex encryption | decryption process. A solution that is more suitable for encrypting large amounts of data and having a lower performance cost is a symmetric key (symmetric key), which is a key that encrypts and decrypts the same data.

SQL Server allows these encryption capabilities to be placed in the encryption hierarchy. When SQL Server is installed, a server-level certificate named Service Master key is created in the database master and is implicitly bound to the SQL Server service account login name. The Service master key is used to encrypt all other database certificates and to create keys in the instance of SQL Server. Alternatively, you can create a database master key in the user database, which can be used to encrypt database certificates and keys.

In SQL Server 2008, Microsoft introduced transparent data encryption (TDE), which encrypts the entire database without needing to modify any applications that access it. Data, log files, and associated database backups are encrypted. If the database is stolen, there is no database encryption key (DEK) to access the data.

I. Encryption via passphrase (PassPhrase)

For emergency data encryption that does not involve certificates and keys, data can be encrypted and decrypted directly based on the user-supplied password. The pass phrase (PassPhrase) is a password that allows spaces to exist. This passphrase is not stored in the database, and thus means that it is not "cracked" using the stored system data. At the same time, you can use spaces to create a long, easy-to-remember sentence to encrypt and decrypt sensitive data.

The pair of functions we need to know is

Encryptbypassphrase ( http://technet.microsoft.com/zh-cn/library/ms190357.aspx )

Decryptbypassphrase ( http://technet.microsoft.com/zh-cn/library/ms188910.aspx )

This pair of functions must use the same parameters.

Let's look at an example:

--------Cryptographic Functions-----------
CREATE FUNCTION dbo. Encryptbypassphrasepwd (@password nvarchar (50))
RETURNS varbinary (max)
As
BEGIN
DECLARE @pwd varbinary (max) SELECT @pwd = Encryptbypassphrase (
' 1234567 ',
@password)
Return @pwd
END
---------decryption Function----------
CREATE FUNCTION dbo. DECRYPTBYPASSPHRASEPWD (@password varbinary (max))
RETURNS nvarchar (max)
As
BEGIN
declare @pwd nvarchar (max) SELECT @pwd =cast (decryptbypassphrase (' 1234567 ', @password) as nvarchar (max))
Return @pwd
END Note: 123456 is a passphrase used to generate a symmetric key SELECT dbo. Encryptbypassphrasepwd (' test11 ') as result
SELECT dbo. Decryptbypassphrasepwd (0x010000004779c35f96dacc0ec6a8c518e186d203b1a336ee5b8a51b4271b54f56f516ece) as result

Supreme Arrow God: http://www.zhizunjianshen.com/  

SQL Server simple encryption and decryption "go"