Encryption

If I wanted to learn the law, I would have been a lawyer

So your probably a lot like me, you were a teen who liked to play with computers and you managed to end up in IT.  This is awesome you figured, I play with computers and someone actually pays me for this.

During the 90’s life was good, there wasn’t any regulations to deal with, you followed best practices to the extent that the company you worked for could afford them.  Change control usually consisted of sending out an email saying “Hey, were going to change a bunch of stuff, nothing should break.”.  Today however things are different, very different depending on how large the company is that you work for, and if your company is public or private.

Today I work for a private company, so we are able to run things much like the “good old days”, but most are not so lucky.  Change control processes are cumbersome at best, and the number of legal compliance issues that we have to not only be aware of the existence of, but actually understand is quite daunting.  You’ve got everyone’s favorite SOX which says that lots of stuff needs to be controlled and duties must be separated, but doesn’t give any sort of guidelines as to how to do this, or what duties should be done by who.  If you take credit cards over the web, or process credit cards over the web then you’ve got PCI (which I’m dealing with now on our shopping cart server).  If you work for a company which stored medical records then got help you when it comes to HIPPA.  For those that aren’t aware of HIPPA part of it basically says that every lookup to medical records has to be logged.  Within an application that’s easy.  Windows SQL Server 2008 that’s easy, but what about the legacy SQL 2000 medical application?  It provides no guidance other than to say “do it”.

I remember that when Windows 2003 SP 1 was released (it may have been SP 2) there was a thread on a forum somewhere (probably tek-tips.com) where we were discussing SP1 and HIPPA.  Somewhere in HIPPA is says that you have to keep your systems patched.  Somewhere else it also says that systems which stored medical information on there cannot report data back to a vendor.  Well SP1 introduced code that would allow a sysadmin to have the server report usage and error data back to Microsoft.  So which part of HIPPA should you violate?

On top of all the federal regulations, states are now passing data encryption laws which have to be dealt with.  Here in California we’ve had data encryption regulations in place since 2003 or so.  At several companies that I’ve worked at the IT managers didn’t know anything about the law and what it meant.  The law here in California is so vague that it is almost meaningless.  It says (in laymen terms) that if your data is breached, and the data isn’t encrypted then you have to tell your customers either directly or via the media.  But it doesn’t define encryption, or how strong that encryption has to be.  It at least defines what data items it includes (name, address, username, password, social security number, etc) but if you take the law at face value doing a simple character replacement is sufficient to comply with the law.  While this complies with the letter of the law it obviously doesn’t comply with the spirit of the law, but the letter of the law is what matters in court.

Having to keep track of all these laws which apply to us is mind boggling at best, and impossible at worst.  And reading the laws is amazingly painful.  The California law I sited above, which I’ve read several times, still confuses me to no end; and I’ve reviewed it with the legal team at one company already due to a data breach.  And consider that there are data encryption laws in several states, all of which you have to comply with if you have customers in that state, or if you do business in that state.  I have no idea which states have these laws, or even how many states have these laws.  Even if I did, I’d then need to find the overlaps and the exceptions, then figure out how to build our database to meet these laws.  Beyond that I’d have to anticipate the future laws that could be coming in the other states and account for those potential laws at the same time.

At this point handling the database design is just getting more complex.

Now the new Transparent Data Encryption is great for handling data at rest.  It keeps your backups all encrypted and save.  But what happens when the bad guy breaks in and swipes the data by logging into the database.  Yea the data is encrypted on disk, so technically we are covered, but the data is still out there and usable because the bad guy was able to login as a database user with select access to the tables and dump the data to his system via a SELECT statement.  What has to happen now?

I don’t know about you, but I got into this field so that I wouldn’t have to worry about stuff like this.  I guess those times are over with.

This rant is now complete.  See what happens when I get on a plane at 6:30am and the nice lady starts poring coffee down my throat for the entire flight.

Denny

What’s the difference between encrypted data and hashed data?

The biggest difference between encrypted data and hashed data is that encrypted data can be decrypted later.  Hash algorithms such as MD5 are one way hashing algorithms which means that the value that is returned can’t be decrypted back to the original value.

It is important to know the difference between the two when designing your database encryption schema.  If you don’t need to retrieve the encrypted value then only store the hash.  This way you don’t have the actual data for anyone to steal.

Denny

I need a secure transfer of data between SQL Servers. What are my options?

You’ve got a few options, none of which are all that easy to setup.

1. Export the data, then encrypt the file, then transfer the file, then decrypt the file, then import the file.
2. Setup IPSec between the two SQL Servers and then transfer the data as normal.
3. Setup a Secure FTP Server at the second SQL Server. Export the data, then use a SFTP server to transfer the file to the remove server, then import the file.

If you take option #1 you can use use openssl or PGP to encrypt the files.

If you take option #2 you can find some info about IPSec here.

If you take option #3 here is a site about setting up an SFTP server on Windows. This site also includes information on finding an SFTP client.

If I was setting this up I would probably go for Option #2, and if that didn’t meet my requirements Option #1.

Denny

My Experience with putting a CRL Procedure into SQL

A little while ago I came upon a situation where I needed to put a CRL procedure into SQL Server 2005.  Now I’ve been using SQL Server 2005 since the day it went RTM, and this is the first occasion that I’ve needed to put a CLR procedure into SQL.  Could I have done what I needed to without a CRL procedure?  Yeah probably, but the .NET code was written, and had been working.

My code is written in C# as that’s what the .NET guys here use.  There is one method within the C# class called HashPassword.  It takes a text string and makes a SHA1 Hash of it which we then store.  We had to make a couple of changes to the C# code to make it work within SQL Server.

The original code looked like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
 

namespace Rapid.Database.Security{
public class User
     {
     public static void HashPassword(String password, out String hash)
          {
          SHA1 sha1 = new SHA1CryptoServiceProvider();hash = 
          BitConverter.ToString sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace(“-”, “”);
          }
     }
}

The changed C# code looks like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using Microsoft.SqlServer.Server;

namespace Rapid.Database.Security

{
public class User
     {

     [SqlProcedure(Name="HashPassword")]
     public static void HashPassword(String password, out String hash)
          {
          SHA1 sha1 = new SHA1CryptoServiceProvider();hash = 
          BitConverter.ToString sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace(“-”, “”);
          }
     }
}

Once these changes are made and the DLL recompiled we can attach the DLL to the SQL Server database as an assembly.  This is done with the CREATE ASSEMBLY command.  I have to use the EXTERNAL_ACCESS flag instead of the SAFE flag because my dll requires the use of the System.IO assembly which can’t be run as SAFE under SQL Server 2005.

CREATE ASSEMBLY [Rapid.Database.Security]AUTHORIZATION [dbo]
FROM ‘D:\Rapid.Database.Security.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

We can now create our procedure which needs to have one input and one output parameter to match the number of parameters within the C# procedure.  This is done with the CREATE PROCEDURE command just like creating any normal procedure.

CREATE PROCEDURE [dbo].[HashPassword]
@Password [nvarchar](50),
@PasswordHash [nvarchar](50) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Rapid.Database.Security]. Rapid.Database.Security.User].[HashPassword]
GO

The three part name of the C# method are:

1. Assembly name which SQL Server knows about (basically the name of the DLL without the file extension).
2. The full name to the class, in our case the namespace then the class.  Our namespace is Rapid.Database.Security with the name of the class after that.
3. The name of the method within the C# class.

Don’t forget to enable CLR within SQL Server using the sp_configure before trying to use the CLR procedure.

 At this point I am able to run the procedure and pass in text and get back the hash.

declare @PasswordHash varchar(50)
exec HashPassword @password=‘test’, @passwordHash=@PasswordHash OUTPUT
SELECT @PasswordHash

The value of this is: A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

I hope that you find this useful. It took me and a .NET developer a couple of hours to get all this working correctly and loaded into the SQL Server correctly. I know that I’ll be referring back to this if I ever need to attach a CLR method into SQL again.

Do keep in mind that when we were doing this, we created this as a separate DLL for the SQL Server as to get the original DLL into the SQL Server, SQL wanted us to load up a bunch of other assemblies that some of the other methods required.

Denny

EMC World 2008 Day 3

Wednesday at EMC World was a session packed day to be sure.

I started my morning with CLARiiON Rebuild Settings and Data which was an in depth look at how exactly the CLARiiON systems handle rebuilds, and how long various rebuilds take.  In addition we went into detail as to how the CLARiiON will not see that a disk is going to fail, and instead of waiting for it to fail and then having to rebuild it will actually copy the data from the disk ahead of time to a hot-spare then mark the disk as failed so that it can be replaced before the disk actually fails there by providing you with no point in time where your data is unprotected.

 Another session went through the changes to the EMC PowerPath product and all the new features they are building into PowerPath such as the encryption of data when it leaves the server on its way to the disk.

Another session went through some tuning tips an tricks for getting the best performance from the EMC CLARiiON product.  These include the strip size, cache settings at both the LUN and SP level. 

The night ended with the Billy Crystal performance.  I figured that the show would be funny.  I was wrong, it was hilarious.  Like everyone else I’ve seen Billy Crystal on TV and in Movies and thought he was funny, but in person he was probably the best comedy performance that I had ever seen.

Denny

SQL 2008 one click database encryption gives a false sense of security

While I think that the one click database encryption that Microsoft has built into SQL Server 2008 is a good idea, but I’m not sure how useful it actually is.  It’s touted as giving you data encryption of the entire database without any code change.

What this actually means is that if someone stops the SQL Server and copies the data files to another server and attaches the files to that SQL Server the files won’t be of any use.  This however isn’t the common way that data is stolen from a SQL Server.  A much more common method is someone logs into the database using a perfectly legit account and exports the table or tables which contain the useful information to Excel, Access, csv, etc.  This method of database encryption does nothing to protect against this as if you query the table directly you are displayed the data in clear text.

All this database encryption does (as it appears to me) is that it protects the data at rest (ie. when the SQL Server service is stopped) and does nothing else to protect the data.  If all you are looking for is a compliance check off saying that you data is encrypted for SOX, HIPPA, the California Online Privacy Protection Act of 2003, etc then this will probably do the trick.  If you want actual data encryption where the data is not viewable when you query the table unless you know the key to unlock the data this isn’t it.

If you do want that quick and easy encryption for a compliance check off be sure to check your data storage requirements and IO requirements.  Encrypted data is typically larger than unencrypted data and you may need to increase your allocated storage or IO requirements in order to have the encryption completed.  Also remember that the higher level of encryption you use the more CPU time is required to decrypt the data.

Feel free to share your opinions below.  I’m interested to here what others have to say on the topic.

Denny