locked
Using Union with Always Encrypted Enclave RRS feed

  • Question

  • I'm following instructions from https://docs.microsoft.com/en-us/sql/relational-databases/security/tutorial-getting-started-with-always-encrypted-enclaves?view=sqlallproducts-allversions to try the new Always Encrypted Enclave feature out. After I encrypted the [SSN] column, I found out that UNION doesn't work with encrypted columns:

    Sample SQL script:

    select [SSN]
    from Employees
    union
    select [SSN]
    from Employees

    Error messages:


    Msg 33277, Level 16, State 2, Line 25
    Encryption scheme mismatch for columns/variables 'LastName'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK Contoso1', column_encryption_key_database_name = 'ContosoHR') and the expression near line '2' expects it to be DETERMINISTIC, or PLAINTEXT.
    Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 23]
    Statement(s) could not be prepared.
    Msg 33277, Level 16, State 2, Line 25
    Encryption scheme mismatch for columns/variables 'LastName'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK Contoso1', column_encryption_key_database_name = 'ContosoHR') and the expression near line '2' expects it to be DETERMINISTIC, or PLAINTEXT.
    

    Is this a bug or an unsupported feature by design?

    Friday, June 28, 2019 1:32 PM

All replies

  • I would guess it is by design. Or maybe we should call it a limitation. If you read the error message, it says that DETERMINISTIC is amount the expected values.

    Keep in mind that UNION implies DISTINCT, so SQL Server must examine the values to find duplicates. With deterministic encryption this is possible to perform on the encrypted values, but not on the randomized values.

    Or more precisely, it is not possible to perform outside the enclave, but I don't think they have implemented support for operations inside the enclave.


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

    Friday, June 28, 2019 9:43 PM
  • Your explanation makes sense. Unfortunately this means that the new enclave feature still can't solve the long time issue with Entity Framework when generated queries have union, e.g. https://github.com/aspnet/EntityFramework6/issues/265
    Saturday, June 29, 2019 8:53 AM
  • Hi ThuanNguy,

     

    Did you use ssms to execute the query? What version of ssms are you using? 

     

    SSMS 17.0 introduces the new feature, called Parameterization for Always Encrypted , which, when enabled, maps Transact-SQL variables to query parameters ( SqlParameter objects, in .NET - SSMS uses .NET Framework Data Provider for SQL Server) and it refactors queries, using Transact-SQL variables, into parameterized statements.

     

    Would you please try to enable Parameterization for Always Encrypted on ssms:

     

     

     

     

    For more details, please refer to https://techcommunity.microsoft.com/t5/Azure-SQL-Database/Parameterization-for-Always-Encrypted-Using-SSMS-to-Insert-into/ba-p/386124

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    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 1, 2019 8:24 AM
  • Hi Dedmon Dai,

    I'm using SSMS 18.1. The UNION case that failed above didn't have any parameter at all.

    Best Regards,

    NDT.

    Monday, July 1, 2019 10:48 AM