How best to encrypt/decrypt SQL Server data to prevent even developers from seeing it?

Question

Here's an interesting problem, and I'm looking for a pattern that will keep it all workable.

I am building a smart-client app for a school system. It will contain information about students including their report cards, sick days, and so forth. it will generate student-level reports, including their report cards, each rich with very personal commentary by their teachers. The app will retrieve data from the remote server via web services.

So the data is quite confidential. I will encrypt it in the database, and decrypt it on retrieval - no problem there.

The problem is that my team and I should really never see the production plaintext data. An interesting problem emerges then for investigating production bugs! We'll want to open the same record as the user to see what they're seeing. But if we DO we're violating confidentiality.

My thought is this, and it's not perfect.

• First, encrypt the data before storing in the database, decrypt it at the UI. Nothing new there.
• Second, put a mechanism in the UI to obfuscate privileged data. (i.e., names and teacher narrative are privileged; grade level is not.) I won't encrypt it, but obfuscate it - even a simple key-shift would suffice. The reason being, these reports are full of text. If I encrypt a paragraph and show the result in a report, it will be a solid wall of uppercase characters, looking nothing like the original text. If I do a key-shift on the alphabetic characters, it will be unreadable, but will still look like paragraphs, sentences, bulleted lists and the like. It will be easier to see what's going wrong without adding visual complication.
• Third, I put in a configuration setting to perform this UI obfuscation just for members of say, the SysAdmin role, not the Teacher or SchoolAdmin. During development, I set this configuration to False, and we develop against fake plaintext. For production, we set it to True, and from that point forward, we only see obfuscated text.

Finally, for those cases where we absolutely MUST see the plaintext of a student's records, we have an override setting in the UI that countermands the configuration setting, and presents the plaintext. And we manage that at the human level - informing the school administration that on THIS date for THIS reason we will need to see THIS student's record, etc. Sign offs are signed, grumbling consent is given, lawyers are scrambled to their jets, rinse and repeat.

Thoughts? I feel like this must be well-trodden ground. Please help me improve on this plan, if possible.

Answer 1

Actually, you don't need to decrypt the data at the UI. SQL Server has tools for doing real-time encryption for you. If someone needed to see the plaintext, they could be dropped into a role that would given them that permission (and taken out when they were done).

However, if seeing the data breaks confidentiality then clearly you can never see the production data. The only solution is a copy of the data with either munged data or completely random data.

Answer 2

So generally I've approached this as follows:

SQL server has encryption functionality - either transparent encryption (which is not suitable for your case as you will see decrypted data in queries) or key based encryption where user accounts have ACLs on the key. With this method you create an X509 certificate on the SQL server itself, and then generate symmetric keys which have suitable ACLs. Within your stored procedures you can then open the symmetric key to return unencrypted data to your application. Of course you should be connecting to SQL via secure means - you can put an X509 certificate on the SQL server to protection connections.

Of course now you have the problem of key management. You can create a specific windows account for your application to run under, with a random strong password that is discarded as soon as you configure the application pool and then add that NT account into SQL (if you're within a domain environment this is easy to do, workgroups you must mirror the account on the IIS and SQL server). For debugging you'll need another account with access to the keys. The password for this account should be in a "break glass" set up - stored somewhere that is auditable, or half the password is shared between two or more people who must agree, with formal signoff that it's needed (and then it's changed once used)

There's an introduction to SQL encryption here, but it doesn't cover ACLs. MSDN has an entire section on it which also covers authenticators, and the various options available to you.

Or you take the option of having an encryption web service, which you call to encrypt data, this returns a GUID reference to a key and the cipher text. This way the key can be stored in a well protected database, and the decrypt functions can be protected on a per account basis. Again you'd have to have a break glass account. I've done this when customers don't feel confident enough to manage SQL encryption themselves.

Answer 3

One thing you should definitely have is an immutable audit-trail. Whenever anyone accesses any confidential data, an audit record is created. In cases like SysAdmins requesting access to report data that they shouldn't ordinarily be looking at, exceptions are logged.

The client should put a business process in place to regularly review any exceptions in the audit log. This should both act as a deterrent to abusing the system, and allow for investigations after the fact if someone does go rogue.