Using JSON functions with Always Encrypted data in SSMS
-
I have an Azure SQL database that uses the Always Encrypted functionality. In this database I have a column called entryObject. It is an nvarchar field containing JSON data. It is encrypted using a Randomized encryption type. I am currently writing SQL that should us the JSON_MODIFY function to update this data as well as unencrypted data in another table. I would like to have all of this functionality wrapped in a transaction and in a stored procedure. This way, my C# code can call this one stored proc and, if successful, I'll know that all data was updated successfully. However, any time I try to use the JSON_MODIFY function or even JSON_QUERY with my encrypted entryObject column's data, I get an error stating
Argument data type nvarchar(max) encrypted with (encryption_type = 'RANDOMIZED', ... is invalid for argument 1 of json_query function.
How can I use JSON_MODIFY to change one of the values in this encrypted JSON? Thanks in advance for any assistance you can give.
Denise
-
I have an Azure SQL database that uses the Always Encrypted functionality. In this database I have a column called entryObject. It is an nvarchar field containing JSON data. It is encrypted using a Randomized encryption type. I am currently writing SQL that should us the JSON_MODIFY function to update this data as well as unencrypted data in another table. I would like to have all of this functionality wrapped in a transaction and in a stored procedure. This way, my C# code can call this one stored proc and, if successful, I'll know that all data was updated successfully. However, any time I try to use the JSON_MODIFY function or even JSON_QUERY with my encrypted entryObject column's data, I get an error stating
Argument data type nvarchar(max) encrypted with (encryption_type = 'RANDOMIZED', ... is invalid for argument 1 of json_query function.
How can I use JSON_MODIFY to change one of the values in this encrypted JSON? Thanks in advance for any assistance you can give.
Denise
Always Encrypted (Database Engine) - SQL Server | Microsoft Docs[^]:
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). ... Decryption occurs via the client. This means that some actions that occur only server-side will not work when using Always Encrypted.
SQL Server doesn't know how to decrypt your data. It can't read the value stored in your column, so it can't issue a JSON query against it, let alone modify the value. You'll need to load the data into your client application, make the changes there, and then update the database value. You can do that within a transaction, using
TransactionScopeorBeginTransaction. But you can't do it from a stored procedure.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Always Encrypted (Database Engine) - SQL Server | Microsoft Docs[^]:
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). ... Decryption occurs via the client. This means that some actions that occur only server-side will not work when using Always Encrypted.
SQL Server doesn't know how to decrypt your data. It can't read the value stored in your column, so it can't issue a JSON query against it, let alone modify the value. You'll need to load the data into your client application, make the changes there, and then update the database value. You can do that within a transaction, using
TransactionScopeorBeginTransaction. But you can't do it from a stored procedure.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Hi there, Thanks for the reply. I was hoping to keep it all in SQL as that's where I was more comfortable with the ability to rollback a transaction in case of failure. I will definitely look into transaction processing in C# and thank you for pointing me in the right direction. Denise
Denise
