' END AS Integrity Check FROM Asymmetric Temp; -- add a '0' to the end of the plaintext UPDATE Asymmetric Temp SET Plain Text = Plain Text '0'; -- check the integrity of the data stored by checking the -- signature against the plaintext SELECT Plain Text, CONVERT(NVARCHAR(100), Decrypt By Asym Key(Asym Key_ID('Another Asymmetric Key'), Cipher Text, N'Very Very Strong Password')) AS Decrypted, CASE WHEN Verify Signed By Asym Key(Asym Key_Id('Another Asymmetric Key'), Plain Text, Signature) = 1 THEN N'The data has not been changed.' ELSE N'The data has been modified!

' END AS Integrity Check FROM Asymmetric Temp; -- delete key and table DROP ASYMMETRIC KEY Another Asymmetric Key; DROP TABLE Asymmetric Temp; to hold the digital signatures for each record.

Open a New Query and execute the following script to create the necessary database master key, the certificate, and the symmetric key.

updating database asp-71

This time the function outputs 0, indicating that the signature is no longer valid and, therefore, that the data has been changed.

Note that in a real world application we would more than likely encrypt the plaintext first (not store it directly), and then sign the encrypted ciphertext.

Unfortunately, as one might guess from the previous sections, SQL Server Management Studio is lacking when it comes to wizard-based creation of keys and certificates.

Therefore, it is impossible for a developer to completely rid him or herself from writing a little T-SQL.

993e7$$'; -- Create a certificate CREATE CERTIFICATE Customer Data Certificate WITH SUBJECT = 'Certificate used to encrypt Customer Data Key'; -- Create a symmetric key for encrypting customer data, and -- encrypt that key -- using the certificate Customer Data Certificate CREATE SYMMETRIC KEY Customer Data Key WITH ALGORITHM = DESX ENCRYPTION BY CERTIFICATE Customer Data Certificate; that will be used to encrypt and decrypt the customer data.

Note that we did not specify a password for the certificate. In Design mode, add a Sql Data Source to the blank page and click on the Configure Data Source link in the control's Smart Tag.

We specify the algorithm to use to create the key (in the example above the 2,048-bit key veriosn of the RSA algorithm is used), and supply the password to use to encrypt the private key created. With this function we must supply the key's identifier, the ciphertext to be decrypted, and the password used when the key was created.

SQL Server 2005 can create its own certificates or use those created by a trusted thrid party.

If the data has not been changed since its signing, the function will output the value 1, otherwise 0. Next, we update the plaintext by appending an extra "0" to the string, thereby modifying the data.