Database Objects
CDP for MSSQL includes database objects that provide direct access to CDP features from SQL Server. These objects include stored procedures, extended procedures, and functions.
User Defined Functions (UDFs) allow direct interaction with CDP using the extended procedures. These UDFs can be used to generate random binary values or encrypt/decrypt the following data types: char, varchar, smalldatetime, datetime, decimal, numeric, int, bigint, money, and smallmoney.
Note
In this topic, the terms Standard Algorithm and Non-FPE Algorithm are used interchangeably.
UDFs are categorized into the following types:
Cryptographic UDFs
The cryptographic UDFs allow you to access the cryptographic functionality of the CDP for MSSQL directly from the SQL Server database.
The cryptographic UDFs are further categorized as follows:
- Command Line UDFs: These UDFs accepts parameters such as key name, mode, padding, and IV as parameters and apply these values to the input data. The names of these UDFs contain
clorfcl.
Following table outlines the input parameters required to use Command Line UDFs:
| 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. |
| pi_vKey | varchar(100) | The name of the key to use to encrypt or decrypt the data. This key must exist on the CipherTrust Manager, and the requester must have permission to use the key for the operation requested. You can also use versioned key with non-FPE algorithms. Refer to How to call direct UDFs for non-FPE |
| pi_vMode | varchar(100) | A string representing the mode/cardinality in which the key is used for operations that use a symmetric key (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. |
| pi_vPadding | varchar(100) | A string indicating what kind of padding to use for operations that use a symmetric key ( DES-EDE or AES algorithms). For other algorithms, this value is ignored. |
| pi_vIV | varbinary(128) | The value to use as the Initialization Vector (IV) for data being encrypted or decrypted with a symmetric key (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-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. The IV value for standard encryption can be null or empty. In such cases, default IV value will be used. |
- Name UDFs: These UDFs accepts parameters such as database name, user name, table name, column name, and IV as parameters and use these values to retrieve the encryption metadata (key name, algorithm, and IV). This metadata is then applied to the input data. The names of these UDFs contain
nmorfnm.
Following table outlines the input parameters required to use by Name UDFs:
| 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. |
| pi_vDatabase | varchar(128) | The database on which the table resides. |
| pi_vOwner | varchar(128) | The database owner. |
| pi_vTable | varchar(128) | The table on which the column resides. |
| pi_vCol | varchar(128) | The column to be operated on. |
| pi_vIV | varbinary(128) | The value to use as the Initialization Vector (IV) for data being encrypted or decrypted with a symmetric key (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-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. The IV value for standard encryption can be null or empty. In such cases, default IV value will be used. |
Note
Name UDF operations are not permitted while online encryption/key rotation is in progress.
Based on the type of encryption, non-FPE (standard Algorithm) or FPE, UDFs are divided as follows:
Not all the UDFs can be called directly; they are integrated into the data encryption functionality. However, some UDFs can be called directly. Refer to How to call direct UDFs for non-FPE and How to call direct UDFs for FPE for details.
Data Type Conversion UDFs
CDP cryptographic UDFs automatically convert the input data into VARBINARY using the conversion UDFs as 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. You can call this UDF directly as shown in the example below.
| 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
