How can you not have permission to create a temp table?

Why are you currently doing weekly fulls and daily diffs?

Short answer: Yes.

The stored procedure does the insert, taking parameters, meaning that you can change the data type to varbinary, and have the procedure directly insert the encrypted value. A trigger, since...

Table definitions please. There's no way to debug a data type conversion error without knowing what the data types are.

Either TempDB is tiny and can't grow, or you have some *really* long running transactions that's preventing the version store from being cleaned up.
Check which please.

There's no point in setting the isolation level. You have no select queries, so there's nothing for it to affect. Data modifications always take exclusive locks.

You don't need...