How to decrypt encrypted Oracle data that was replicated to SQL Server 2008?

Question

I have a case where Oracle data (phone numbers) was encrypted using the Oracle DBMS_OBFUSCATION_TOOLKIT DESEncrypt function with a unique user generated hash key.

SQL Example:

 update Phone
   set
     encrypted_phone = WEBX_ENCRYPT_DECRYPT.ENCRYPT(
           '212-555-1201', '8IcrEuOdDjRT5iDjqHLcsA==')
   where
     person_id = 12000039742;

The unique customer hash key ('8IcrEuOdDjRT5iDjqHLcsA==') is calculated from the username.

The WEBX_ENCRYPT_DECRYPT.ENCRYPT function calls the following Oracle package function.

   DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input:  input_string,
                                       input:  key_string,
                                       output: encrypted_string)

This encrypted phone data was then replicated to a SQL Server 2008 database.

Oracle encryption info:

• Encryption done using Oracle Obfuscation Toolkit DES Encrypt function.
• Uses DES stream cipher.
• DES Encrypt function uses a 256 bit hash key (only 56 of the 256 bits are used).

Is there an equivalent SQL Server 2008 function that can be used to decrypt the Oracle encrypted phone strings?

Answer 1

No. While DES decryption is available in SQL Server, the SQL Server decryption functions expect the data to be in a format as the one created by the EncryptByKey function. the format is proprietary and not public afaik.

Your best bet is to decrypt the data in Oracle and push it decrypted into SQL. Otherwise you'd need to decrypt it using some out of bad method, eg. a SQL CLR function that understands the Oracle format.