Database Objects
CDP for MSSQL includes database objects that enable you to access features of the NAE Server directly from SQL Server. These objects include stored procedures, extended procedures, and functions.
User Defined Functions (UDFs) enable you to interface with the CipherTrust Manager directly by accessing the extended procedures in CDP. You can use these UDFs to generate random binary values, or encrypt and decrypt the following data types: char, varchar, smalldatetime, datetime, decimal, numeric, int, bigint, money, and smallmoney.
Encryption and Decryption UDFs
The encryption and decryption UDFs enable you to access the cryptographic functionality of the CipherTrust Manager from the database. There are two types of cryptographic UDFs: command line and name. The command line UDFs take the key name, mode, padding, and IV as parameters and apply these values to the input data. The names of these UDFs include the prefix ‘cl’. The input parameters are detailed below.
The name UDFs take the database name, user name, table name, column name, and IV as parameters and use these values to recall the encryption metadata (key name, algorithm, and IV). That metadata is then applied to the input data. The names of these UDFs include the prefix ‘nm’. The input parameters are detailed below.
Note
Name UDF operations are not permitted while online encryption/key rotation is in progress.
| Parameter Name | Data Type | Description |
|---|---|---|
| pi_Data | varies by UDF | The value to be encrypted or decrypted. The data type is abbreviated in the UDF name. This parameter is used for both the command line and the name UDFs. |
| pi_vKey | varchar(100) | The name of the key to use to encrypt or decrypt the data. This key must exist on the NAE Server, and the requester must have permission to use the key for the operation requested. This parameter is used for the command line UDFs. |
| pi_vMode | varchar(100) | A string representing the mode/cardinality in which the key is used for operations that use a symmetric key (DES, DES-EDE, or AES algorithms). For other algorithms, this value is ignored. Use CBC or ECB for standard encryption and FPE/CARD10 or FPE/CARD62 for FPE encryption. This parameter is used for the command line UDFs. |
| pi_vPadding | varchar(100) | A string indicating what kind of padding to use for operations that use a symmetric key (DES, DES-EDE, or AES algorithms). For other algorithms, this value is ignored. This parameter is used for the command line UDFs. |
| pi_vDatabase | varchar(128) | The database on which the table resides. This parameter is used for the name UDFs. |
| pi_vOwner | varchar(128) | The database owner. This parameter is used for the name UDFs. |
| pi_vTable | varchar(128) | The table on which the column resides. This parameter is used for the name UDFs. |
| pi_vCol | varchar(128) | The column to be operated on. This parameter is used for the name UDFs. |
| pi_vIV | varbinary(128) | The value to use as the Initialization Vector (IV) for data being encrypted or decrypted with a symmetric key (DES, DES-EDE, or AES algorithms) in CBC mode. For other algorithms or symmetric keys used in ECB mode, this value is ignored. The IV you specify for operations that use DES or DES-EDE keys must be eight-byte; for AES keys, the IV must be sixteen-byte. If you are going to specify an IV here, the IV must be base 16 encoded. This parameter is used for both the command line and the name UDFs. Note: The IV value for standard encryption can be null or empty for command line UDFs. For such cases, default IV value will be used. |
UDFs are broadly classified into:
You will normally not call these UDFs directly; they are integrated into the data encryption functionality. However, if you want to call these UDFs directly, refer to Examples of Direct UDFs Calls.
Data Type Conversion UDFs
Thales encryption and decryption UDFs automatically convert the input data into VARBINARY using the conversion UDFs shown below. You would not normally call these UDFs directly, they are integrated into the data encryption functionality.
The following table describes the process of converting various data types before the encryption UDFs are applied.
| Name | Description |
|---|---|
| ing_chr_to_bin420 | Converts a CHAR value into a VARBINARY value. |
| ing_ncr_to_bin420 | Converts an NCHAR value into a VARBINARY value. |
| ing_dec_to_bin420 | Converts a DECIMAL value into a VARBINARY value. |
| ing_sdt_to_bin420 | Converts a SMALLDATETIME value into a VARBINARY value. |
| ing_dtm_to_bin420 | Converts a DATETIME value into a VARBINARY value. |
| ing_int_to_bin420 | Converts an INTEGER value into a VARBINARY value. |
| ing_bin_to_bin420 | Converts a BIGINT value into a VARBINARY value. |
| ing_mny_to_bin420 | Converts a MONEY value into a VARBINARY value. |
| ing_smo_to_bin420 | Converts a SMALLMONEY value into a VARBINARY value. |
| ing_nvc_to_bin420 | Converts an NVARCHAR value into a VARBINARY value. |
| ing_vrc_to_bin420 | Converts a VARCHAR value into a VARBINARY value. |
Generate Random (ing_gn_rndm)
This UDF generates a random binary value of the specified number of bytes. This function is used mainly to generate random IVs. You can request a number of bytes between 1 and 2000.
| Parameter | Data Type | In/out | Description |
|---|---|---|---|
| @numBytes | int | In | The byte length of the binary value. |
| Function Output | Data Type | In/out | Description |
|---|---|---|---|
| @RETURN_VALUE | varbinary(8000) | Out | The ciphertext. |
For example
SELECT metadb.dbo.ing_gn_rndm (10)
AS encrypted_value;
------------------------
encrypted_value
0xB0076D117E5BAD14975C
