SQL Server Roll-Your-Own Cryptography

David Fowler has an interesting article on a simple cryptographic algorithm in SQL Server:

As this post was prompted by my post on bitwise logic, we’re going to base our algorithm around the XOR cypher.  Basically this cypher works by taking a key which for simplicity sake will be a single byte and XOR-ing that against the message (or plain text) that we want to encrypt.

Let’s look at an example of how this is going to work.

Let’s say that we want to encrypt the plain text ‘SQLUndercover’.  How are we going to do this?  Firstly we need to remember that all text characters are represented by a single byte as an ASCII code.  ‘SQLUndercover’ is represented by the following set of ASCII codes, 83 81 76 85 110 100 101 114 99 111 118 101 114.

I definitely recommend reading this article for two reasons:  first, because it’s interesting; second, because it shows how easy it is to break amateur crypto.  If you feel the need to roll your own cryptographic algorithm, follow my Official Crypto Flow Chart (patent pending):  Do you have a PhD in mathematics with a specialty in cryptanalysis?  If not, don’t do roll your own crypto; if so, maybe do it but even then probably not.

Related Posts

When MS_SSISServerCleanupJobLogin Is Orphaned

Sreekanth Bandarla noticed a problem in cleaning up SSIS metadata: Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), […]

Read More

The Key Hierarchy And SQL Server Encryption

David Fowler walks us through the various keys used in encrypting data in SQL Server: I’m sure that we all know that SQL Server includes all sorts of interesting functionality to allow us to encrypt our data and like with all encryption techniques, that data is encrypted using keys. In SQL Server we’ve got a […]

Read More


July 2017
« Jun Aug »