Post reply

Can SQL Server decrypt a table that was encrypted by Oracle?

  • September 1, 2009 at 8:46 am

    #214066

    I've got two databases - one oracle (Oracle 10g), the other sql server (SQL Server 2000 (I know... I know...)). The Oracle database has a table that is encrypted using the following:

    DBMS_CRYPTO.ENCRYPT

    (

    src => UTL_I18N.STRING_TO_RAW (in_text, 'AL32UTF8'),

    typ => encryption_type,

    key => key_bytes_raw

    )

    Where 'encryption_type is defined as:

    encryption_type PLS_INTEGER :=

    DBMS_CRYPTO.ENCRYPT_AES256

    + DBMS_CRYPTO.CHAIN_CBC

    + DBMS_CRYPTO.PAD_PKCS5;

    This table is copied from Oracle to SQL Server across a linked server. Once in SQL Server, I'd like to be able to unencrypt the data (SQL Server will have access to the same key).

    Any thoughts on a SQL Server command to unencrypt this Oracle table?

    Thanks,

    Tom

  • September 1, 2009 at 9:26 am

    #1047612

    SQL Server 2000 does not have any built-in ability to do decryption or encryption.

    There are some third-pary add on products that might be able to do what you want, or you could look into use the decryption built into SQL Server 2005 or 2008.

  • September 1, 2009 at 9:32 am

    #1047614

    As I suspected. Thanks.

  • September 1, 2009 at 9:39 am

    #1047622

    for SQL 2000, there is a set of extended stored procedures right here on SSC that have a collection of encryption methods:http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2000encryptiontools/2344/

    the tools include the following:

    The tools are extended stored procedures, stored procedures and user-defined functions that perform the following functions:

    AES (128, 192, or 256 bit) encryption

    DES (56 bit) encryption

    3DES (3x56 bit) encryption

    Blowfish (32 to 448 bit) encryption

    SHA (256, 384, 512 bit) hashing

    Regular expression matching, searching and splitting (with group sub-captures)

    your code looks like it might be able to use the AES256 that is included, but I don't know if Oracle's version of AES256 will encrypt and decrypt tot eh same values as the SQl versions...that you'd have to test.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 1, 2009 at 10:11 am

    #1047643

    Thanks - I'll look into these.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply