Decryption in Reports

In the interest of keeping sensitive constituent information secure, social security numbers, credit card numbers, and bank account numbers are encrypted before they are stored in Millennium.

Decryption and Millennium Reporter

To include a social security number, credit card number, or bank account number in a report, Millennium Reporter will decrypt the field for you if you have set the Decrypt Columns Report Option to Yes. Then, to display these decrypted values, place the GENERIC worktable column_decrypt field on your report format.

• To include the social security number on your report, your key file must be corebio ('00') so that the Profile worktable's column_decrypt field will hold the decrypted value of the social security number (coressnum) stored in Basic Data.
• To include the credit card number field (which stores both credit card numbers and bank account numbers) on your report, your key file must be creditcard ('37') so that column_decrypt will hold the decrypted value of the Credit Card table's credccnum field.

But, setting the Decrypt Columns Option to Yes may cause your reports to run slower than if this option is set to No. And, Millennium Reporter will only decrypt and place only one of the two fields, but not both, in the worktable. In the interest of efficiency, and for those who write reports to display both fields on one format, Millennium provides a way to decrypt these fields on the report format itself.

Decryption and Crystal

When a Millennium Reporting Server is installed (or upgraded), a file named CRUFL_mil.dll is installed in the Global Assembly Cache on the Report Server computer. This file contains the functions needed to both decrypt and encrypt certain Millennium fields within Crystal.

A standalone installation program is available if CRUFL_mil.dll needs to be installed on a computer that does not have a Reporting Queue (for example, a report developer's computer).

• The program is crystal_crufl_mill_install.exe, and it is located in the inetpub/wwwroot/mill/tools directory on the IIS Server computer.
• Run the program from the computer on which you need to install it. (if necessary, copy crystal_crufl_mill_install.exe to that computer).
• CRUFL_mil.dll will be installed into the Global Assembly Cache, and CRUFL_mil.tlb will be installed in Program Files\Common\Millennium\Crystal UFL directory.

The millDecrypt function is used to decrypt the following Millennium fields: coressnum, credccnum, credssnum, ecomccnum, and ecomssnum. If you want to display these fields on a report, they must be decrypted first.

To add a decrypted field to a report format, within Crystal:

1. Navigate to Crystal's Formula Workshop - Formula Editor
2. Expand Functions. Expand Additional Functions. Expand Visual Basic UFL's
3. Select millDecrypt.
4. Build your formula, following the syntax as outlined in the table below. Do not decrypt the results of the encryption of a string whose value was Null, so make sure to add a check for Null into your formula.
5. Insert your formula on to the format.

The correct Crystal formula syntax for decrypting Millennium fields is as follows:

Field	Data Table	Crystal Formula Syntax
Social Security Number	Basic Data	millDecrypt ("coressnum", {corebio_full.coressnum})
Social Security Number	Credit Card	millDecrypt ("credssnum", {creditcard_full.credssnum})
Social Security Number	eCommerce Pending	millDecrypt ("ecomssnum", {ecommerce_pending.ecomssnum})
Credit Card Number or Bank Account Number	Credit Card	millDecrypt ("credccnum", {creditcard_full.credccnum})
Credit Card Number or Bank Account Number	eCommerce Pending	millDecrypt ("ecomccnum", {ecommerce_pending.ecomccnum})

MillCrypt_Crystal.dll also includes a function, called millEncrypt, that will encrypt strings for future storage into Millennium's encrypted columns. You may want to use this functionality when writing a Crystal Report that uses an Input file that is external to Millennium and that contains one or more of these fields.

Important! The value of the millEncrypt string parameter (for example, corebio_full.coressnum) must not be already encrypted.

To add an encrypted field to a report format, within Crystal:

1. Navigate to Crystal's Formula Workshop - Formula Editor.
2. Expand Functions. Expand Additional Functions. Expand Visual Basic UFL's.
3. Select millEncrypt.
4. Build your formula, following the syntax as outlined in the table below. Do not encrypt a field value that is Null. And, blank field values will be encrypted as a series of 0's and 1's, so you will want to add a check for both Null and Blank strings into your formula.
5. Insert your formula on to the format.

The correct Crystal formula syntax for encrypting Millennium fields is as follows:

Field	Data Table	Crystal Formula Syntax
Social Security Number	Basic Data	millEncrypt ("coressnum", {corebio_full.coressnum})
Social Security Number	Credit Card	millEncrypt ("credssnum", {creditcard_full.credssnum})
Social Security Number	eCommerce Pending	millEncrypt ("ecomssnum", {ecommerce_pending.ecomssnum})
Credit Card Number or Bank Account Number	Credit Card	millEncrypt ("credccnum", {creditcard_full.credccnum})
Credit Card Number or Bank Account Number	eCommerce Pending	millEncrypt ("ecomccnum", {ecommerce_pending.ecomccnum})