locked
SQL Always Encrypted Column Issue RRS feed

  • Question

  • User1765954050 posted

    I have a column [Amount] of type float in my table, I encrypted it using Always Encrypted, everything works fine I can even see plain text value if I use Column Encryption Setting=Enabled in SSMS.

    Now I have a SP, I'm trying to get that encrypted column value in a variable but SSMS not letting me compile my function it says.

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

    This is how I'm assigning value to variable

    Declare @value AS FLOAT
    SET @value = (select top 1 Amount from tbl)

    Any help will be appreciated.

    Thanks

    Wednesday, September 12, 2018 12:07 PM

All replies

  • User283571144 posted

    Hi smtaqi ,

    As far as I  know,  if you send plaintext to the SP, it will show this error.

    Since you have already set the Amount SQL Server expects the value targeting the Amount column to be encrypted, not in plaintext.

    So you couldn't set the encrypted value to plaintext, it will show this error.

    If you are using Sql Server Management Studio to execute your stored procedure, I suggest you could enable Parameterization for Always Encrypted.

    More details about how to enable the Parameterization , you could refer to below article.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio?view=sql-server-2017#Anchor_0 

    Best Regards,

    Brando

    Thursday, September 13, 2018 5:36 AM
  • User364663285 posted

    Dear,

    See example below to work with encrypted column

    USE AdventureWorks2012;  
    
    GO  
    
    CREATE CERTIFICATE Sales09  
       WITH SUBJECT = 'Customer Credit Card Numbers';  
    GO  
    
    CREATE SYMMETRIC KEY CreditCards_Key11  
        WITH ALGORITHM = AES_256  
        ENCRYPTION BY CERTIFICATE Sales09;  
    GO  
    
    -- Create a column in which to store the encrypted data.  
    ALTER TABLE Sales.CreditCard   
        ADD CardNumber_Encrypted varbinary(128);   
    GO  
    
    -- Open the symmetric key with which to encrypt the data.  
    OPEN SYMMETRIC KEY CreditCards_Key11  
       DECRYPTION BY CERTIFICATE Sales09;  
    
    -- Encrypt the value in column CardNumber using the  
    -- symmetric key CreditCards_Key11.  
    -- Save the result in column CardNumber_Encrypted.    
    UPDATE Sales.CreditCard  
    SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
        , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary  
        , CreditCardID)));  
    GO  
    
    -- Verify the encryption.  
    -- First, open the symmetric key with which to decrypt the data.  
    
    OPEN SYMMETRIC KEY CreditCards_Key11  
       DECRYPTION BY CERTIFICATE Sales09;  
    GO  
    
    -- Now list the original card number, the encrypted card number,  
    -- and the decrypted ciphertext. If the decryption worked,  
    -- the original number will match the decrypted number.  
    
    SELECT CardNumber, CardNumber_Encrypted   
        AS 'Encrypted card number', CONVERT(nvarchar,  
        DecryptByKey(CardNumber_Encrypted, 1 ,   
        HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))  
        AS 'Decrypted card number' FROM Sales.CreditCard;  
    GO  

    Thursday, September 13, 2018 7:36 AM