Using Pass Phrases to Encrypt Data

A password that allows for spaces can be referred to as a pass phrase. The benefit of pass phrases is that you can make them meaningful and easy to remember. Instead of creating and managing encryption keys or certificates in your database server, you can encrypt data using only a pass phrase.The ENCRYPTBYPASSPHRASE statement uses the supplied pass phrase to generate a symmetric key, which is used to perform the actual data encryption. No key management is required, as the key will be recreated each time the same pass phrase is supplied.The common syntax of the ENCRYPTBYPASSPHRASE statement is as follows:

ENCRYPTBYPASSPHRASE ('PASSPHRASE', 'PLAINTEXT')

In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key. PLAINTEXT specifies the data to be encrypted. No permissions are required to run the ENCRYPTBYPASSPHRASE statement.

The following syntax encrypts the string using the supplied pass phrase:

SELECT ENCRYPTBYPASSPHRASE('SQL Server 2005 Pass Phrase Encryption', 'pass phrase encryption test')

Here are the results:

0x01000000B0FA66E0152FB0B655B23439904E36F3ED5B758618BEED0F2A2BF918C6CF9DF685BC2A60A
AD5E81D660BA5A396D1CA89

As mentioned earlier, the preceding results will differ from what you receive on your SQL Server.To decrypt data, you can use the DECRYPTBYPASSPHRASE statement.The general syntax of this statement is as follows:

DECRYPTBYPASSPHRASE ('PASSPHRASE', 'CIPHERTEXT')

In this statement, PASSPHRASE specifies the data string to be used to derive a decryption key. CIPHERTEXT specifies the data to be decrypted. Similar to the ENCRYPTBYPASSPHRASE statement, no permissions are required to execute the DECRYPTBYPASSPHRASE statement.The following syntax uses the DECRYPTBYPASSPHRASE statement to decrypt the previously encrypted data, and converts it into the human readable varchar format:

SELECT CAST (DECRYPTBYPASSPHRASE('SQL Server 2005 Pass Phrase Encryption',
0x01000000B0FA66E0152FB0B655B23439904E36F3ED5B758618BEED0F2A2BF918C6CF9DF685BC2A60A
AD5E81D660BA5A396D1CA89) AS varchar)

Note that you should substitute the cipher text in the preceding statement with the cipher text that you obtained from the earlier ENCRYPTBYPASSPHRASE statement. Here is what the results of running the preceding statement will look like:

pass phrase encryption test

The encryption algorithm and key length used by pass phrase encryption have not been formally documented by Microsoft. Because of this, it is recommended that you do not use this encryption mechanism to encrypt sensitive data.

Flaws within Microsoft Object Encryption

The object encryption used by Microsoft is weak, and there are publicly available scripts that can successfully decrypt the objects. Further, at run-time, SQL Server internally decrypts the object and SQL Profiler can be used to capture object logic in plain text form. Due to this, object encryption should not be used to encrypt sensitive information, and you should not embed key or certificate passwords or pass phrases in SQL Server objects encrypted using objectbased encryption.