Sample Header Ad - 728x90

Insert data into Always Encrypted column using stored procedure in MSSQL

0 votes
0 answers
343 views
I am facing an issue while trying to insert data into SQL table which is having encrypted columns in it. Following 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 

GO
When I try to insert record using the above procedure I am getting error as shown below. >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. Do not want to use ADO.Net for SQL connection. Want to implement the same using EntityFramework.
Asked by Prathap r (1 rep)
Jan 22, 2024, 09:34 AM
Last activity: Jan 22, 2024, 11:49 AM