I have a scenario where i need to have literals(hard coded strings) inside proc used against "Always Encrypted" columns, Since this fails with the following error,
Operand type clash: varchar is incompatible with nvarchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto4', column_encryption_key_database_name = 'DBName')
I am trying to do Parameterization for Always Encrypted within the stored proc, similar to below
GO
CREATE PROCEDURE InsertProc
@Var1 nVarchar(20)
As
BEGIN
DECLARE @Plaintext nvarchar(20)='testText'
INSERT INTO testClass(EncryptedCol1,EncryptedCol2,NonEncryptedCol)
VALUES (@Plaintext,@Var1,default)
END
I have also enabled parameterization and also enabled column encryption setting for connection. Still getting the following error when creating procedure,
Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 4] Statement(s) could not be prepared. An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@p3467a2cdc3d547a3be48f46dfc7e9580' in statement or procedure<--my proc code here--> ' is missing in resultset returned by sp_describe_parameter_encryption.
NOTE:I still could execute and insert if i manually run the script which is inside the proc.But Proc creation is issue
Could some one help to fix or provide alternative solution.
The main purpose of Always Encrypted is to make it impossible for SQL Server (hence your DBAs) to decrypt your sensitive data. To achieve this SQL Server has abolutely no access to they encryption keys and can't encrypt and decrypt data. All encryption and decryption is performed by the client (your application, SSMS, etc.). Enable Parameterization for Always Encrypted is a feature of SSMS. When it is enabled (and you specify Column Encryption Setting=Enabled for your connection), SSMS will detect the declared variables in your script, convert it to parametrized query and executes this converted version of it. This way the parameter values will be encrypted on the client and SQL Server will not see their plain text values at all.
In your example, @Plaintext is not a variable declared in your script, but local variable for the stored procedure. So this code (the assigning of the value) will be performed in the SQL Server's engine itself, and since it doesn't has access to the encryption keys, it is simply impossible to encrypt the value. So you need to move this as input parameter for the store procedure too. In this case it will be tempting to give it a default value and omit it when calling the procedure, but the default value is something stored in the metadata and it is in plain (not encrypted). Someone have to encrypt it, and the only one who can do this is the client. Hence, you need to pass it from the client and not to make it a default value, i.e. it is not possible to use "hard-coded values" in the server.
If this is a major obstacle for you, probably Always Encrypted isn't the right technology for your case. If you use lets say column level encryption with certificates, you will be able to do all these things. But this defeats the main advantage of Always Encrypted - the inability your DBA to get access to your secrets.
I found a work around for using hard coded strings in procedures.
Example:
Create table with encrypted column
Insert text into encrypted column the value that you would required to be used inside procedures.
Now you could consume in stored procedure
Voila!!! now you could do anything with just a little twist.