0

Due to the regulation changes in the UK we are changing our Database to use Always Encrypted on all of our person identifiable and potentially sensitive data.

When trying to run a stored procedure after all the Encryption had taken place I received the following error.

Encryption scheme mismatch for columns/variables '@FilePath'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'xxxxx') and the expression near line '19' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).

So, when looking into this and commenting out the offending line the SP creates and run's "fine".

Fine in the way that it now has incorrect values.

The offending SQL is : 

select @FilePath = ParameterValue from Parameter where ParameterCode = 'EXPORT_PATH'

So, I pulled this line out into its own SQL and not in an SP and got it to work like this. 

DECLARE @FilePath VARCHAR(8000) = '',
@ParameterCode varchar(50) = 'EXPORT_PATH'

SELECT @FilePath  = ParameterValue from Parameter where ParameterCode = @ParameterCode
SELECT @FilePath

Which works perfectly, however, when I wrap this into an SP like 

CREATE PROCEDURE SIMONTEST
AS
BEGIN
DECLARE @FilePath VARCHAR(8000) = '',
@ParameterCode varchar(50) = 'EXPORT_PATH'

SELECT @FilePath  = ParameterValue from Parameter where ParameterCode = @ParameterCode
SELECT @FilePath
END 
GO

I get the error

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'PROCEDURE'. Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0]

Statement(s) could not be prepared.

An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@p411c14c506c74dc690baf9ef724f7409' in statement or procedure 'CREATE PROCEDURE SIMONTEST AS BEGIN DECLARE @FilePath AS VARCHAR (8000) = @p411c14c506c74dc690baf9ef724f7409, @ParameterCode AS VARCHAR (50) = @p9f3729b1d64742cf858ff9edb30a5d71; SELECT @FilePath = ParameterValue FROM Parameter WHERE ParameterCode = @ParameterCode; SELECT @FilePath; END

' is missing in resultset returned by sp_describe_parameter_encryption.

I would be grateful if someone could help me understand the issue and help me get through it.

Simon Price
  • 3,011
  • 3
  • 34
  • 98

1 Answers1

0

I cannot tell exactly why or how it worked but here it goes what did work for me

First I disabled encryption (aka set PLAINTEXT as encryption method in the SSMS Always Encrypted columns encryption dialog window) for the columns involved in the WHERE clauses of the sproc

Second I disabled the Parametrized Query option from the SSMS just for that particular ALTER or CREATE I needed to do. You find it under Query > Query options > Execution > Advanced

Third I modified / created my sproc paying attention to rightly parametrize the variables for the WHERE clauses not to incur in comparison operators incompatiblenesses

Fourth after having executed the creation / alter Query without that error you reported I re-enabled back the columns encryptions the way they were.

Sprocs finally executed fine and also (It needs further testing) it seemed like subsequent alters didn’t need the above steps. But as I said I’m not sure about this. Tomorrow I’ll give it another try.

Shockwaver
  • 366
  • 3
  • 17
  • I did try this, or at least something similar to this but it didnt yield anything positive for me. My problem is I have a large number of fields across a number of tables, and a bucket load of sprocs and reports where encrypted fields will be used – Simon Price May 21 '18 at 19:42
  • You mean it didn’t work or it is not viable in your scenario because of too many changes are needed? Because if it’s the former, and you try with your example sproc, you’ll see it works... just tested with SSMS 17.6. If it’s the latter, well I can only tell that it’s your call, but do consider you may not avoid needing to touch the sprocs because right parametrization could be mandatory... Temporarily plaintexting involved columns may result to be the lesser pain... – Shockwaver May 21 '18 at 20:23
  • bit of both in reality. We are exploring other options now with this as a second favoured option but getting Enterprise licensing for SQL server to do TDE along with DDM. If we come back to this option I will try these methods out but its circa one week of effort with all the stored procs, reports and schema changes that need to take place, along with testing after that. – Simon Price May 22 '18 at 07:40