Instead, you now need to switch to T-SQL editing to create scripts to build the stored procedures you'll use to interface with the CLR assembly's functions. Although you can use the Visual Studio 2005 alpha tools to build the test-harness T-SQL scripts, the alpha release has problems that make building and testing stored procedures difficult. I understand the latest beta builds handle the entire process of building T-SQL stored procedures that call CLR functions (as we're doing here), but I didn't have access to those builds at the time I wrote this article. For this example, Management Studio was easier for me to use than the Visual Studio 2005 tools. In the example files you downloaded, I included a Management Studio project, EncryptDecrypt
.sqlsln, that includes the following scripts:
- Create CCard Table—I used this T-SQL script to build the CCards table, which holds the encrypted credit card data.
- Build script—As I explain in a moment, this T-SQL script creates the server-side ASSEMBLY object and addresses the CLR assembly DLL I created in the first step. This script also creates three T-SQL stored procedures—GetKeys, Encrypt, and Decrypt—that link to the CLR assembly functions we just looked at.
- Create Proc InsertCCard—This T-SQL script creates the InsertCC stored procedure that the Visual Basic .NET front-end application calls so that it can save a credit card number to the database as an encrypted varbinary value.
- Create FetchEncryptedCCNo—This T-SQL script creates the FetchCCNo stored procedure, which the Visual Basic .NET front-end application calls when it needs to decrypt an encrypted credit card number.
The next step in creating a callable CLR-based assembly from SQL Server is to create a server-side ASSEMBLY object from the CLR assembly that Visual Studio .NET built.
Invoking a CLR-Based Stored Procedure
To create the CLR assembly, I used the T-SQL CREATE ASSEMBLY statement. Visual Studio 2005's alpha release can do this for you automatically if your CLR-based assembly is simple, but once you start calling out to the .NET Framework's external DLLs, you have to revert to installing your server-side assemblies manually. The alpha release of Visual Studio 2005 doesn't include the mechanisms that Visual Studio needs to pass to SQL Server so that SQL Server knows how to trust the new ASSEMBLY.
The T-SQL script BuildScript installs a new server-side assembly. Because the ALTER ASSEMBLY statement isn't implemented in SQL Server 2005's alpha code, you must drop all dependencies before recreating the assembly. Visual Studio 2005 drops the dependencies for you when you use the Deploy feature on the Project. This means that if your CLR ASSEMBLY gets too complex, you'll need to revert to manually dropping the dependencies, as the code at callout D shows, and hard-coding the CREATE ASSEMBLY code in a T-SQL script, as the code at callout E shows.
The next few lines at callout E show the name and address of the new SQL Server assembly, which I built at the beginning of this process by using Visual Studio 2005 and compiled to a DLL. Note that I specified that this assembly is UNSAFE, which is one of three permission settings that tell SQL Server whether to execute code in safe or unsafe areas outside the sandbox. The term sandbox refers to the "safe" area within reach of SQL Server. If your code attempts to access functions or resources outside of this area (i.e., in areas considered to be unsafe), it's said to be working outside of the sandbox and is prohibited from executing—unless it's trusted and permitted to do so. In this case, I tell SQL Server that this CLR assembly is permitted to make UNSAFE calls to encryption libraries outside SQL Server. For a description of the permission settings, see the sidebar ".NET Safety Settings."
After you create the CLR assembly on the server, you can address it the same way you do any other T-SQL function—from
T-SQL procedures or functions. For this example, I installed three T-SQL procedures (as the code at callout F shows) to call three function classes that the assembly exposes: the GetKeys class, which generates public and private RSA encryption keys; the Encrypt class, which accepts a string and uses a specified RSA public encryption key to return an encrypted byte array; and the Decrypt class, which accepts a private RSA key and a byte array and returns the decrypted value as a string. All three of these classes are exposed as Visual Basic .NET functions within the single CLR assembly we just created on SQL Server.
You can invoke the server-side assembly from a T-SQL stored procedure (as in this example), a user-defined function (UDF), or a trigger. As the code at callout F shows, the T-SQL procedures start out like any typical stored procedure, by defining the input and output parameters and starting the T-SQL code block with an AS statement. The magic occurs as you specify the connection with the EXTERNAL NAME statement to the pre-installed server-side ASSEMBLY, which links to the CLR assembly DLL entry points (the class functions). I named the CLR DLL assembly asyEncryptDecrypt, the class within the assembly is clsEncryptDecrypt, and the method (Visual Basic .NET function) the assembly links to is GetKeys, as the EXTERNAL NAME T-SQL clause shows:
EXTERNAL NAME
[asyEncryptDecrypt]:[asyEncrypt
Decrypt.clsEncryptDecrypt]::[Get
Keys]
When this T-SQL procedure executes, SQL Server fetches and prepares the CLR assembly for execution and verifies several pieces of information. In the process, SQL Server verifies the existence of the CLR assembly. If the assembly isn't installed at the specified location or you didn't specify the path correctly, the CREATE PROCEDURE statement fails. Then, SQL Server verifies the number of parameters the invoking stored procedure passes to the CLR class function. This number must be the same as the number of parameters you defined for the T-SQL procedure. Next, SQL Server confirms the data type of each of the CLR-based function arguments. In my testing of this early SQL Server 2005 build, CLR-based stored procedures couldn't access all T-SQL data types. For example, if you want to pass a string from your CLR code, you have to declare it as a SqlTypes.SqlString data type and declare the T-SQL parameter as nvarchar because CLR strings are Unicode by default. Finally, SQL Server verifies the way you defined the CLR assembly's function arguments. To get T-SQL to recognize an output parameter, you must define the CLR function arguments as ByRef and the input arguments as ByVal.
One of the mysteries I had to solve was how to pass T-SQL data types to and from CLR assemblies. The sidebar "Mapping T-SQL Data Types to SqlTypes" lists some tips for converting T-SQL data types.
As I noted earlier, I also didn't have much luck using the alpha release of the Management Studio Create Stored Procedure feature to create and edit these stored procedures. I found it easier to use Management Studio's built-in SQL file editor to code and test the T-SQL procedures. The alpha Visual Studio 2005 build wouldn't let me easily create and edit the stored procedures either. Both tools lacked the ability to set permissions, so I hope Microsoft has solved this problem in more recent builds.
A Valuable Process
This encryption project has been valuable as well as challenging. It taught me a lot about the realities of building, testing, and deploying CLR-based procedures as well as what works and what doesn't. I also learned quite a bit about suitable uses for CLR-based procedures, functions, and triggers and for CLR-based user-defined types (UDTs). Some of the marketing I've seen about SQL Server's upcoming support for the CLR is overblown, and I saw some exaggerations of the CLR's usefulness when I was at Microsoft TechEd in May. But I think Microsoft is reducing the hyperbole and doing a better job of informing its marketers about SQL Server 2005 and Visual Studio 2005 feature sets.
I encountered lots of quirks in the alpha versions of SQL Server 2005 and Visual Studio 2005 that I had to write around as I went. I achieved the purpose of the exercise, though—to see how to write and test CLR-based database code. Although you might have only a few uses for this particular encrypt/decrypt application, it demonstrates the ability of the CLR to deal well with specific operations and functionality that T-SQL currently doesn't provide. Developers have struggled for years to implement technology that supplements T-SQL functionality, and I can see that CLR-based code execution will be a welcome innovation.
End of Article
Prev. page
1
2
[3]
next page -->
