How WITH ENCRYPTION Works

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.

Related Posts

SQL Server and Recent Security Patches

Allan Hirt takes us through recent security updates and how they pertain to SQL Server: After Spectre and Meltdown a few months back (which I cover in this blog post from January 4), another round of processor issues has hit the chipmaker. This one is for MDS (also known as a ZombieLoad) This one comprises […]

Read More

NT AUTHORITY\ANONYMOUS Error Editing Procedures

Kenneth Fisher takes us through a security issue: If you have to deal with linked servers then you probably have or will run into the following error: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ But I’m not trying to use the linked server. I’m trying to create/alter a stored procedure. Kenneth explains why you might […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031