Posted by: Denny Cherry
Data Encryption, Data Loss, Data Security, Data Types, Database, Database Administration, Database Design, Database security, Encryption, Identity theft, Security, SQL, SQL Server, SQL Server stored procedures, Stored Procedures, T/SQL, Tables
I wrote a little while ago about the fact that sensitive data needs to be encrypted within the database for all applications. This is the first technique that is available to you to encrypt data in a database with as little outage as possible.
In this technique we’ll encrypt the data using just a single column. This technique requires butting some additional logic within the application to figure out if the value is encrypted or not, but other than that logic, which you can leave in and strip out later the changes to the application are pretty minimal as the column stays the same, so that means that the stored procedures don’t need to be changed.
The first thing to remember is that the encrypted data will be larger, possibly much larger than the plain text version of the data. Because of this you’ll need to increase the size of the field which you’ll be putting the data into. Now the good news is that if this column isn’t indexed this change should be pretty quick and easy as it should just be a meta change which tells the SQL Server that the column size can be bigger without having to actually change the pages. You can see this by making some changes to the [HumanResources].[Employee] table within the AdventureWorks database. By turning on STATISTICS IO and using the ALTER TABLE statement we see that there is no IO generated when we change the size of the LoginID column from nvarchar(256) to nvarchar(512).
set statistics io on
alter table MyTable
alter column LoginID nvarchar(512)
Once the column is made larger the .NET code needs to be modified to see if the data is compressed for not. Now there is no sure fire way to check to see if a value has been encrypted or not, but a pretty good test is to look at the last two characters of the value. If they are both an equal sign (==) then it is probably safe to assume that the value is encrypted. To don’t want to just attempt to decrypt the data and look for an error message, and if there is an error assume that the encrypted value is in plain text, throwing and catching error messages in .NET is very expensive, especially compared to simply checking to see if the last two characters are an equal sign. This isn’t to say that you shouldn’t have TRY/CATCH logic around the code that decrypts the values as someone could easily enough put two equal signs at the end of their password.
At this point either a .NET app or a T-SQL script can loop through the values in the table which aren’t encrypted and then encrypt them, updating the rows which aren’t already encrypted.
Look for more blog posts in this series on how to encrypt data which already exists within your applications database.