Microsoft is working on a new Windows Azure service called “Trust Services”. Trust Services takes a certificate you upload and uses it to encrypt and decrypt sensitive data in the cloud. Of course, like any security service, there’s a bit more to it than that. I’ll give you a quick overview of how you can use this product to protect data you send to SQL Azure.
The primary issue with storing data in the cloud is that you are in an environment that isn’t under your control – in fact, that’s the benefit of being in a distributed computing environment in the first place. On premises you’re able to encrypt data you don’t want anyone else to see, using various methods such as passwords (not very strong) or certificates (stronger). When you use a certificate, it’s vital that you create (or procure) and protect it yourself.
When you store data remotely, regardless of IaaS, PaaS or SaaS, you don’t own the machines where the data lives. That means if you use a certificate from the cloud vendor to encrypt the data, you have to trust that the data won’t be accessed by the vendor. In some cases having a signed agreement with the vendor that they won’t access your data is sufficient, in other cases that doesn’t meet the requirements your system has for security.
With the new Trust Services service, the basic process is that you use a Portal to create a Trust Server using policies and other controls. You place a X.509 Certificate you create or procure in that server. Using the Software development Kit (SDK), the developer has access to an Application Layer Encryption Framework to set fields of data they want to encrypt. From there, the data can be stored in SQL Azure as a standard field – only it is encrypted before it ever arrives. The portion of the client software that decrypts the data uses the same service, so the authenticated user sees the data if they are allowed to do so. The data remains encrypted “at rest”.
You can learn more about this product and check it out in the SQL Azure labs at Microsoft Codename "Trust Services"
What's the guidance for cases where you need to query the data, but also need it encrypted-at-rest? Something that would look like "select FirstName from DemographicTable where SSN = '802-23-3833'" if SSN had been plaintext. Are we heading toward SQL Azure support for "select FirstName from DemographicTable where Decrypt(SSN) = '802-23-3833'" ?
Great question - this is mentioned only briefly in the video, but they go into more depth in the SDK documentation if you want to download that. (It's free). In general, this works like it does in most encrypted data cases - the query field is normally another field within the data set, meaning that you don't query the social directly. You wouldn't want to do that anyway, since the data would be sent across the wire that way, and even though you use an encrypted channel, you don't want to take that risk. This is why you'll see systems ask you for your "final four" numbers in your social, and combine that with your name to search.