locked
Encrypted columns not working with ISNULL and order by clause RRS feed

  • Question

  • Hi,

     

    I have an existing  table with the following columns:

     

    CREATE TABLE [dbo].[Employee](

           [EmployeeID] [int] IDENTITY(1,1) NOT NULL,

           [Title] [nvarchar](max) NULL,    

           [Location] [nvarchar](500) NULL,

           [Address] [nvarchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

           [Qualifications] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

           [Salary] [money] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,

           [Age] [int] NULL

    )

     

    Now I have to perform the following actions:

    1. Change Address and Qualifications to nullable columns

     

    Alter Employee Alter Address NVARCHAR(MAX)      NULL;

    Alter Employee Alter Qualifications  VARCHAR(MAX)      NULL;

     

    1. Sort by Salary

    Select * from Employee order by Salary

     

    1. Check for null Values

    Select Title, case when address is null then Location Else Address End As Address,ISNULL(Salary,0) from Employee

     

    But all the above SQL statements are giving errors.

     

    Errors:

    1. Operand type clash: nvarchar(max) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name =’TESTDB’) is incompatible with nvarchar(max)
    2. Encryption scheme mismatch for columns/variables 'Salary'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = ’TESTDB’) and the expression near line '91' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
    3. Cannot assign the same encryption scheme to two expressions at line '55'. The encryption scheme of the first expression is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = ’TESTDB’) and the encryption scheme of the second expression is (encryption_type = 'PLAINTEXT'). Other columns/variables with the same encryption scheme as the first expression are: ' Address '. Other columns/variables with the same encryption scheme as the second expression are: 'Location'.

     

    I have another test environment without the encrypted columns where everything works fine.

     

    So, is there a workaround to get the issues fixed from backend without involving any client application?

    Thanks in advance

    • Moved by Tom Phillips Tuesday, October 31, 2017 12:18 PM TSQL question
    Tuesday, October 31, 2017 10:36 AM

Answers

  • You need to do it be enabling parameterization for always encrypted in SQL Management Studio

    See

    https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/

    https://matthewmcgiffen.com/2017/04/10/working-with-data-in-always-encrypted/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ABC30 Wednesday, November 1, 2017 8:51 AM
    Tuesday, October 31, 2017 11:17 AM
  • Hi,

    I have solved few of my problems after installing the certificate on the server.

    For point 1

    Suppose my table is as follows:

    CREATE

    TABLE[dbo].[Employee](


           [EmployeeID] [int]

    IDENTITY(1,1)NOTNULL,


           [Title] [nvarchar]

    (max)NULL,    

           [Location] [nvarchar]

    (500)NULL,


           [Address] [nvarchar]

    (max)COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NOTNULL,


           [Qualifications] [varchar]

    (max)COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NOTNULL,


           [Salary] [money]

    ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Deterministic,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NULL,


           [Age] [int]

    NULL



    )

    I want to make the Address and qualifications columns nulable, then my alter script will go as below:

    ALTER

    TABLEEmployee ALTERCOLUMN  AddressNVARCHAR(MAX)  COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NULL;



    ALTER

    TABLEEmployee ALTERCOLUMNQualifications  VARCHAR(MAX)COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')     NULL;

    For point 2

    I don't know how it executes internally, but I got the order by working with the TOP clause.

    Select

    top100 *fromEmployee orderbySalary

    And I chose my top (?) accordingly. For example,  if I had actually 10-12 rows returned by the query , I took top 100.

    For point 3

    As I mentioned before, I got ISNULL and CASE working with parameterized query. But it does not work when placed in a stored proc.

    • Marked as answer by ABC30 Wednesday, November 8, 2017 7:28 AM
    Wednesday, November 8, 2017 7:28 AM
  • I don't know how it executes internally, but I got the order by working with the TOP clause.

    Select

    top100 *fromEmployee orderbySalary

    And I chose my top (?) accordingly. For example,  if I had actually 10-12 rows returned by the query , I took top 100.
    • Marked as answer by ABC30 Wednesday, November 8, 2017 7:29 AM
    Wednesday, November 8, 2017 7:29 AM

All replies

  • You need to do it be enabling parameterization for always encrypted in SQL Management Studio

    See

    https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/

    https://matthewmcgiffen.com/2017/04/10/working-with-data-in-always-encrypted/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ABC30 Wednesday, November 1, 2017 8:51 AM
    Tuesday, October 31, 2017 11:17 AM
  • For your second Error,

    Ordering is not supported on encrypted columns.

    See this article for details

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine


    Thanks for reading!
    If you found my answer useful please mark as answered
    David

    Tuesday, October 31, 2017 11:23 AM
  • Thanks vishakh for the reply.

    I took references from the second link and I am trying to create exactly the same example given there. But still getting error.

    I created this table.

    CREATE TABLE [dbo].[EncryptedTable](
    Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED,
    LastName [nvarchar](32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK_1],
    ENCRYPTION_TYPE = Deterministic,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NULL,
    FirstName [nvarchar](32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK_1],
    ENCRYPTION_TYPE = Randomized,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
    );

    And tried to insert values.

    INSERT INTO dbo.EncryptedTable (LastName, FirstName)
    VALUES ('McGiffen','Matthew');

    It gave error as expected:

    Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = 'TestDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

    Now I tried to used parameterized query:

    declare @LastName nvarchar(32)='McGiffen'
    declare @FirstName nvarchar(32)='Matthew'
    INSERT INTO dbo.EncryptedTable (LastName, FirstName)
    VALUES (@LastName,@FirstName);

    and did these two settings changes:

    “Column Encryption Setting = Enabled”

    AE_Parametization.png

    But still getting below error:

    Encryption scheme mismatch for columns/variables '@LastName'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '4' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = 'TestDB') (or weaker).

    Is there anything else that need to be done?

    I am using sql server 17.

    Wednesday, November 1, 2017 5:43 AM
  • Thanks vishakh for the reply.

    I took references from the second link and I am trying to create exactly the same example given there. But still getting error.

    I created this table.

    CREATE TABLE [dbo].[EncryptedTable](
    Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED,
    LastName [nvarchar](32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK_1],
    ENCRYPTION_TYPE = Deterministic,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NULL,
    FirstName [nvarchar](32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK_1],
    ENCRYPTION_TYPE = Randomized,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
    );

    And tried to insert values.

    INSERT INTO dbo.EncryptedTable (LastName, FirstName)
    VALUES ('McGiffen','Matthew');

    It gave error as expected:

    Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = 'TestDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

    Now I tried to used parameterized query:

    declare @LastName nvarchar(32)='McGiffen'
    declare @FirstName nvarchar(32)='Matthew'
    INSERT INTO dbo.EncryptedTable (LastName, FirstName)
    VALUES (@LastName,@FirstName);

    and did these two settings changes:

    “Column Encryption Setting = Enabled”

    AE_Parametization.png

    But still getting below error:

    Encryption scheme mismatch for columns/variables '@LastName'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '4' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = 'TestDB') (or weaker).

    Is there anything else that need to be done?

    I am using sql server 17.

    Are you sure you tried it in a new window opened after turning the settings on?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, November 1, 2017 6:34 AM
  • Hi Vishakh,

    Thanks for your time.

    It looked like a certificate error. 

    I deleted all the old certificates, generated a new certificate, exported it and then installed it. 

    All the other settings were same.

    So, this solves my first problem.

    Is there any work around for 2nd and 3rd point?

    Further I have certain more computations also on these encrypted columns but currently simple case and ISNULL is also not working......

    Wednesday, November 1, 2017 8:51 AM
  • Hi Vishakh,

    Thanks for your time.

    It looked like a certificate error. 

    I deleted all the old certificates, generated a new certificate, exported it and then installed it. 

    All the other settings were same.

    So, this solves my first problem.

    Is there any work around for 2nd and 3rd point?

    Further I have certain more computations also on these encrypted columns but currently simple case and ISNULL is also not working......

    2 and 3 are due to this

    • Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (e.g. greater/less than, pattern matching using the LIKE operator, or arithmetical operations).

    • Queries on columns encrypted by using randomized encryption cannot perform operations on any of those columns. 

    Refer

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, November 1, 2017 11:20 AM
  • Hi,

    I got the ISNULL working using parameterized query.

    Select Title, case when address is null then Location Else Address End As Address,ISNULL(Salary,0) from Employee

    For the above query, I replaced as below:

    DECLARE @salary Money=0

    Select Title, case when address is null then Location Else Address End As Address, ISNULL(Salary,@salary ) from Employee

    AND IT WORKED. :)

    Wednesday, November 1, 2017 1:02 PM
  • Hi,

    I have solved few of my problems after installing the certificate on the server.

    For point 1

    Suppose my table is as follows:

    CREATE

    TABLE[dbo].[Employee](


           [EmployeeID] [int]

    IDENTITY(1,1)NOTNULL,


           [Title] [nvarchar]

    (max)NULL,    

           [Location] [nvarchar]

    (500)NULL,


           [Address] [nvarchar]

    (max)COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NOTNULL,


           [Qualifications] [varchar]

    (max)COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NOTNULL,


           [Salary] [money]

    ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Deterministic,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NULL,


           [Age] [int]

    NULL



    )

    I want to make the Address and qualifications columns nulable, then my alter script will go as below:

    ALTER

    TABLEEmployee ALTERCOLUMN  AddressNVARCHAR(MAX)  COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')NULL;



    ALTER

    TABLEEmployee ALTERCOLUMNQualifications  VARCHAR(MAX)COLLATELatin1_General_BIN2 ENCRYPTEDWITH (COLUMN_ENCRYPTION_KEY=[CEK_1],ENCRYPTION_TYPE=Randomized,ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256')     NULL;

    For point 2

    I don't know how it executes internally, but I got the order by working with the TOP clause.

    Select

    top100 *fromEmployee orderbySalary

    And I chose my top (?) accordingly. For example,  if I had actually 10-12 rows returned by the query , I took top 100.

    For point 3

    As I mentioned before, I got ISNULL and CASE working with parameterized query. But it does not work when placed in a stored proc.

    • Marked as answer by ABC30 Wednesday, November 8, 2017 7:28 AM
    Wednesday, November 8, 2017 7:28 AM
  • I don't know how it executes internally, but I got the order by working with the TOP clause.

    Select

    top100 *fromEmployee orderbySalary

    And I chose my top (?) accordingly. For example,  if I had actually 10-12 rows returned by the query , I took top 100.
    • Marked as answer by ABC30 Wednesday, November 8, 2017 7:29 AM
    Wednesday, November 8, 2017 7:29 AM