Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

Published: Wednesday, February 28, 2007

A Multi-Part Article on Cryptographic Systems and Encrypting Database Data

This article is the third and final piece of a three-part series exploring cryptographic systems and encrypting data within a Microsoft SQL Server 2005 database. It explores techniques for encrypting data in a Microsoft SQL Server 2005 database using asymmetric encryption. The three parts proceed as follows: An Overview of Cryptographic Systems and Encrypting Database Data Using Symmetric Encryption in a SQL Server 2005 Database Using Asymmetric Encryption and Digital Signatures in a SQL Server 2005 Database

Introduction
Two previous article of ours, An Overview of Cryptographic Systems and Encrypting Database Data and Using Symmetric Encryption in a SQL Server 2005 Database, explored cryptosystem fundamentals and looked at encryption support in Microsoft SQL Server 2005. We compared and contrasted symmetric and asymmetric encryption, examined SQL Server 2005's key management, and saw examples of how to use T-SQL commands to create symmetric keys and to use these keys to encrypt and decrypt data.

In this article - the final one of the series - starts with a look at the T-SQL commands for performing asymmetric encryption and decryption. Next, we discuss using digital signatures as a means for ensuring the integrity of the encrypted data. This article concludes with an ASP.NET 2.0 website example that ties together the lessons learned throughout this article series. Specifically, the database used by this ASP.NET application stores customer information with the customer's credit card information encrypted. An ASP.NET page provides a means to view the sensitive information in plaintext as well as a means to add new customers to the database with the credit card information properly encrypted. Read on to learn more!

T-SQL Support for Asymmetric Encryption
In Using Symmetric Encryption in a SQL Server 2005 Database we looked at the T-SQL support for symmetric encryption. Let us now take a look at how Microsoft SQL Server 2005 allows for the use of asymmetric encryption and its constituent functions. The key functions are summarized in Table 2 [5].

Table 2: Asymmetric Functions

Just as there is a system catalog view for the symmetric keys in a database, there is one for asymmetric keys as well. The following statement will list information about the asymmetric keys in the current database:

SELECT * FROM sys.asymmetric_keys;

Also, SQL Server 2005 provides functions for asymmetric encryption that are used in a similar manner as the symmetric cryptographic ones were. The following example illustrates using the EncryptByAsymKey and DecryptByAsymKey functions for performing asymmetric encryption and decryption. Also shown is the AsymKey_ID function, which returns the ID for a particular asymmetric key.

-- use the database tempdb USE tempdb; -- create symmetric key 'SecureAsymmetricKey' -- using the 2048-bit RSA encryption algorithm -- and encypt the key using the password -- 'AnotherStrongPassword' CREATE ASYMMETRIC KEY SecureAsymmetricKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = N'AnotherStrongPassword'; -- create temp table for inserting data CREATE TABLE AsymmetricTempTable ( Id INT IDENTITY(1,1) PRIMARY KEY, PlainText NVARCHAR(100), CipherText VARBINARY(MAX) ); -- declare and set varible @str to store plaintext DECLARE @str NVARCHAR(100) SET @str = N'Hello RSA 2048'; -- insert data into AsymmetricTempTable INSERT INTO AsymmetricTempTable (PlainText, CipherText) VALUES ( @str, EncryptByAsymKey(AsymKey_ID('SecureAsymmetricKey'), @str) ); -- display data in table SELECT * FROM AsymmetricTempTable; -- display decrypted text SELECT CONVERT(NVARCHAR(100), DecryptByAsymKey(AsymKey_ID('SecureAsymmetricKey'), CipherText, N'AnotherStrongPassword')) AS PlainText FROM AsymmetricTempTable; -- delete key and table DROP ASYMMETRIC KEY SecureAsymmetricKey; DROP TABLE AsymmetricTempTable;

Asymmetric keys must be created before they can be used, and to do that we make use of the CREATE ASYMMETRIC KEY statement. 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. To actually encrypt the plaintext the EncryptByAsymKey function is used and passed the ID of the key via the AsymKey_ID function. Lastly, to decrypt the ciphertext, we use the DecryptByAsymKey function. 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. The Windows operating system comes pre-loaded with a list of trusted certificate authorities (CAs). Each is known as a Trusted Root Certificate Authority. We will examine how certificates can be created using SQL Server 2005 along with how to use their public key to encrypt and its private key to decrypt.

"Self-signed" certificates can be created in T-SQL using the CREATE CERTIFICATE statement. When creating the certificate we set certain properties such as the expriation date, the subject, and the password to use to encrypt the cooresponding private key. If a password is not supplied, the Database Master Key will be used to encrypt the private key (of course you must create one before this can be done). There are several other properties that can be set when creating a certificate, and also several ways to import externally created certificates. See [5] for more information on creating certificates in SQL Server 2005.

The T-SQL syntax for performing encryption and decryption using certificates is very simalarily to the T-SQL used with asymmetric keys. The main difference, syntactically, is that the Cert_ID function is used to return the certificate's identifier from the database (rather than the AsymKey_ID function, which was used in the previous example).

-- use the database tempdb USE tempdb; -- create permanent temp table CREATE TABLE CertificateTempTable ( Id INT IDENTITY(1,1) PRIMARY KEY, PlainText NVARCHAR(100), CipherText VARBINARY(MAX) ); -- crate self signed certificate encrypting the private -- key with the supplied -- password CREATE CERTIFICATE SelfSignedCertificate ENCRYPTION BY PASSWORD = 'CertificateStrongPassword' WITH SUBJECT = 'Self Signed Certificate', EXPIRY_DATE = '12/01/2030'; -- declare and set plaintext to be encrypted DECLARE @str NVARCHAR(100); SET @str = 'Secret information...shhhhhh'; -- insert plaintext and encrypted data into the temp table, -- using the public key of the specified certificate INSERT INTO CertificateTempTable (PlainText, CipherText) VALUES(@str, EncryptByCert(Cert_ID('SelfSignedCertificate'), @str)); -- display data in table SELECT * FROM CertificateTempTable; -- decrypt data and display SELECT CONVERT(NVARCHAR(MAX), DecryptByCert(Cert_Id('SelfSignedCertificate'), CipherText, N'CertificateStrongPassword')) As PlainText FROM CertificateTempTable; -- delete certificate and drop table DROP CERTIFICATE SelfSignedCertificate; DROP TABLE CertificateTempTable;

T-SQL Support for Authentication and Message Integrity
As discussed in An Overview of Cryptographic Systems and Encrypting Database Data, digital signatures can be used to ensure the integrity of a message or data. In particular, a sender can encrypt their message using their private key. Since only the sender has access to their own private key, the recipient can be assured that the message has not been forged or altered en route. Table 3 summarizes those SQL Server 2005 functions used for authentication and message integrity purposes [5].

Table 3: Signing and Signature Verification Function

The following T-SQL sample illustrates using the SignByAsymKey and VerifySignedByAsmKey functions.

-- use the database tempdb USE tempdb -- create symmetric key 'AnotherAsymmetricKey' -- using the 2048-bit RSA encryption algorithm -- and encypt the key using the password -- 'VeryVeryStrongPassword' CREATE ASYMMETRIC KEY AnotherAsymmetricKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = N'VeryVeryStrongPassword'; -- create temp table for inserting data CREATE TABLE AsymmetricTemp ( Id INT IDENTITY(1,1) PRIMARY KEY, PlainText NVARCHAR(100), CipherText VARBINARY(MAX), Signature VARBINARY(MAX) ); -- declare and set varible @str to store plaintext DECLARE @str NVARCHAR(100); SET @str = N'60000'; -- insert data into AsymmetricTemp INSERT INTO AsymmetricTemp (PlainText, CipherText, Signature) VALUES ( @str, EncryptByAsymKey(AsymKey_ID('AnotherAsymmetricKey'), @str), SignByAsymKey(AsymKey_ID('AnotherAsymmetricKey'), @str, N'VeryVeryStrongPassword') ); -- display data in table SELECT * FROM AsymmetricTemp; -- check the integrity of the data stored by checking -- the signature against the plaintext SELECT PlainText, CONVERT(NVARCHAR(100), DecryptByAsymKey(AsymKey_ID('AnotherAsymmetricKey'), CipherText, N'VeryVeryStrongPassword')) AS Decrypted, CASE WHEN VerifySignedByAsymKey(AsymKey_Id('AnotherAsymmetricKey'), PlainText, Signature) = 1 THEN N'The data has not been changed.' ELSE N'The data has been modified!' END AS IntegrityCheck FROM AsymmetricTemp; -- add a '0' to the end of the plaintext UPDATE AsymmetricTemp SET PlainText = PlainText + '0'; -- check the integrity of the data stored by checking the -- signature against the plaintext SELECT PlainText, CONVERT(NVARCHAR(100), DecryptByAsymKey(AsymKey_ID('AnotherAsymmetricKey'), CipherText, N'VeryVeryStrongPassword')) AS Decrypted, CASE WHEN VerifySignedByAsymKey(AsymKey_Id('AnotherAsymmetricKey'), PlainText, Signature) = 1 THEN N'The data has not been changed.' ELSE N'The data has been modified!' END AS IntegrityCheck FROM AsymmetricTemp; -- delete key and table DROP ASYMMETRIC KEY AnotherAsymmetricKey; DROP TABLE AsymmetricTemp;

The above examples starts by creating an asymmetric key and a temporary table to hold the data. This is very similar to the previous exmaples, except this time we add another column named Signature to hold the digital signatures for each record. Next, a string is assigned the value "60000" and is then inserted into the table as plaintext, as encrypted ciphertext, and then finally as a digital signature. To digitally sign the data, we use the SignByAsymKey function, passing the identifier for the asymmetric key, the data to be signed, and the password which we used to encrypt the private key. The function takes the plaintext data and encrypts it using the private key of the asymmetric key specified (this is why the function asks for the password). Next, we display the data to make sure it was inserted correctly and check its integrity using the VerifySignedByAsymKey function.

The VerifySignedByAsymKey function takes as input the identifier for the asymmetric key, the data to compare against, and the signature. If the data has not been changed since its signing, the function will output the value 1, otherwise 0. After this check we see it has not been changed. Next, we update the plaintext by appending an extra "0" to the string, thereby modifying the data. We again check the integrity of the data using the VerifySignedByAsymKey function. 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.

Using SQL Server 2005 Encryption with ASP.NET 2.0
Web developers will no doubt be wanting to see a sample web page showing how exactly one might send and retrieve encrypted data to and from a web page (this section is for you!). The remainder of this article discussed how to create the sample from the group up.

First off, we you will need to create a new database in SQL Server 2005; we called our database SecureDatabase in the following example. Next, you will want to create a table named Customers, with the same schema that is shown in Figure 8. Mark Id as an IDENTITY column.

Figure 8: Customers Table Schema

The values in the CreditCardNum and CreditCardSecurityCode columns will be encrypted using a symmetric key that is encrypted using a "self-signed" certificate.

With the Customers database table in place, we must next create the keys necessary for encryption. 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. Open a New Query and execute the following script to create the necessary database master key, the certificate, and the symmetric key.

USE SecureDatabase; -- Create DMK encrypt using password CREATE MASTER KEY ENCRYPTION BY PASSWORD = '@ujjshskk!!993e7$$'; -- Create a certificate CREATE CERTIFICATE CustomerDataCertificate WITH SUBJECT = 'Certificate used to encrypt CustomerDataKey'; -- Create a symmetric key for encrypting customer data, and -- encrypt that key -- using the certificate CustomerDataCertificate CREATE SYMMETRIC KEY CustomerDataKey WITH ALGORITHM = DESX ENCRYPTION BY CERTIFICATE CustomerDataCertificate;

The above statements create the database master key for SecureDatabase, a certificate named CustomerDataCertificate, and a symmetric key named CustomerDataKey (that is encrypted using the CustomerDataCertificate certificate). It is the symmetric key CustomerDataKey that will be used to encrypt and decrypt the customer data. Note that we did not specify a password for the certificate. This has been done so that we may take advantage of SQL Server 2005's automatic key management system (this was talked about in the section titled "Key Management in SQL Server 2005" in the Using Symmetric Encryption in a SQL Server 2005 Database article). In short, SQL Server will decrypt the symmetric key for us automatically.

Now on to the good stuff! Crank up Visual Studio 2005 and create a new ASP.NET Web Site. In Design mode, add a SqlDataSource to the blank page and click on the Configure Data Source link in the control's Smart Tag. Click on New Connection and selecting the proper SQL Server instance and database name, then click OK and Next (if prompted to add the new connection string to your application configuration file click Next again). For more information on the SqlDataSource control and the Create Data Source wizard, see Accessing and Updating Data in ASP.NET 2.0.

When asked to specify the SELECT statement, opt to return the following columns: Id, FirstName, LastName, Address, and Phone and click Next and then Finish to complete the wizard. Next, drag a GridView on to the page and, from its smart tag, bind it to the SqlDataSource we just created. Also check the box labeled "Enable Selection".

At this point we have a web page that will show a summary of customer information in a grid. When the visitor clicks the Select button for a particular customer, we want to show the encrypted information (but in plaintext, of course). Also, we need to allow new customers to be added to the table.

To accomplish this we will need an additional SqlDataSource, so add another one to the page. This time we will want to create our own custom SQL statement rather than return a subset of columns from an existing table. Therefore, choose the "Specify a custom SQL statement or stored procedure" radio button (see Figure 9) and click Next.

Figure 9: Configuring Data Source

In the text area under the SELECT tab enter the following:

OPEN SYMMETRIC KEY CustomerDataKey DECRYPTION BY CERTIFICATE CustomerDataCertificate; SELECT Id, FirstName, LastName, Address, Phone, CAST(DecryptByKey(CreditCardNum) AS NVARCHAR(MAX)) AS CreditCardNum, CAST(DecryptByKey(CreditCardSecurityCode) AS NVARCHAR(MAX)) AS CreditCardSecurityCode FROM Customers WHERE Id = @Id; CLOSE SYMMETRIC KEY CustomerDataKey;

Next, select the INSERT tab and enter the following script:

OPEN SYMMETRIC KEY CustomerDataKey DECRYPTION BY CERTIFICATE CustomerDataCertificate; INSERT INTO Customers (FirstName, LastName, Address, Phone, CreditCardNum, CreditCardSecurityCode) VALUES (@FirstName, @LastName, @Address, @Phone, EncryptByKey(Key_GUID('CustomerDataKey'), @CreditCardNum), EncryptByKey(Key_GUID('CustomerDataKey'), @CreditCardSecurityCode)); CLOSE SYMMETRIC KEY CustomerDataKey;

Our SELECT statement grabs all the columns of the Customers table (decrypting each row using the symmetric key CustomerDataKey) for a particular customer (namely, one whose Id value equals the value of the supplied @Id parameter). The INSERT statement adds a record into the Customers table (encypting the sensitive data) using the parameters it is passed for each attribute.

Since we included a parameter in the SELECT statement, the next screen in the Configure Data Source wizard prompts us for this parameter's source. Recall that we enabled selection in the GridView, so we can populate the @Id parameter with the currently selected GridView row value. Thus, under Parameter Source select Control and under ConrtolID choose the GridView control's ID ("GridView1"). Click Next and then Finish to complete the wizard. Last by not least, we need to add a FormView or DetailsView conrtol to the page (choosing "SqlDataSource2" as the data source). Moreover, you'll need to configure the FormView or DetailsView to provide inserting support. In fact, you may find it helpful to use two DetailsView controls - one that is used solely for inserting new customers and another for showing the selected customer's details. For the one used for inserting you can have the inserting interface always displayed by setting its DefaultMode property to Insert.

Additionally, we need to instruct the GridView to "refresh" when a new record is added to the Customers table via the FormView or DetailsView control. To accomplish this, create an event for the DetailsView or FormView's ItemInserted event and, from there, call the GridView's DataBind() method (like GridView1.DataBind()).

When visiting this page through a browser for the first time - when there are no customers in the database - you should see

Figure 10: Inserting Encrypted Data through ASP.NET

Now let's see if this thing really works! Enter some data into the text boxes and click Insert. Doing so will pass the inputs as parameters to SqlDataSource2 and it should then insert a row containing the entered data into our database table Customers. Afterwards, the GridView should refresh and now include the just-added customer information (thanks to the ItemInserted event handler re-binding the GridView to its data source). We can then either insert more rows, or select rows from the GridView to view their details. When a row is indeed selected, the Id of that row is passed to SqlDataSource2 and used that to select the individual row (decrypting the credit card information)

Figure 11 shows the web page after a couple of customers have been added.

Figure 11: Ouput After Two Customers Have Been Added

Finally, return to SQL Server Management Studio to see that the encrypted data has, in fact, been stored correctly in the Customers database table. Open a New Query and execute the following T-SQL code:

USE SecureDatabase; SELECT CreditCardNum, CreditCardSecurityCode FROM Customers; OPEN SYMMETRIC KEY CustomerDataKey DECRYPTION BY CERTIFICATE CustomerDataCertificate; SELECT CAST(DecryptByKey(CreditCardNum) AS NVARCHAR(MAX)) AS CreditCardNum, CAST(DecryptByKey(CreditCardSecurityCode) AS NVARCHAR(MAX)) AS CreditCardSecurityCode FROM Customers; CLOSE SYMMETRIC KEY CustomerDataKey;

Figure 12: The Encrypted Credit Card Information

Happy Programming!