June 6, 2018 at 1:59 pm
We have an application that needs to occasionally fix bad data on Always Encrypted columns. Because of a variety of reasons, they can't totally clean data until it arrives in their system. The team is working on an application-level solution; in the meantime, the DBAs are fixing the data in SSMS. Parameterized queries are working fine for simple inserts and updates.
Occasionally the application team has a more complex request, such as replacing XXXXX with YYYYY in a single row's encrypted VARCHAR(MAX) field.
The requirement to assign the value in the DECLARE statement is the source of the issue. Unless I am missing something obvious (entirely possible) I can assign static values to a variable in a DECLARE statement, but can't assign the results of a SELECT statement. The plan had been to pull the single value out in a SELECT statement, run a replace, and then insert the new value via a parameterized query.
My guess is that we are out of luck, and will first have to extract the unencrypted value, and then hardcode the new value in a DECLARE statement - but I wanted to put the question out there in case someone has a better interim solution for us.
Thanks!
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy