none
SQL Server 2016 Always encrypted Insert into Select RRS feed

  • Question

  • Hi, I create a table with four always encrypted columns and try to load  5 million rows data for test performance from another table. But I got errors like :

    Mens. 33277, Nivel 16, Estado 6, Línea 60
    Encryption scheme mismatch for columns/variables '@ape1_pac'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '60' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto3', column_encryption_key_database_name = 'test_ss'). 

    1. I set  'Column Encryption Setting = Enabled'

    2. I checked Parameterization for Always Encrypted

    Here the code :

    declare 
    ,@tdoc_pac    char(1)       
    ,@ndoc_pac    varchar(100)   
    ,@ape1_pac    varchar(30)    
    ,@ape2_pac    varchar(30)    
    ,@nom1_pac    varchar(30)    
    ,@nom2_pac    varchar(30)    
    SELECT 
    ,@tdoc_pac  =  tdoc_pac  
    ,@ndoc_pac  =  ndoc_pac  
    ,@ape1_pac  =  ape1_pac  
    ,@ape2_pac  =  ape2_pac  
    ,@nom1_pac  =  nom1_pac  
    ,@nom2_pac  =  nom2_pac  
    FROM fact_tb
    where rowid =660 
    INSERT INTO  fact_tb_test
    ( [tdoc_pac],[ndoc_pac],,[ape1_pac],[ape2_pac],[nom1_pac],[nom2_pac]
    values 
    ( @tdoc_pac,@ndoc_pac,@ape1_pac,@ape2_pac,@nom1_pac,@nom2_pac)

    But if I set values then its ok the insert 

    declare 
    @tdoc_pac    char(1)    ='ssn'
    ,@ndoc_pac    varchar(100)    =  '451078569503236'
    ,@ape1_pac    varchar(30)    =  'Mend'
    ,@ape2_pac    varchar(30)    ='Sol'

    ,@nom1_pac    varchar(30)    = 'Jorge'
    ,@nom2_pac    varchar(30)    = 'Gonza'

    thnx

    Jose Nadim

    Thursday, July 9, 2020 10:31 PM

Answers

  • Hi all !,  
    Finally I loaded data by export import wizard , next step is load huge data for review performance.
    Here the steps:
    1. I downloaded SSMS 18.5
    2. Export from unencrypted table data.
    The conenction  for this table is by SQL SERVER NATIVE CLIENT 11.0
    3. Import to a table with always encrypted column data.
    The connection from  this table is by NET FRAMEWORK DATA PROVIDER FOR SQL SERVER.
    on the ConnectionStrig property I set :

    Data Source=ssycorapru03;Initial Catalog=PRUEBA_SBS;Integrated Security=True;Password=;Column Encryption Setting=Enabled
    This part is applied  from this link:
    https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-sql-server-data-source-sql-server-import-and-export-wizard?view=sql-server-ver15#connect-to-sql-server-with-the-net-framework-data-provider-for-sql-server

    Thanks for your help.

    Jose Nadin Mendez

    Wednesday, July 15, 2020 5:18 PM

All replies

  • Hi josenadim,

    Which version of SSMS you are using? In SSMS 16.x, queries that insert, update or select data (in the WHERE clause) in encrypted columns  are not supported.  Please using the latest version SSMS 18.5.1 or you can use SSMS 17.9.1. Make sure SSMS is connected to the database with column encryption setting=enabled in the database connection string. And enable “Parameterization for Always Encrypted”  in SSMS.

    Please refer to Parameterization for Always Encrypted - Using SSMS to Insert into, Update and Filter by Encrypted Columns.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, July 10, 2020 8:24 AM
  • Keep in mind that with Always Encrypted, all encyprtion/decryption happens client-side. So if you want to copy an unencrypted table, to a table with encrypted columns, you need need to extract the data to the client and reinsert. (You could use BCP for this.)

    With the script you have, SSMS will understand @ape1_pac maps to an encrypted column, and pass it as such when it sends the batch to SQL Server. And it will understand that when you say:

       DECLARE @ape1_pac    varchar(30)    =  'Mend'

    that it should encrypt Mend before sending it to SQL Server.
    You can see this, if you use Profiler to see what SSMS actually sends to SQL Server.

    But @ape1_pac is still a T-SQL variable. So when you say

      SELECT @ape1_pac = ape_apc
      FROM   unencrypted_table

    SSMS is not going to break up this batch in pieces and retrieve the data from SQL Server and send it back the data.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 10, 2020 9:11 AM
  • Hi, Erland so I understand next :

    1.  From SSMS I cannot execut "insert into slect with variables" queries from unecrypt     to encrypt tables.
    2. only for bpc or apps :  unload data to text file and then load data by bcp  or   net  applications  ok?.
    3. Is possible insert data with export/import dts?" 

    Thnx.

    JOse Nadin

    I cannot execute "insert into select" queries
    I cannot execute "insert into select" queries
    I cannot execute "insert into select" queries
    Friday, July 10, 2020 8:37 PM
  • Hi Cathy  I need download the latest SSMS  ok?.

    Yes I checked query options and I set encryiption setting enable.

    Thnx.

    Jose Nadin

    Friday, July 10, 2020 8:39 PM
  • 1.  From SSMS I cannot execut "insert into slect with variables" queries from unecrypt     to encrypt tables.

    Correct. That does not work out.

    2. only for bpc or apps :  unload data to text file and then load data by bcp  or   net  applications  ok?.

    Yes.

    3. Is possible insert data with export/import dts?" 

    That should work. The key thing is that the data must be bounced outside SQL Server so it can be encrypted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 10, 2020 9:15 PM
  • Hi Jose Nadim,

    Is there any progress? If the reply could help you, please kindly mark the useful reply as answer.This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 13, 2020 9:12 AM
  • Hi

    USE test_ss;

    Go
    OPEN SYMMETRIC KEY SymmetricKey1

    NSERT INTO  fact_tb_test
    ( [tdoc_pac],[ndoc_pac],,[ape1_pac],[ape2_pac],[nom1_pac],[nom2_pac]
    values 
    ( @tdoc_pac,@ndoc_pac,EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,@ape1_pac) ),@ape2_pac,@nom1_pac,@nom2_pac)

    Thanks and Regards

    Monday, July 13, 2020 11:27 AM
  • OPEN SYMMETRIC KEY SymmetricKey1

    NSERT INTO  fact_tb_test
    ( [tdoc_pac],[ndoc_pac],,[ape1_pac],[ape2_pac],[nom1_pac],[nom2_pac]
    values 
    ( @tdoc_pac,@ndoc_pac,EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,@ape1_pac) ),@ape2_pac,@nom1_pac,@nom2_pac)

    Eh? The question was about Always Encrypted. OPEN SYMMETRIC KEY is for the old-style cell encryption and has nothing to do with Alwyas Encrypted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, July 13, 2020 8:43 PM
  • Hi Cathy I downlaoded SSMS 18.5.1. and Failed Here the script

    declare
    @tdoc_pac    char(1)       ,@ndoc_pac    varchar(100)   ,@ape1_pac    varchar(30)    ,@ape2_pac    varchar(30)    ,@nom1_pac    varchar(30)    ,@nom2_pac    varchar(30)    

    SELECT
    @tdoc_pac  =  tdoc_pac  ,@ndoc_pac  =  ndoc_pac  
    ,@ape1_pac  =  ape1_pac  ,@ape2_pac  =  ape2_pac  
    ,@nom1_pac  =  nom1_pac  ,@nom2_pac  =  nom2_pac  
    FROM ma_pacfac    where rowid =660

    INSERT INTO  ma_pacfac
    ( [tdoc_pac],[ndoc_pac],[fech_acc],[ape1_pac],[ape2_pac],[nom1_pac],[nom2_pac])
    values
    (
    @tdoc_pac  ,@ndoc_pac  ,@ape1_pac
    ,@ape2_pac  ,@nom1_pac  ,@nom2_pac  
    )

    Mens. 33277, Nivel 16, Estado 6, Línea 27
    Encryption scheme mismatch for columns/variables '@ape1_pac'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '27' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto3', column_encryption_key_database_name = 'PRUEBA_DB').

    Tuesday, July 14, 2020 8:54 PM
  • Hi , I made the activity with BCP but failed too.

    thnx for your help.

    1.
    CREATE TABLE [dbo].[ma_paciente_factura1001](
        [rowid] [numeric](18, 0) NOT NULL,
        [tdoc_pac] [char](1) NULL,
        [ndoc_pac] [varchar](15) NULL,
        [ape1_pac] [varchar](30) COLLATE Modern_Spanish_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto3], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
        [ape2_pac] [varchar](30) COLLATE Modern_Spanish_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto3], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
        [nom1_pac] [varchar](30) COLLATE Modern_Spanish_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto3], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
        [nom2_pac] [varchar](30) COLLATE Modern_Spanish_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto3], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
        [fecha] [datetime] NULL,
     CONSTRAINT [PK_ma_paciente_factura7878100] PRIMARY KEY CLUSTERED
    (    [rowid] ASC))
    go
    ALTER TABLE [dbo].[ma_paciente_factura1001] ADD  CONSTRAINT [DF_ma_paciente_factura_fecha1780]  DEFAULT (getdate()) FOR [fecha]
    GO

    -- bcp a run from DOS command prompt
    2.
    bcp prueba_sbs.dbo.mapacfac  out d:\borrar\datos.txt -c -t, -r \n -T
    Iniciando copia...

    5 filas copiadas.
    Tamaño del paquete de red (bytes): 4096
    Tiempo de reloj (ms.) Total     : 16     Media: (312.50 filas por s)

    3.
    bcp prueba_sbs.dbo.ma_paciente_factura1001 in d:\borrar\datos.txt -c -t , -r \n -T
    Iniciando copia...
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 13 for SQL Server]Valor de carácter no válido para especificación cast
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 13 for SQL Server]Valor de carácter no válido para especificación cast
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 13 for SQL Server]Valor de carácter no válido para especificación cast
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 13 for SQL Server]Valor de carácter no válido para especificación cast
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 13 for SQL Server]Valor de carácter no válido para especificación cast
    SQLState = 37000, NativeError = 4816
    Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column type from bcp client for colid 4.

    Error en copia BCP in

    Tuesday, July 14, 2020 11:32 PM
  • Hi , I made the activity with BCP but failed too.

    Sorry, I should have checked that out earlier. BCP does not support Always Encrypted. We can tell by running "bcp -?". We would need to see options to enable Always Encrypted, but we don't.

    I suggest that you look at this topic and the one that follows in Books Online to get better understanding of what you can do:
    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-migrate-using-bacpac?view=sql-server-ver15


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 15, 2020 9:04 AM
  • Hi all !,  
    Finally I loaded data by export import wizard , next step is load huge data for review performance.
    Here the steps:
    1. I downloaded SSMS 18.5
    2. Export from unencrypted table data.
    The conenction  for this table is by SQL SERVER NATIVE CLIENT 11.0
    3. Import to a table with always encrypted column data.
    The connection from  this table is by NET FRAMEWORK DATA PROVIDER FOR SQL SERVER.
    on the ConnectionStrig property I set :

    Data Source=ssycorapru03;Initial Catalog=PRUEBA_SBS;Integrated Security=True;Password=;Column Encryption Setting=Enabled
    This part is applied  from this link:
    https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-sql-server-data-source-sql-server-import-and-export-wizard?view=sql-server-ver15#connect-to-sql-server-with-the-net-framework-data-provider-for-sql-server

    Thanks for your help.

    Jose Nadin Mendez

    Wednesday, July 15, 2020 5:18 PM
  • Hi Jose Nadin Mendez,

    I am so glad to hear that you have resolved your issue, in order to close this thread, please kindly mark helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best regards,
    Cathy  

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, July 16, 2020 9:30 AM