To read the article online, visit http://www.4GuysFromRolla.com/articles/022807-1.aspx

Using Asymmetric Encryption and Digital Signatures in a SQL Server 2005 Database

By Erich Peterson and Siqing Li


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:

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

FunctionDescription
EncryptByAsmKeyEncrypts data with an asymmetric key.
DecryptByAsmKeyDecrypts data with an asymmetric key.
EncryptByCertEncrypts data with the public key of a certificate.
DecryptByCertDecrypts data with the public key of a certificate.
Cert_IDReturns the ID of a certificate.
AsymKey_IDReturns the ID of an asymmetric key.
CertPropertyReturns the value of a specified certificate property.

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

FunctionDescription
SignByAsymKeySigns plaintext with an asymmetric key.
VerifySignedByAsmKeyTests whether digitally signed data has been changed since it was signed.
SignByCertSigns text with a certificate and returns the signature.
VerifySignedByCertTests whether digitally signed data has been changed since it was signed.

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.

The Customers Table Schema
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.

Use a Custom SQL Statement
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

Inserting Encrypted Data through ASP.NET
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.

The Ouput After Two 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;

The Encrypted Credit Card Information
Figure 12: The Encrypted Credit Card Information

Happy Programming!

  • By Erich Peterson and Siqing Li


    <%-- Removed 3/1/2007, SKM

    Attachments


  • Download the ASP.NET code sample
  • --%>

    References


    [1] Microsoft, "Improving data security by using SQL Server 2005: Using SQL Server 2005 to help protect data," Technical White Paper, October 2005.
    [2] W. Stallings, "Cryptography and network security principles and practices", Fourth Edition, Prentice Hall, 2005.
    [3] Application Security, Inc., "Encryption of data at rest," White Paper, Accessed November 2006, http://www.appsecinc.com/presentations/Encryption_of_Data_at_Rest.pdf.
    [4] Microsoft, "Encryption Hierarchy," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms189586.aspx.
    [5] Microsoft, "Cryptographic Functions," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms173744.aspx.
    [6] Microsoft, "CREATE SYMMETRIC KEY (Transact-SQL)", SQL Server 2005 Books Online, Accessed January 2007, http://msdn2.microsoft.com/en-us/library/ms188357.aspx.

    Bibliography


    Michael Coles, "SQL 2005 Symmetric Encryption," SQL Server Central, March 2006, http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp.
    Don Kiely, "Hackers Beware: Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005," MSDN Magazine, June 2005, http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx.

    Article Information
    Article Title: ASP.NET.Using Asymmetric Encryption and Digital Signatures in a SQL Server 2005 Database
    Article Author: Erich Peterson and Siqing Li
    Published Date: February 28, 2007
    Article URL: http://www.4GuysFromRolla.com/articles/022807-1.aspx


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers