Results 1 to 9 of 9

Thread: Decrypt Data

  1. #1
    Join Date
    Feb 2004
    Posts
    214

    Lightbulb Unanswered: Decrypt Data

    I have encrypted data in a MySQL Database. I have created a linked server in SQL Server 2012 and I need to decrypt some of the data. I have the key but I am unsure how to begin decrypting. Any and all assistance is greatly appreciated.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I'm not familiar with MySQL encryption, but I think the decryption would be part of the OPENQUERY call to the linked server.

  3. #3
    Join Date
    Feb 2004
    Posts
    214
    I'm in the same boat. I know very little of MySQL. The data is encrypted AES 256 on the MySQL side. I know the key but I don't have a certificate and the SQL Server database doesn't have the key locally stored.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If this is a symmetric key and you have Enterprise edition, you can try to get the key over to SQL Server using Extensible Key Management. I have not used this feature myself, so you would be blazing a bit of your own path.

    If this is an Asymmetric key, there are a few options for importing the key to SQL Server that are not bound by Version. See the Create Asymmetric key page for more details on what methods are available.
    Last edited by MCrowley; 05-26-15 at 12:18.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would assume that the data is encrypted at the MySQL field level, using the MySQL Encryption Functions. If that is the case, then you will need to code the AES_Decrypt() call within the MySQL query that you pass via OpenQuery() to the MySQL server. As a really crude example:
    Code:
    SELECT *
       FROM OpenQuery(MySQLLinkedServerName, 'SELECT unencrypted_column, AES_Decode(AES_encrypted_column, ''AES Key string goes here'') AS Decrypted_Column
       FROM MySQL_Table_name_goes_here;) AS oq
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Posts
    214
    Pat, I appreciate the sample code. I was able to get the query to run as
    Select *
    FROM OpenQuery (MySQLLinkedServerName, 'Select AES_Decrypt(''Key'')
    FROM MySQL_Table_Name)

    But it returns all null values. Any ideas on why that would happen. Again, appreciate the help from everyone so far.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please take a second look at my example code, particularly one column that was plain text and one column that was encrypted... You provided the key, but not the encrypted column data! AES_Decrypt() only decrypts a single column, so you need to call it for each column that has been encrypted. At least in the databases that I've used, only one or two of the columns are encrypted and the remainder are left un-encrypted.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2004
    Posts
    214
    Same result. My code now looks like this (the select * reverts to my two fields)
    SELECT EncryptedField, DecryptedField
    FROM OPENQUERY(LinkedServerName, SELECT(EncryptedField, AES_Decrypt(EncryptedField,''Key'') as DecryptedField FROM Database.Table') AS DerivedTbl_1

    My output shows the encrypted data and the decrypted column shows nulls across the board.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  9. #9
    Join Date
    Feb 2004
    Posts
    214
    Ok, so new information I just learned from my developer. It wasn't encrypted in MySQL. He encrypted it in PHP and then inserted it into MySQL which would likely be why AES_Decrypt isn't returning any values.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •