locked
Always encrypted RRS feed

  • Question

  • I encountered the following error while attempting to insert using tsql in ssms into the table which has an always encrypted column.

    Operand type clash: varchar is incompatible with varchar(20) 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 = 'training') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

    Tuesday, August 28, 2018 10:29 AM

Answers

  • I see. Just for the heck of it, can you try to change the password to something else? As a test. Also, can you declare the variable with the same length as the column in the table? Just guessing here...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Wednesday, August 29, 2018 10:16 AM
    Wednesday, August 29, 2018 10:01 AM
  • What was it that made it work? The correct length for the parameter?

    Yes, SSMS will decrypt the value for you. If you open a query window *without* that value in the connection string you shuld see the encrypted value.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Wednesday, August 29, 2018 10:16 AM
    Wednesday, August 29, 2018 10:13 AM

All replies

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

    /*

    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.

    */

    Also

    https://social.technet.microsoft.com/wiki/contents/articles/37979.working-with-the-always-encrypted-feature-in-sql-server-2016.aspx#Insert_data_from_SSMS


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, August 28, 2018 10:46 AM
  • Hello Uri,

    I followed the steps explained in the above blogs. But still I am unable to insert. Following error is raised:

    Operand type clash: varchar(100) 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 = 'training') collation_name = 'SQL_Latin1_General_CP1_CI_AS' is incompatible with 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 = 'training') collation_name = 'Latin1_General_BIN2'
    Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 27]
    Statement(s) could not be prepared.
    An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@p9181c8dbe5ff4d039012a27709f46042' in statement or procedure 'DECLARE @password AS VARCHAR (100) = @p9181c8dbe5ff4d039012a27709f46042;

    INSERT  INTO aetest
    VALUES ('kesab', @password);

    ' is missing in resultset returned by sp_describe_parameter_encryption.

    I tried inserting the following way:

    declare @password varchar(100)='kesab123'
    insert into aetest values('kesab',@password)

    Thank you.




    • Edited by Curendra Wednesday, August 29, 2018 9:21 AM
    Wednesday, August 29, 2018 9:19 AM
  • The point is that you cannot use SSMS to insert the data into encrypted columnist (AE) instead use  feature called Parameterization for Always Encrypted (SSMS), which, when enabled, maps Transact-SQL variables to query parameters

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, August 29, 2018 9:37 AM
  • I have enabled Parameterization for Always Encrypted, but still I am not able to insert.

    Wednesday, August 29, 2018 9:39 AM
  • Do you also use variables in your script? And modify the connection string for your query window? You will see that the variables (which will be substituted for the encrypted values) will be underlined by blue (as I recall) in SSMS query window.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 29, 2018 9:41 AM
  • Yes, the variables are underlined and conversion message is displayed on hover. In connection, I have used Column Encryption Setting=Enabled.
    Wednesday, August 29, 2018 9:43 AM
  • I see now that there is something strange with your INSERT statement. We generally do them like:

    INSERT INTO tbl (col1, col2) VALULES (12, 'hello')

    Do you have two columns in that table? Do you want to store both the clear text value as well as the encrypted value? That is what your INSERT command is implying.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 29, 2018 9:48 AM
  • I have a table with two columns; username and password. Password in configured for always encrypted. I want to insert rows into it and when I insert, I type plain text for password and I want it to be encrypted in the table. Is anything wrong with my understanding? How can I achieve this?
    Wednesday, August 29, 2018 9:51 AM
  • Right-click over the query and select “Query Options”, then “Advanced” and enable the option 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, August 29, 2018 9:51 AM
  • Uri, I have already done this. I proceeded by completing this step. But still the problem persists.
    Wednesday, August 29, 2018 9:53 AM
  • I see. Just for the heck of it, can you try to change the password to something else? As a test. Also, can you declare the variable with the same length as the column in the table? Just guessing here...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Wednesday, August 29, 2018 10:16 AM
    Wednesday, August 29, 2018 10:01 AM
  • Tibork, it worked now. I am able to insert. But when I select, the encrypted field is showing plain text not the encrypted value.
    Wednesday, August 29, 2018 10:06 AM
  • What was it that made it work? The correct length for the parameter?

    Yes, SSMS will decrypt the value for you. If you open a query window *without* that value in the connection string you shuld see the encrypted value.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Wednesday, August 29, 2018 10:16 AM
    Wednesday, August 29, 2018 10:13 AM
  • I changed the field from password to username and also I used the parameter with same length. I think same length made it work.

    Thank you.

    Wednesday, August 29, 2018 10:15 AM