Data Encryption in Sql Server

JAN

31

Protecting User Data

For the last 12 to 18 months I've been on a mission to ensure any user data i stored in a sql data base is well encrypted not just encrypted but with some thought behind so if someone steals my data on the sql server they will just nothing of any value.

For this i am using rc4 which i found on sqlteam when i googled SQL RC4 , having said that the version i found had a few issues and i've since taken it apart and fixed it so it works to encrypt and decrypt strings with out issue.

For my implementation of this RC4 encryption i have a master key set to 100 to 120 bytes, which is used to encrypt the users field they want to protect , ie username or email address, mind you i didn't stop there, my thoughts were what if the username was very small , ie 4 or characters , my answer to that would be that for instance the username field in my database is 100 characters wide (varchar(100)) i would produce an encrypted string that is 100 characters wide.

Now your wondering how do i go from lets say 5 characters of encrypted data to 100 ? . simple i pad the now encrypted with a character of the callers choice ( lets say 10 possible characters to choose from) and then encrypt this 100 character string with a new key , not just any key but a key derived from the master encryption key. 

The reason i use a derived key is this is not stored anywhere but its produced on the sql server in a stored function that is a wrapper around the RC4 encryption routine , this derived key is formed by doing a process of grabbing every x number of characters IE every 7 counting up from 0 till the end of the master key , then grabbing every y characters ie every 5 counting down from the length of the master key until we can grab no more characters , and repeat these two steps until we have a derived key that matches the length of the master encryption key.

Now their's one final step , its done at the just before the data is encrypted  , the caller specify's the starting position for the master encryption key. How I've implemented it lets say your key is 120 characters long and you specify that the start will be at 45 characters , i build the encryption key using a sql command like this ( @Start is set to 45 for this example)

The function that does this , has the data to encrypt passed in , the length of the encrypted field , the start position and the desired index of the padding key ( IE to a linked table with x number of padding characters in it)

To See this in action here is what the data might look like ( I've cut the field down to fit the image in)

In My next blog i'll talk about how this is done in a SQL Query in PHP