4

I have password stored in a SQL Server table that is "always encrypted" (deterministic). I have a SSIS package that has a package level parameter called "parmPassword". I am trying to use that encrypted column value to set the default SSIS parameter by creating an execution in the SSISDB with the parameter value.

I have the "column encryption setting = enabled" in the connection string. Parameterization is set for the query.

This code runs fine (in SSMS):

DECLARE @var3 nvarchar(100) = (Select [SSISPackageParameterValue]
  FROM [mydb].[environment].[myalwaysencyptedtable]
  Where SSISPackageParameter = 'parmPassword'
  Collate Latin1_General_BIN2)

  select @var3

It returns my password.

mypassw0rd

But when I add the EXEC, it doesn't work anymore, maybe because the variable @var3 is being passed around too many times:

DECLARE @var3 nvarchar(100) = (Select [SSISPackageParameterValue]
  FROM [mydb].[environment].[myalwaysencyptedtable]
  Where SSISPackageParameter = 'parmPassword'
  Collate Latin1_General_BIN2)

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=30,
      @parameter_name=N'parmPassword',
      @parameter_value=@var3

I get this error when I run the above code in SSMS. (The above code also works fine when run against an non-encrypted table.) The error hits at the line with the EXEC:

Encryption scheme mismatch for columns/variables '@var3'. 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 = 'mydb') and the expression near line '0' expects it to be (encryption_type = 'PLAINTEXT').

Microsoft says this error can be caused if you don't parameterize the variable:

https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/

They say you must declare the variable all in one line for this to occur. I think this might work if I could define @parameter_value inline, instead of using @var3, but I don't think that is possible.

Anyone have any ideas on how to accomplish this?

MikB
  • 41
  • 2
  • Is the failing code still executed in SSMS or somewhere else? – Piotr Palka May 08 '19 at 22:20
  • Yes, still using SSMS. – MikB May 08 '19 at 22:45
  • I would say it is similar to https://stackoverflow.com/questions/53102756/parameterizationalways-encrypted-inside-stored-proc . To run this script SQL Server needs to "see" the un-encrypted value, I would recommend using different encryption in this scenario. Check Damien_The_Unbeliever comment. – Piotr Palka May 08 '19 at 22:46

0 Answers0