I'm implementing Always Encrypted feature for an ASP.NET Application using SQL Server 2016. I have applied deterministic encryption on first five tables and created few stored procedures that references these tables for select/update logic using Management Studio 17.
Later, I created another table (with a stored procedure to update data) and applied the same encryption. However, the application had thrown an exception during the execution of the relevant web page. After further analysis, read about sp_refresh_parameter_encryption statement and used that for the procedure that updates the last table. Issue is fixed.
It is still unclear why application didn't break while working on the web pages that is referencing the first five tables and how it has failed on the web page that uses the last table.
In another scenario, when I encrypt a table using PowerShell Scripts, system throws same error and requires to run the sp refresh command.
However, it didn't give any errors for the first tables and its related stored procedures that were created using Management Studio.
How to find whether we have to run this command for all stored procedures that inserts/updates the encrypted tables? It's really important to keep track of this while moving forward to Production deployment.
Is it possible to run this command for all stored procedures in the entire database?
Could anyone explain this please?