How to call direct UDFs for non-FPE algorithms
Note
The user running a command line UDF must be mapped to the owner of the key (passed as a parameter) in the UDF.
Versioned keys can only be used with command line (
cl UDFs).If a versioned key is passed in the non-versioned direct UDFs call, latest version of that key will be used in encryption/decryption.
When calling command line UDFs (cl) directly for non-FPE algorithms, both versioned and non versioned keys are supported. To use a versioned key, pass the key version along with key name, separated by a # (keyName#keyversion). See the example below.
Direct Call to dbo.ing_e_chr_cl420
UDF calls with versioned key
SELECT dbo.ing_e_vrc_cl420('yourCharValue'/* inputData , */'yourKey#Version'/* keyName#keyversion', */'CBC' /* Mode used, */'PKCS5Padding'/* Padding used ,*/0x4A40F6DFB05F379630AC0533E7F6357 /* iv */)
AS ENCRYPTED_VALUE
---------------------------------------------------------
ENCRYPTED_VALUE
0x10003009629310648CA6E40AE098542E43B2B2
UDF calls with non-versioned key
SELECT dbo.ing_e_vrc_cl420('yourCharValue'/* inputData , */'yourKey'/* keyName', */'CBC' /* Mode used, */'PKCS5Padding'/* Padding used ,*/0x4A40F6DFB05F379630AC0533E7F6357 /* iv */)
AS ENCRYPTED_VALUE
---------------------------------------------------------
ENCRYPTED_VALUE
0x9F71F06D9F1E123B68D910F0EC34E8C5
Direct Call to dbo.ing_d_chr_cl420
UDF calls with versioned key
SELECT dbo.ing_d_vrc_cl420(0x10003009629310648CA6E40AE098542E43B2B2,/* inputData */'yourKey#Version'/* keyName#keyversion */'CBC', /* Mode used */
'PKCS5Padding', /* Padding used */0x24A40F6DFB05F379630AC0533E7F6357) /* iv */
AS DECRYPTED_VALUE
----------------
DECRYPTED_VALUE
yourCharValue
Note
Make sure to use the same key version for decryption that was used for encryption.
UDF calls with non-versioned key
SELECT dbo.ing_d_vrc_cl420(0x9F71F06D9F1E123B68D910F0EC34E8C5,/* inputData */'yourKey', /* keyName */'CBC', /* Mode used */
'PKCS5Padding', /* Padding used */0x24A40F6DFB05F379630AC0533E7F6357) /* iv */
AS DECRYPTED_VALUE
----------------
DECRYPTED_VALUE
yourCharValue
Direct UDF calls with non-versioned key
Direct Call to dbo.ing_e_chr_nm420
SELECT dbo.ing_e_chr_nm420('yourCharValue', /* inputData */'yourDatabase', /* DBName */'dbo', /* DBOwnerName */'yourTable', /* tableName */'yourColumn', /* columnName */0x24A40F6DFB05F379630AC0533E7F6357) /* iv */
AS ENCRYPTED_VALUE
---------------------------------
ENCRYPTED_VALUE
0x9F71F06D9F1E123B68D910F0EC34E8C5
Direct Call to dbo.ing_d_chr_nm420
SELECT dbo.ing_d_chr_nm420(0x9F71F06D9F1E123B68D910F0EC34E8C5, /* inputData */'yourDatabase', /* DBName */'dbo', /* DBOwnerName */
'yourTable', /* tableName */'yourColumn', /* columnName */0x24A40F6DFB05F379630AC0533E7F6357) /* iv */
AS DECRYPTED_VALUE
----------------
DECRYPTED_VALUE
yourCharValue
Similarly, you can use any command line (cl UDFs) or by name (nm UDFs) based on your data types mentioned here.
