Using Asymmetric Encryption and Digital Signatures in a SQL Server 2005 DatabaseBy 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:|
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 .
Table 2: Asymmetric Functions
|Encrypts data with an asymmetric key.|
|Decrypts data with an asymmetric key.|
|Encrypts data with the public key of a certificate.|
|Decrypts data with the public key of a certificate.|
|Returns the ID of a certificate.|
|Returns the ID of an asymmetric key.|
|Returns 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:
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
DecryptByAsymKey functions for performing asymmetric encryption and decryption. Also shown is the
AsymKey_ID function, which returns the ID for a particular asymmetric key.
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
Lastly, to decrypt the ciphertext, we use the
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  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
function is used to return the certificate's identifier from the database (rather than the
which was used in the previous example).
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 .
Table 3: Signing and Signature Verification Function
|Signs plaintext with an asymmetric key.|
|Tests whether digitally signed data has been changed since it was signed.|
|Signs text with a certificate and returns the signature.|
|Tests whether digitally signed data has been changed since it was signed.|
The following T-SQL sample illustrates using the
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
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
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
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
CreditCardSecurityCode columns will be
encrypted using a symmetric key that is encrypted using a "self-signed" certificate.
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.
The above statements create the database master key for
SecureDatabase, a certificate named
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:
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:
Next, select the INSERT tab and enter the following script:
SELECT statement grabs all the columns of the
(decrypting each row using the symmetric key
CustomerDataKey) for a particular customer (namely, one whose
Id value equals the value of the supplied
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
@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
Additionally, we need to instruct the GridView to "refresh" when a new record is added to the
via the FormView or DetailsView control. To accomplish this, create an event for the DetailsView or FormView's
event and, from there, call the GridView's
DataBind() method (like
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
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:
Figure 12: The Encrypted Credit Card Information
<%-- Removed 3/1/2007, SKM
 Microsoft, "Improving data security by using SQL Server 2005: Using SQL Server 2005 to help protect data," Technical White Paper, October 2005.
 W. Stallings, "Cryptography and network security principles and practices", Fourth Edition, Prentice Hall, 2005.
 Application Security, Inc., "Encryption of data at rest," White Paper, Accessed November 2006, http://www.appsecinc.com/presentations/Encryption_of_Data_at_Rest.pdf.
 Microsoft, "Encryption Hierarchy," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms189586.aspx.
 Microsoft, "Cryptographic Functions," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms173744.aspx.
 Microsoft, "CREATE SYMMETRIC KEY (Transact-SQL)", SQL Server 2005 Books Online, Accessed January 2007, http://msdn2.microsoft.com/en-us/library/ms188357.aspx.
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.