sql - T-SQL 将数据插入加密列

Question

根据大量研究和本文档，看来我下面的存储过程应该可以工作。但是，无论是直接在 SQLServer17 中调用还是通过 ADO 连接调用，我都会收到此错误。

从数据类型 varchar 到 varchar(255) 的隐式转换使用 (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AutoStripes_Machine_CEK', column_encryption_key_database_name = 'AutoStripes') collat​​ion_name = 'CI_SQL_Latin_1_General_CP1'是不允许的

我已Column Encryption Setting = Enabled在查询选项中选中并启用始终加密的参数化，并且我在使用的机器上有密钥。

存储过程如下。我使用游标是因为不可能加密临时表 AFAIK，所以我想参数化 VIN 以及在插入记录后对这些字段执行更新语句的条件。

USE AutoSTripes;
GO 

CREATE PROCEDURE [dbo].[loadVehicles] (@xmlString XML)

AS 

BEGIN

    DECLARE @tempVehs TABLE 
    (
        [ProductNo] [bigint],
        [VersionNo] [bigint],
        [DateAdded] [smalldatetime],
        [UserID] [varchar](255),
        [VehicleYear] [varchar](255),
        [Make] [varchar](255),
        [Model] [varchar](255),
        [VINNumber] [varchar](255), -- matches column that is encrypted
        -- .... more columns
        [VehicleSeqNo] [bigint],
        [CollDed] [varchar](255)
)

    INSERT INTO @tempVehs   
    SELECT
         XD.xTbl.value('./Product[1]', 'bigint') as ProductNo
         ,XD.xTbl.value('./Version[1]', 'bigint') as VersionNo
         ,XD.xTbl.value('./DateAdded[1]', 'smalldatetime') as DateAdded
         ,XD.xTbl.value('./UserID[1]', 'varchar(255)') as UserID
         ,XD.xTbl.value('./VehYear[1]', 'varchar(255)') as VehiclYear
         ,XD.xTbl.value('./Make[1]', 'varchar(255)') as Make
         ,XD.xTbl.value('./Model[1]', 'varchar(255)') as Model
         ,XD.xTbl.value('./Vin[1]', 'varchar(255)') as VinNumber
         -- ... more columns
         ,XD.xTbl.value('./SeqNo[1]', 'bigint') as VehicleSeqNo
         ,XD.xTbl.value('./CollDed[1]', 'varchar(255)') as CollDed
    FROM
        @xmlString.nodes('//VehicleList/Vehicle') AS XD(xTbl)

    INSERT INTO dbo.tVehicle
        (ProductNo, VersionNo, DateAdded, UserID, VehicleYear, Make, Model, GLCity, GLState, GLZip, VehicleType, Radius, Class, VehicleSeqNo, PIP, AddlPIP, MedPay, UMUIM, UMPD, OTCCov, OTCDed, ACVorStated, CollCov, CollDed) 
        SELECT
            ProductNo, VersionNo, DateAdded, UserID, VehicleYear, Make, Model, GLCity, GLState, GLZip,  VehicleType, Radius, Class, VehicleSeqNo, PIP, AddlPIP, MedPay, UMUIM, UMPD, OTCCov, OTCDed, ACVorStated, CollCov, CollDed
        FROM
            @tempVehs


    -- loop temp table and update VIN for each unique record    
    DECLARE @vin varchar(255)
    DECLARE @product bigint
    DECLARE @version bigint
    DECLARE @sequence bigint

    DECLARE @curs CURSOR

    BEGIN

        SET @curs = CURSOR FOR SELECT ProductNo, VersionNo,  VinNumber, VehicleSeqNo FROM @tempVehs
        OPEN @curs
        FETCH NEXT FROM @curs INTO @product, @version, @vin, @sequence

        Set @product = @product
        Set @version = @version
        Set @vin = @vin
        Set @sequence = @sequence

        UPDATE dbo.tVehicle SET VINNumber = @vin WHERE ProductNo = @product AND VersionNo = @version and VehicleSeqNo = @sequence

    END

    close @curs
    deallocate @curs

END

GO

我错过了什么？还是根本不可能？

标签： sqlsql-serverencryptionado