Paul White digs into the WITH ENCRYPTION clause:
The core RC4 algorithm is well-known, and relatively simple. It would be better implemented in a .Net language for efficiency and performance reasons, but there is a T-SQL implementation below.
These two T-SQL functions implement the RC4 key-scheduling algorithm and pseudorandom number generator, and were originally written by SQL Server MVP Peter Larsson. I have a made some minor modifications to improve performance a little, and allow LOB-length binaries to be encoded and decoded. This part of the process could be replaced by any standard RC4 implementation.
Using WITH ENCRYPTION is a gentleman’s agreement that you won’t look at the underlying code. In practice, it’s trivial to get around, and Paul shows exactly why.