CODEWITHSUNDEEP
sqlsql-serveroracle-databaseencryption
Gary Russo
Gary Russo

Reputation: 355

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

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:

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

Upvotes: 0

Views: 3808

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

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.

Upvotes: 1

Related Questions