How to fix?
Many people here suggest that there is gross negligence by the company losing the data, as opposed to the consultants who failed to encrypt or protect it.
From a technical point of view what exactly is the solution, then?
1. Have policy and equipment requiring consultants to use a remote desktop so data always remains on site.
This has it's own technical problems, requires high connectivity, can be expensive, limits the software/os available to the consultants, and is potentially vulnerable to exploitation in itself.
2. Require consultants to always store data on remotely mounted drive located at company via VPN.
Difficult to enforce, and requires high connectivity.
3. Require consultants to keep data encrypted.
They should already have been doing this, it is difficult to enforce.
4. Prevent them from having access to all the data (select *) so they can't loose it
I've heard people say this, but what exactly are you talking about? The consultants may if fact need the data. SQL is by nature an adhoc mechanism, how would one impose restrictions while not simultaneously hampering the ability to do one's job?
The company could have DBA to create and grant restrictive views to the consultants. However if every query needed approval, efficiency would drop like a rock. And if the DBA knew which queries to grant, then they probably wouldn't need the consultants in the first place. So this still wouldn't necessarily fill the security hole.
I'm really interested in knowing how you guys would go about solving this? Clearly there are things that the consultants can do, but what about the company who's data is at risk?