How to insert values into a table in sql 2016 whose columns are always encrypted?

Question

I have encrypted few columns in sql 2016 table using column encryption. Now I want to Insert data into that table. I tried creating a stored procedure and executing that procedure with parameters but I am getting following error.

Encryption scheme mismatch for columns/variables '@lastName'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' 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 = 'BROps_TestDB') (or weaker).

Also, I have an existing application where values are inserted in table using Entity framework in SQL 2008 (which we are trying to upgrade to SQl 2016 for always encrypt feature). So, is there any flag or any method through which we can insert data into SQL 2016 (column encrypt) with minimal change in our code ?

I have given sample stored procedure code and execution of that stored procedure.

    CREATE PROCEDURE dbo.AddCustomer
      @CustomerID int,
      @FirstName nvarchar(25),
      @LastName nvarchar(25),
      @SIN nvarchar(11),
      @CreditCardNumber nvarchar(25),
      @EmailAddress nvarchar(50),
      @PhoneNumber nvarchar(25),
      @TerritoryID int
    AS
    BEGIN
     INSERT INTO [dbo].[Customers]
               ([CustomerID]
               ,[FirstName]
               ,[LastName]
               ,[SIN]
               ,[CreditCardNumber]
               ,[EmailAddress]
               ,[PhoneNumber]
               ,[TerritoryID])
         VALUES
               (@CustomerID,
               @FirstName,
               @LastName,
               @SIN,
               @CreditCardNumber,
               @EmailAddress,
               @PhoneNumber,
               @TerritoryID)
    END 

----------------------------------------
    DECLARE @CustomerID int,
    @FirstName nvarchar(25),
    @LastName nvarchar(25),
    @SIN nvarchar(11),
    @CreditCardNumber nvarchar(25),
    @EmailAddress nvarchar(50),
    @PhoneNumber nvarchar(25),
    @TerritoryID int
    SET @CustomerID = 1
    SET @FirstName = 'David'
    SET @LastName = 'Postlethwaite'
    SET @SIN = '12345-3-ee-3'
    SET @CreditCardNumber = '1111-1233-1231-1233'
    SET @EmailAddress = '[email protected]'
    SET @PhoneNumber = '406555'
    SET @TerritoryID = 1
    execdbo.AddCustomer @CustomerID,@FirstName,@LastName,@SIN,@CreditCardNumber,@EmailAddress,
    @PhoneNumber,@TerritoryID

Answer 1

In order to insert data into the Encrypted column directly from SSMS you need to set Parameterization for Always Encrypted to True.

However this feature is not supported in SSMS 2016, so you need to install the newer version of SSMS which could be found here.

Once you get the SSMS 17.0, you need to enable Column Encryption for your connection while you are connecting to the instance.

In order to insert the data into the specified encrypted column, when you open a new query window, do the following:

• Select Query Options from Query menu
• In the Advanced, Select Enable Parameterization for Always Encrypted

Now you will be able to insert data directly into the table from SSMS.

In order to view the encrypted column value in plain text, you need to Enable Column Encryption Setting. To do this, to the following:

• In the Connect to Server dialog
• Select Options
• Go to Additional Connection Parameters
• Enter Column Encryption Setting = Enabled