Encrypted columns in SQL Server database

Nicky 116

I have to retrieve data from database (DB1) which has encrypted columns.

I have written a stored proc (as my query has lot of temp tables) to extract data from DB1 and to insert it to tables in database DB2.

In SSIS package or direct insert from SQL Server I get this error:

Operand type clash: nvarchar(255) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColoumnEncryptionKey', column_encryption_key_database_name = 'DB1') is incompatible with nvarchar

I have tried using ADO.NET connection but no success.
I would really appreciate any help here.

I have installed certificate on my machine.

Thanks!
Nikz

Sean Gallardy - MSFT 1,886 Reputation points Microsoft Employee

2021-02-17T13:37:53.457+00:00

I'm confused, you wrote a stored procedure... where? It then directly inserts into another database or copies the rows to ssis which then has another step to insert them somewhere else?
Nicky 116 Reputation points

2021-02-17T15:39:09.277+00:00

Stored proc select rows and insert to destination database. In SSIS I am using ADO.NET connection with Column Encryption settings enabled.

I have also tried using OLEDB Connection and selecting rows from storedproc and inserting using OLEDB Destination in to destination database but no success.
tibor_karaszi@hotmail.com 4,306 Reputation points

2021-02-17T16:59:32.927+00:00

Strip it down to bare minimum. Create an SSIS package with only one Data Flow Task, that does a plain SELECT TOP(100) * from a table with some encrypted column and stores the result in a flat file target. Make sure that you have the proper connection string attribute and that the certificate is available from wherever the SSIS package is executed.

Does it work now? The answer to that question will determine your next step.

And, as Sean says (paraphrased): the data need to pass through a client so it can decrypt the data - which rules out a pure T-SQL solution.

Nicky 116

Thanks !

I did some workaround using ADO.NET

       In first solution I am able to achieve this by passing direct query in ADO.NET source. But my query is too long and have many temp tables which I coverted to table variables and it worked fine.
   In second solution I used storedproc Execute sql task(ADO.NET connection) and using foreach loop. This is going to take ages to import data.
     Please help me if there is a wayaround to use storedprocedure without using foreachloop?
      My query return 100000+ records and my query being so  long and using so many temp tableswould like to keep in storedproc.

1 answer

Sean Gallardy - MSFT 1,886 Reputation points Microsoft Employee

2021-02-17T16:52:15.497+00:00

Stored proc select rows and insert to destination database.

That's never going to work because that's not how Always Encrypted works. In order for Always Encrypted to be used, the client driver is what does the decryption and encryption processes, in your scenario the result set is never returned to the client driver but is attempted to be directly inserted into a different table, this will fail as it has.

I edited the public Docs article on this a few weeks ago to better help those understand this, please reference it here under 'Remarks':

Encryption and decryption occurs via the client driver. This means that some actions that occur only server-side will not work when using Always Encrypted. These actions include (but are not limited to):

Copying data from one columng to another via an UPDATE, BULK INSERT(T-SQL), SELECT INTO, INSERT..SELECT.
Triggers, temporal tables, sparse columns, full-text, in-memory OLTP, and Change Data Capture (CDC).
Please sign in to rate this answer.
Nicky 116 Reputation points

2021-02-18T09:22:23.42+00:00

Thanks !

I did some workaround using ADO.NET

In first solution I am able to achieve this by passing direct query in ADO.NET source. But my query is too long and have many temp tables which I coverted to table variables and it worked fine. In second solution I used storedproc Execute sql task(ADO.NET connection) and using foreach loop. This is going to take ages to import data. Please help me if there is a wayaround to use storedprocedure without using foreachloop? My query return 100000+ records and my query being so long and using so many temp tableswould like to keep in storedproc.

Sean Gallardy - MSFT 1,886 Reputation points Microsoft Employee

2021-02-18T12:20:11.627+00:00

It doesn't matter the construct used, all the data will first need to be exported, then imported. That's how Always Encrypted works. It sounds more like you don't want to use Always Encrypted.

Nicky 116 Reputation points

2021-02-21T16:20:15.597+00:00

Hi Sean

The data I have consist of client data and is encrypted so I do not have any option.

The select query I have to write to export data from our OLTP database to reporting database is very complex with many temp tables so want to keep it stored procedure. Problem with stored proc is reading data in ADO.NET source. I can see the data when I click Preview but cannot see column names in column tab.

Thanks !
Sign in to comment

Share via

Encrypted columns in SQL Server database

1 answer