Post reply

Always encryption error

  • April 11, 2018 at 6:40 am

    #403243

    Hi All,

    Seeking out help on SQL 2016 Alwayson feature. One of the tables in Always encrypted.
    When trying to build a dynamic sql it is throwing error message. Anyone any clue of how to fix.

    Table structure.

    CREATE TABLE [dbo].[Sample]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [LoginID] [varchar](15) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
        [BusinessUnit] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
    )

    declare @bu_list [varchar](50) = '''BU1'',''BU2'''
    declare @sql varchar(500) = 'SELECT * FROM [dbo].[Sample] WHERE [BusinessUnit] IN ('+@bu_list+')'
    print @sql
    exec(@sql)

    Error Message:
    Msg 402, Level 16, State 2, Line 16
    The data types varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Agentdev') collation_name = 'Latin1_General_BIN2' and varchar are incompatible in the equal to operator.

    Steps taken but no use
    =======================
    Refered CSS blog on Always encryption
    https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/ 

    •    Declared and initialized in the same statement (inline initialization). SSMS will not parameterize variables declared using separate SET statements.
    •    Initialized using a single literal. Variables initialized using expressions including any operators or functions will not be parameterized.

    Also ensured below
    1.Right-click anywhere in the Query Editor window
    2.Select Connection > Change Connection ….
    3.Click Options >>.
    4.Select the Additional Properties tab,  type Column Encryption Setting=Enabled .
    5.Click Connect.

    3.Parameterization is disabled by default. To enable it: 1.Select Query from the main menu.
    2.Select Query Options….
    3.Navigate to Execution > Advanced.
    4.Select  Enable Parameterization for Always Encrypted.
    5.Click OK.

    Any thoughts on this error message?

    Thanks,
    Sam

  • April 11, 2018 at 10:30 am

    #1986405

    I'm afraid you need to use the latest version of SSMS

    GASQL.com - Focus on Database and Cloud

  • April 11, 2018 at 11:11 am

    #1986419

    Using latest one. Version 17.4.

  • April 11, 2018 at 1:28 pm

    #1986462

    I'm afraid you have to change your code to Parameterization style. For example,
    1. select * from Sample where BusinessUnit='BU1';
    2. DECLARE @s1 VARCHAR(50) ='BU1';
    select * from Sample where BusinessUnit=@s1;

    Statement 1 will not work, while Statement2 works.

    GASQL.com - Focus on Database and Cloud

  • May 30, 2023 at 4:50 pm

    #4200825

    This old post helped me today.

    Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply