locked
how to move data from a table, which has encrypted column from one database to another database RRS feed

  • Question

  • I am trying to move data from a table which has encrypted column from sql server management studio to a linked IBM  i, iSeries, AS/400

    I am receiving this error when I try it. I tried running an insert statement from ssms to the linked server table and also tried to use 'Execute SQL Task' in ssis using ADO.NET connection. Both gave me same error:

    Operand type clash: varchar(9) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '', column_encryption_key_database_name = '') collation_name = 'Latin1_General_BIN2' is incompatible with varchar

    the code I am using is :

    insert into IBMTable

    SELECT SSN FROM SQLServerTable

    • Edited by sk12346 Monday, July 10, 2017 4:08 AM
    Monday, July 10, 2017 3:33 AM

Answers

  • Thanks for the reply. When moving data to AS400 I am expecting the data to be decrypted and loaded. Also I am using varchar for both sql server table column and AS400 table column. Shouldn't the data be in varchar even after it is encrypted?

    Nope. Encrypted data is always binary, and typically it is longer than the source data.

    As I said, decryption can only happen client-side - that is the beauty of Always Encrypted. (That is, you can finally hide data from the DBA.) And it is also a beauty of Always Encrypted knows that the data is encrypted, even if it does not know how decrypt (because it does not have access to the keys). So you cannot by mistake put something in cleartext in the column. Or by mistake copy the encrypted bits to some other column where they don't make sense.

    • Marked as answer by sk12346 Tuesday, July 11, 2017 12:59 PM
    Monday, July 10, 2017 9:46 PM
  • "Nope. Encrypted data is always binary, and typically it is longer than the source data."

    But you define it as the unencrypted data type (varchar for instance) in the CREATE TABLE. Then SQL Server does its magic and make some binary stuff under the hood. :-)


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by sk12346 Tuesday, July 11, 2017 12:59 PM
    Tuesday, July 11, 2017 11:03 AM

All replies

  • Keep in mind that with Always Encrypted, encryption and decryption occurs client-side. That is, as long as you are in SQL Server the data is encrypted. Furthermore, SQL Server knows that the data is encrypted and handles the data as it was a specific data type. Thus, it is not likely to be compatible which is on the AS400 side.

    Thus, you need to have a program which connects to SQL Server to read the data and then writes the data to the AS400. If the data is to be encrypted on the AS400, reasonably, you need to re-encrypt it using the methods that AS400 supplies.

    Monday, July 10, 2017 8:00 AM
  • Thanks for the reply. When moving data to AS400 I am expecting the data to be decrypted and loaded. Also I am using varchar for both sql server table column and AS400 table column. Shouldn't the data be in varchar even after it is encrypted?
    Monday, July 10, 2017 12:54 PM
  • Thanks for the reply. When moving data to AS400 I am expecting the data to be decrypted and loaded. Also I am using varchar for both sql server table column and AS400 table column. Shouldn't the data be in varchar even after it is encrypted?

    Nope. Encrypted data is always binary, and typically it is longer than the source data.

    As I said, decryption can only happen client-side - that is the beauty of Always Encrypted. (That is, you can finally hide data from the DBA.) And it is also a beauty of Always Encrypted knows that the data is encrypted, even if it does not know how decrypt (because it does not have access to the keys). So you cannot by mistake put something in cleartext in the column. Or by mistake copy the encrypted bits to some other column where they don't make sense.

    • Marked as answer by sk12346 Tuesday, July 11, 2017 12:59 PM
    Monday, July 10, 2017 9:46 PM
  • "Nope. Encrypted data is always binary, and typically it is longer than the source data."

    But you define it as the unencrypted data type (varchar for instance) in the CREATE TABLE. Then SQL Server does its magic and make some binary stuff under the hood. :-)


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by sk12346 Tuesday, July 11, 2017 12:59 PM
    Tuesday, July 11, 2017 11:03 AM
  • But you define it as the unencrypted data type (varchar for instance) in the CREATE TABLE. Then SQL Server does its magic and make some binary stuff under the hood. :-)

    Nah, SQL Server knows that the data is varchar(9) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '', column_encryption_key_database_name = '') collation_name = 'Latin1_General_BIN2' and gets upset if you try to feed it something else. That is, the client API is expected to pass encrypted data and with the metadata as given in the error message.

    Tuesday, July 11, 2017 1:09 PM