如何将值插入到始终加密列的sql 2016中的表中？ [英] How to insert values into a table in sql 2016 whose columns are always encrypted?

问题描述

我已使用列加密对sql 2016表中的几列进行了加密。现在，我想将数据插入该表。我尝试创建存储过程并使用参数执行该过程，但出现以下错误。

列/变量的加密方案不匹配'@姓'。列/变量的
加密方案为（encryption_type =
'PLAINTEXT'），第'0'行附近的表达式期望它为
（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'）（或更弱的
）。

另外，我有一个现有的应用程序，其中使用SQL 2008中的Entity Framework（表将值始终插入到SQl 2016中以始终加密）将值插入表中。
那么，是否有任何标记或方法可以通过最小的代码更改将数据插入SQL 2016（列加密）？

我给出了示例存储过程代码并执行了该存储过程。

 创建过程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 
开始
插入[dbo]。[客户] 
（[CustomerID] 
，[FirstName] 
，[LastName] 
，[SIN] 
，[CreditCardNumber] 
，[EmailAddress] 
，[PhoneNumber] 
，[TerritoryID]）
值
（@CustomerID，
 @名字，
 @姓氏，
 @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 ='[e-mail]'
 SET @PhoneNumber ='406555'
 SET @TerritoryID = 1 
 execdbo.AddCustomer @ CustomerID，@ FirstName，@ LastName，@ SIN ，@信用卡号，@电子邮件地址，
，电话号码，@ TerritoryID 
  

解决方案

为了直接从SSMS将数据插入加密列，您需要将始终加密的参数化设置为 True 。

但是，SSMS 2016不支持此功能，因此您需要安装更新版本的SSMS，可以在

现在您将可以直接将数据插入

要查看纯文本格式的加密列值，您需要启用 列加密设置。为此，请执行以下操作：

• 在连接到服务器对话框中


• 选择选项


• 转到其他连接参数


• 输入列加密设置=启用

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 = '[e-mail]'
    SET @PhoneNumber = '406555'
    SET @TerritoryID = 1
    execdbo.AddCustomer @CustomerID,@FirstName,@LastName,@SIN,@CreditCardNumber,@EmailAddress,
    @PhoneNumber,@TerritoryID

解决方案

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

这篇关于如何将值插入到始终加密列的sql 2016中的表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！