SQL Server Magazine

The process I used to create this application was backward when compared to the way that many people design applications:

1. I started with a paper design (a radical concept for some). This step helped me keep track of my work when my development process was interrupted.
2. Next, I wrote the CLR-based encryption code in a Windows application and tested it using Visual Studio .NET 2003 and the .NET Framework 1.1. I reverted to this earlier release of the .NET Framework because of problems in the 2.0 release that prevented me from converting strings to byte arrays and back.
3. Then, I loaded the Visual Studio 2005 PDC bits and imported the application that used the .NET Framework 1.1. I altered the code to pass a varbinary byte array from and back to the conversion routines. This method was more efficient than converting the byte arrays to Unicode strings before transport.
4. I wrote scripts to create the T-SQL stored procedures that called the CLR-based encryption functions and deployed the CLR DLL assembly. This example let me deploy the assemblies directly from Visual Studio 2005, but when I called the encryption classes, I got protection violations. I had to hard-code the assembly code as UNSAFE to get it to work. Also, when I used the Visual Studio 2005 deploy feature, Visual Studio dropped any T-SQL stored procedures that depended on calls to the CLR assemblies. I expect Microsoft will solve this problem in later builds.
5. Next, I wrote a script to create the test table CCards and set the table's access permissions. (This script and all the SQL scripts for this example are available for download at http://www.sqlmag.com, InstantDoc ID 42208.)
6. I created scripts for T-SQL stored procedures to add rows to the test table. The scripts included code to set execute permissions on the procedures.
7. Finally, I wrote a VB front end to capture parameters from the user and call the T-SQL procedures. The T-SQL procedures called the CLR-based code to encrypt the value and store that value as a varbinary data type in the test table. The application also included code to fetch a row from the test table and attempt to decrypt the value by using a selected private key.

As Figure 4 shows, the example program captures a user's credit card number and an expiration date. When the user clicks Submit, the code calls a stored procedure that calls a CLR assembly, which in turn generates a private RSA encryption key and uses the key to encrypt the credit card number. The stored procedure saves to the database the encrypted data (now in the form of a byte array) and the expiration date. The code saves the private encryption key and displays it in the client application to assist in debugging the application. However, the private encryption key isn't stored on the server; this way, only the user can decrypt the server-side value—and then only if the client-side application persists the value locally. I can't compare the example encryption and decryption routines with equivalent T-SQL code, which doesn't support this functionality, but this example shows the mechanics of creating CLR-based code, and my informal tests showed that it performed well. However, I suggest you experiment with your own CLR code in an environment that matches your production system—don't assume that your code will perform the same way mine did.

When the user wants to retrieve the credit card number so that some server-side function can use it, the client-side application calls a retrieve stored procedure that takes the encryption key, looks up the customer record by ID and expiration date, and decrypts the value by using another CLR-based procedure. To help debug the example application, the code returns the decrypted credit card number to the client application, but I expect that in a real-world solution, the server-side application would simply use the unlocked value to perform some other operation.

Note that this application calls the CLR stored procedures from T-SQL procedures. I found this design more convenient than calling the CLR procedures directly, and I expect that's what most developers will end up doing. Another approach would be to code the CLR procedures as functions and embed them in T-SQL code.

This article introduced you to some concepts and realities of CLR-based stored procedures. You've seen how the procedures work and where they fit in the bigger scheme of a high-performance database management system (DBMS). In an upcoming article, I'll show you how to use Visual Studio 2005 and SQL Server 2005 to code and test the CLR-based stored procedures that this article's test application invokes.

End of Article

Prev. page     1 2 [3]     next page -->