2

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.

Samuel A C
  • 406
  • 3
  • 16
  • You can't do this. The way it's built is around the plaintext for encrypted columns *never* appearing on the server (until 2019 with secure enclaves, but that just moves a few things around and it still doesn't appear as plaintext outside of the enclave) – Damien_The_Unbeliever Nov 01 '18 at 14:00
  • The reason it works when you do it manually is because SSMS goes out of its way to translate the command to one that properly uses encryption. Unfortunately that can only work client-side. It isn't possible to have the server insert a plaintext value into the encrypted column, because if it was, that would imply the server knows the encryption key, which is contrary to the whole idea of Always Encrypted. – Jeroen Mostert Nov 01 '18 at 14:01
  • 1
    So is there no scenarios to work encrypted columns against hard coded literals? Is sending all literals as parameter from application the only option? I badly need work around!! – Samuel A C Nov 01 '18 at 14:06
  • 2
    I know you're focussing on your particular use case at the moment, but consider a stored procedure which allows someone to transfer money between accounts, where the account numbers are encrypted. Always Encrypted is meant to protect our data from, among others, the database administrators. Would you really want one of them to be able to *temporarily* change the definition of this stored procedure so that it uses a hard-coded literal of *their* account number for the recipient? – Damien_The_Unbeliever Nov 01 '18 at 14:11
  • Hmm, I get it, I should start a tough journey of reworking my scenarios. – Samuel A C Nov 01 '18 at 14:37

2 Answers2

4

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 the 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 of your DBA to get access to your secrets.

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
2

I found a work around for using hard coded strings in procedures.

  1. Create a table with a nVarchar column(Note: encrypted nvarchar is not compatable with encrypted varchar, Also you could not assign value from higher data length to lower data length encrypted values(Eg you could not assign a encrypted variable/column of nVarchar(max) to a encrypted column of nVarchar(20)). So choose a smaller size as possible.)
  2. Encrypt the column with the same encryption type and key as that of the column under consideration.
  3. Now you could use the value from the table to insert, update, compare,..etc. operations. You could also use in functions like coalesce , isnull, etc..

Example:

Create table with encrypted column 

CREATE TABLE [dbo].[Encrypted_nVarchar_256](
    [SNo] [smallint] IDENTITY(-32768,1) NOT NULL,
    [EncryptValue] [nvarchar](256) COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_key], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
        [Value_Description] [nvarchar](256) NULL,
     CONSTRAINT [Pk_Enc_nVar_256] PRIMARY KEY CLUSTERED 
    (
        [SNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

Insert text into encrypted column the value that you would required to be used inside procedures.

DECLARE @EncryptValue  nvarchar(256) ='';
INSERT INTO Encrypted_nVarchar_256(EncryptValue,[Value_Description]) VALUES(@EncryptValue,'Empty string')
DECLARE @EncryptValue1  nvarchar(256) ='Some string you would like to hard code';
INSERT INTO Encrypted_nVarchar_256(EncryptValue,[Value_Description]) VALUES(@EncryptValue1,'Your description')
--more rows as you need
GO

Now you could consume in stored procedure

 CREATE PROCEDURE InsertProc  
    @Var1 nVarchar(20)  
    As  
    BEGIN    
        --Passing hard coded ''(Empty string) as insert value
        INSERT INTO testTable(EncryptedCol1,EncryptedCol2,NonEncryptedCol)
        SELECT TOP 1 EncryptValue,@Var1,"some string" FROM Encrypted_nVarchar_256 where Sno=-32768
        --Comparing some hard coded string
        SELECT * from  testTable
        where EncryptedCol1=(SELECT TOP 1 EncryptValue FROM Encrypted_nVarchar_256 
 where Sno=-32768)
         --Using in functions
         SELECT COALESCE(EncryptedCol1,(SELECT TOP 1 EncryptValue FROM Encrypted_nVarchar_256  where Sno=-32768)) as [new col name] from  testable

    END

Voila!!! now you could do anything with just a little twist.

Samuel A C
  • 406
  • 3
  • 16