locked
How to use the Sql encrypted data in Oracle db. RRS feed

  • Question

  • Hi,

    I am using a sql server 2012 and having a database "Test".In this database , having a table "Employee". I have encrypted the employe table data with the password. How can is use these employee data in oracle server. ie;How can I show the employee data as the encrypted  and decrypted form of data in Oracle server( The actual encryption happened only in SQL DB).

    Thanks

    Dileep 

    Friday, June 26, 2015 9:50 AM

Answers

All replies

  • Hi Dileep,

    Based on my research, you can configure Database Gateway for Microsoft SQL Server in Oracle, then select encrypted data from SQL Server. However, per my knowledge, you cannot decrypt SQL Server data in  Oracle environment.

    In addition, the issue is more related to Oracle, I would recommend you post the question in the Oracle forum to get better support.

    For more details about  Oracle Database Gateway, please review the following articles.
    Getting Started with Oracle Database Gateway
    Steps-by-step: Connecting to Microsoft SQL Server database from Oracle using Database Gateway for SQL Server



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support





    Monday, June 29, 2015 2:57 AM
  • Hi ,

     Is anybody knows how to decrpty the sql data in Oracle server,

    Thanks & Regards,

    Dileep Sanker

    Monday, June 29, 2015 7:17 AM
  • Hi Lydia ,

    First lets go with different Sql Server.Now am having two different SQl server( Instead of One Oracle Server) .How Can read the encrypted data of Employee table in another sql server. Also how can I decrypt the data in another server.Both sql Server are in different Machine.How it is possible?.

    Thanks & Regards,

    Dileep Sanker

    Tuesday, June 30, 2015 9:48 AM
  • Hi Lydia ,

    First lets go with different Sql Server.Now am having two different SQl server( Instead of One Oracle Server) .How Can read the encrypted data of Employee table in another sql server. Also how can I decrypt the data in another server.Both sql Server are in different Machine.How it is possible?.

    Thanks & Regards,

    Dileep Sanker

    Hi Dileep,

    Firstly, you can configure linked server between the two SQL Server instances, then select the encrypted data of Employee table in another SQL Server.

    Secondly, in order to decrypt the data in different SQL Server, you can create the symmetric key specifying the key source and the identity value when encrypting data on the first server. Then when decrypting on the other server, specify the same key source and identity value when creating that symmetric key. For more details, please review this similar thread.

    There is also a blog about creating two identical symmetric keys for your reference.
    http://sqlity.net/en/2492/identical-symmetric-key/

    Thanks,
    Lydia Zhang



    Lydia Zhang
    TechNet Community Support



    Wednesday, July 1, 2015 9:29 AM
  • Hi Lydia,

    Please explain more with  Linked server database.

    Thanks & Regards,

    Dileep

    Thursday, July 2, 2015 9:09 AM
  • Hi Lydia,

    Please explain more with  Linked server database.

    Thanks & Regards,

    Dileep


    Hi Dileep,

    I recommend you follow the step-by-step process in the blog below to set up linked server and read data from the first SQL Server.

    http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

    Thanks,
    Lydia Zhang

    Lydia Zhang
    TechNet Community Support


    Thursday, July 2, 2015 9:55 AM
  • Hi Lydia,

    Thank you for the info .Now I am getting the encrypted form of data in other instances (SECOND INSTANCE OF sql) with the linked server. Here the point the actual problem is started. At this point , need to decrypt the data

    in the second instance of DB server.For example,

    SELECT

    *FROM[ServerName].[Encryption].[dbo].[CreditCardInformation]

     It will display the encrypted form of result.

    But at this point need to Decrypt the CreditCardInformation table.How can decrypt the data from the second instance of SQL server.the problem is , the second instance of SQL server throwing an error when open symtrickey , because the symmetric keys are available only in linked server.How can I achieve the decryption.

    Thanks & Regards,
    Dileep

    Thursday, July 2, 2015 11:55 AM
  • Hi Dileep,

    As my previous post, to decrypt the data in different SQL Server, you need to create the symmetric key specifying the key source and the identity value when encrypting data on the first server. Then when decrypting on the other server, specify the same key source and identity value when creating that symmetric key.

    There is a similar thread for your reference
    http://stackoverflow.com/questions/5249713/how-to-encrypt-on-one-sql-server-and-decrypt-on-another


    Thanks,
    Lydia Zhang

    Lydia Zhang
    TechNet Community Support



    Friday, July 3, 2015 7:02 AM