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.
