locked
Using Always Encrypted RRS feed

  • Question

  • Hi, I’m using sql 2016 Always Encrypted.

    I have a column called PinNumber nvarchar(60)

    The column is encrypted, but I received an error;

    Operand type clash: nvarchar(60) is incompatible with nvarchar(60) encrypted with (encryption_type = 'DETERMINISTIC',

    If I change the column to nvarchar(Max) everything works ok.

    If I use encryption do I need to set the field size to nvarchar(Max) or am I doing something wrong?

    Tuesday, August 28, 2018 10:00 PM

Answers

  • I believe that SQL Server figures out the sizing.

    You certainly do not need nvarchar(MAX) for always encrypted, so it sounds like you are doing something wrong. Can you show us your connection string?


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

    • Marked as answer by brucey54 Thursday, August 30, 2018 1:55 PM
    Wednesday, August 29, 2018 9:56 PM

All replies

  • You might check out these articles that outline the limitations of Always Encrypted as relates to implementing deterministic encryption:
    Always Encrypted Limitations
    Limitations of Always Encrypted

    HTH,

     

    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Wednesday, August 29, 2018 2:01 AM
    Wednesday, August 29, 2018 1:59 AM
  • Hi brucey54,

     

    Could you please tell us what operation you executed caused the error. Based on my test, I created a table, and implemented Always Encrypted on one column. Then I inserted the data or updated the encrypted the column via using standard query in SSMS, I got the similar error like the following screenshot.

     

     

     

     

    Inserting data into a table with Always Encrypted and updating the encrypted the column via the standard queries, even in SSMS are not allowed.

     

    To edit the data you can use a parameterised query like stored procedures. Besides, you can’t edit encrypted columns in SSMS, because writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1. Please using one of the supported drivers such as PowerShell or .NET application using ADO.NET 4.6 to write Always Encrypted. For more detailed information, you can refer to the document. http://www.sqlservercentral.com/blogs/sqldbauk/2017/04/24/always-encrypted/

     

    By the way, you can also writing Always Encrypted data with Integration Services, please refer to the link. https://www.mssqltips.com/sqlservertip/4162/writing-always-encrypted-data-with-integration-services-2016/

     

    Best Regards,

    Emily


    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

    Wednesday, August 29, 2018 3:29 AM
  • I was displaying data through a MVC.net web application.

    I fund this link which includes a formula to work out the size of the nvarchar field.

    aead_aes_256_cbc_hmac_sha_256 = versionbyte + MAC + IV + aes_256_cbc_ciphertext

    I'm having some trouble understanding the formula, can you confirm my understanding is correct i.e. encryption fields needs to be carefully calculated?

    Link below;

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-cryptography?view=sql-server-2017#ciphertext-length

       

    Wednesday, August 29, 2018 9:06 AM
  • I believe that SQL Server figures out the sizing.

    You certainly do not need nvarchar(MAX) for always encrypted, so it sounds like you are doing something wrong. Can you show us your connection string?


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

    • Marked as answer by brucey54 Thursday, August 30, 2018 1:55 PM
    Wednesday, August 29, 2018 9:56 PM