Insert data into Always Encrypted column using stored procedure

148 Views Asked by At

I am facing an issue while trying to insert data into a SQL Server table with encrypted columns.

This is my approach.

I have created a table with three columns: [ID], [Name], [Password]. [Name] and [Password] columns are encrypted. with COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [Test_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')

I have set "Column Encryption Setting = Enabled"
I have set "Parameterization for Always Encrypted"
And I have created a procedure to insert records into Test table as follows:

USE [TestDB] GO

CREATE PROCEDURE InsertTest
    @name varchar(50),
    @pwd varchar(max)
AS
BEGIN
    INSERT INTO [dbo].[Test] ([Name], [Password])  
    VALUES (@name, @pwd)
END

When I try to insert a new row using this procedure:

[ EXEC InsertTest 'Test','Test' ]

I get this error:

Msg 206, Level 16, State 2, Procedure InsertTest, Line 0 [Batch Start Line 0]
Operand type clash: varchar is incompatible with varchar(1) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Test_CEK', column_encryption_key_database_name = 'TestDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

I want to insert data into SQL table through stored procedure. I do not want to use ADO.NET for SQL connection.

I want to implement the same functionality using Entity Framework.

This is how I am trying to call the procedure from c# but no luck.

Image

0

There are 0 best solutions below