Introduction
I should stress at this point that this article is in no way intended as a Crystal Reports tutorial, a lesson in Interop, Security or Cryptography, it is however a very specific outline on how to write custom functions using managed code or re-use investments made in existing managed code libraries, to help facilitate the decryption of data in a SQL Server database for use in Crystal Reports. I will however briefly state some things that will be painfully obvious to experienced Crystal users, so bare with me if that's you.
What are User Function Libraries?
User Function Libraries (UFLs) are User Defined Functions which are made accessible (via a COM interface) to programmers, using the formula editor of Crystal Reports. Programmers are capable of writing Crystal Reports without ever having to open the formula editor, however those who have used the reporting capabilities of Crystal Reports will have come to know this tool with some appreciation for its power and usefulness. Put simply, the formula editor furnishes us with programmatic control at the field level, the ability to customize formatting, derive calculation, and places familiar programming constructs such as decision, iteration and evaluation at our fingertips. By the way, you can write code targeted for the formula editor in two syntaxes, namely Crystal and Basic (most VB programmers will probably go straight for Basic for obvious reasons).
Exhibit 1.0
What the above exhibit demonstrates is that we are using a User Function Library and that the formula editor, is for our purposes, a very useful code editor which comes flush with a standard templated set of functions, Additional Functions (that's where UFLs live) and a full set of templated operators ready to use out of the box. Here I have created a formula field which uses the the database field fldTestEncrypted (from the table tblTestEncrypt) as a parameter to the UFL rijDecryptorReconstruct. Once having created this formula with this short trivial line of code, I am able to place it in the report (probably in the Details section of the report) and for every row that the report prints, this field will be passed to a method which will decrypt the data in the database and display the plain text value for the field, in a readable format as displayed in Exhibit 2.0 and 3.0.
Exhibit 2.0 (Data Decrypted in the Crystal Report Viewer)
What do rows 1 & 2 from the report look like in the database? See below.
Exhibit 3.0 (Encrypted Data in SQL Server)
UFLs are Dynamic Link Libraries that expose one or more functions which are part of a COM automation server. When you install Crystal Reports for .NET, the file U2lcom.dll is installed into Crystal Decisions\1.0\Bin\ folder of your installation. U2lcom.dll is a UFL that is able to make use of COM DLL files that have been custom authored for a multitude of purposes. The caveats here are that these automation servers must be built with a COM capable language (not .NET languages that can be used to build DLL files registered for interop - according to Crystal Decisions) and you must name your DLL according to a standard naming convention defined by Crystal. We will see how to do this shortly.
Taking Advantage of System.Security.Cryptography
Because we want to leverage the FCL Cryptography libraries to encrypt our data, we are going to need to decrypt the same data with managed code. For the purpose of this article, I have chosen to create an assembly using C# and the RijndaelManaged cryptography library to both encrypt and decrypt my data.
Listing 1.0 (Managed Code Encrypts and Decrypts)
using System;
using System.Security.Cryptography;
using System.Text; using System.IO;
namespace Dotnetstudios
{
public class CrystalEncryption
{
private static MemoryStream memStream = new MemoryStream();
private string b64key = "06W9tLEMdwxKv1C6XMoel7ibhix9dPpwJqdZ+7EuMMU=";
private string b64iv = "dIOh4fhx5iL6JzGw6b2B1yp56jxShQqwo2U2G3GqI3s=";
private ASCIIEncoding textConverter = new ASCIIEncoding();
private RijndaelManaged myRijndael = new RijndaelManaged();
public CrystalEncryption(){}
public string CrystalEncrypt(string encData)
{
try
{
byte[] rijKey = Convert.FromBase64String(b64key);
byte[] rijIv = Convert.FromBase64String(b64iv);
myRijndael.BlockSize = 256;
myRijndael.KeySize = 256;
ICryptoTransform encryptor =
myRijndael.CreateEncryptor(rijKey, rijIv);
MemoryStream msEncrypt = new MemoryStream();
CryptoStream csEncrypt = new CryptoStream(msEncrypt,
encryptor, CryptoStreamMode.Write);
byte[] toEncrypt;
toEncrypt = textConverter.GetBytes(encData);
csEncrypt.Write(toEncrypt, 0, toEncrypt.Length);
csEncrypt.FlushFinalBlock();
byte[] encrypted;
encrypted = msEncrypt.ToArray();
string cipherText = Convert.ToBase64String(encrypted,
0 , encrypted.Length);
return cipherText;
}
catch(Exception ex)
{
throw ex;
}
}
public string CrystalDecrypt(string decData)
{
try
{
byte[] rijKey = Convert.FromBase64String(b64key);
byte[] rijIv = Convert.FromBase64String(b64iv);
byte[] encrypted;
encrypted = Convert.FromBase64String(decData);
myRijndael.BlockSize = 256;
myRijndael.KeySize = 256;
ICryptoTransform decryptor =
myRijndael.CreateDecryptor(rijKey, rijIv);
MemoryStream msDecrypt = new MemoryStream(encrypted);
CryptoStream csDecrypt = new CryptoStream(msDecrypt,
decryptor, CryptoStreamMode.Read);
byte[] fromEncrypt;
fromEncrypt = new byte[encrypted.Length];
csDecrypt.Read(fromEncrypt, 0, fromEncrypt.Length);
string roundtrip = textConverter.GetString(fromEncrypt);
return roundtrip;
}
catch(Exception ex)
{
return ex.Message;
}
}
}
}
Why Interop?
Very simply, the class above is not the actual UFL, (remember UFLs can only be authored in COM capable environments excluding .NET assemblies registered for Interop). Therefore what is required here is an Automation Server built in say VB or C++ (perhaps even Delphi - don't quote me on that). We are going to use the COM Automation Server to create a UFL that is a wrapper for our Managed Code Libraries that will perform the encryption and decryption. If you are using Visual Studio .NET, then all you need to do to register the managed code assembly is set the Register for COM Interop attribute in the project's property sheet and build the project.
Exhibit 4.0
If you are not using VS.NET, then the Regasm utility tool will do the job just fine. You can find it at C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322 in a typical install.
Building the UFL
I am going to limit the example here to Visual Basic, as C++ is not my best suit. First create a Visual Basic ActiveX DLL project and add the functions that you want to be exposed by the UFL and any private ones if necessary. All of the functions you want to be available in the Crystal Environment need to be declared as PUBLIC and must return one of the following data types only: Integer, Long, Single, Double, Currency, Date, Boolean and String. You cannot use the Variant type.
Change the ActiveX DLL project name to CRUFLxxx where xxx can be any three characters and the first character being only a letter. CRUFLa87 and CRUFLrij will both work fine. The first part of the project name must be CRUFL in order for the U2lcom.dll to be able to understand the PUBLIC functions defined in your UFL.
Exhibit 5.0 (Visual Studio 6 IDE)
Listing 2.0
Public Function Reconstruct(data As String) As String
Dim reco
Set reco = CreateObject("Dotnetstudios.CrystalEncryption")
Reconstruct = reco.CrystalDecrypt(data)
End Function
The code above is using late binding to create an instance of our managed assemblies class. The string argument used in the CreateObject function must be fully qualified i.e. namespace.namespace.namespace.Class Name.
Finally, build the ActiveX DLL, place it in the same directory as U2lcom.dll and then register your DLL using Regsvr32.exe. If your not familiar with Regsver32.exe then either use the command window or the Run dialog in Windows to execute a command like this: regsvr32.exe "C:\Program Files\Common Files\Crystal Decisions\1.0\Bin\CRUFLrij.dll". This will register the ActiveX DLL with Windows.
Using your UFL in Crystal Reports
All that's left to do now is create a formula field that calls the UFL in your Crystal Report. Start by creating a report and then using the Field Explorer, create a New... formula field. Under the Additional Functions node in the Functions tree in the formula editor, select your UFL and the appropriate function, and select the relevant database field to pass as argument.
Exhibit 6.0 (Creating a new formula)
Exhibit 7.0 (Writing the formula)
Test the formula, save it and then run the report and what you should see is the encrypted data decrypted and displayed as plain text as per exhibit 2.0.
Final Word
Clearly, questions of securing your Crystal Reports should be considered, otherwise encrypting the data would seem pointless if you are going to expose the data to any prying eyes via the report. As I stated earlier, this article does not set out to discuss securing Managed Code. But I strongly advise you make sure your reports are secure, otherwise your data will be in jeopardy.
And finally I like to thank Professor Ido Millet for his help and guidance.
