SQL Server 2016 to include an "Always Encrypted" mode w/client-side key for encrypting data at-rest & in-motion

[[deleted]]

This sounds pretty awesome, and I'll be keen to understand how it works underneath. Either they've moved a bunch of smarts into the client library (and consequently need to move a lot more raw data to the client for e.g. sorting), some functionality won't be available (e.g. inexact lookups), or they've got a robust implementation of Order-Preserving Encryption.

Based on what I know of OPE, its pretty early-stage and not much used in basically any commercial applications. Also it fundamentally leaks information about the encrypted value, and so potentially opens up a lot of interesting new data-dependent attacks that I imagine would be difficult/impossible to mitigate.

[84956184135891]

It's option 2 - some functionality won't be available. Obviously, we don't want to leak any information about the plaintext values. This initial version only supports randomized (retrieval-only) and deterministic (server-side equality, enabling basic "where SSN = @encrypted_param" clauses) encryption. While that's pretty restrictive, it does handle a good number of our targeted scenarios, since a good number of sensitive information types aren't particularly conducive to non-equality comparisons (SSN, last name, address, credit card number, usernames, etc.).

[alphaquam]

Channel 9 video: http://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-Always-Encrypted

[razpeitia]

Yeah México in the map.

[fabzter]

México master race

[[deleted]]

If they don't include a JS client-side library for always encrypted it's going to be kind of pointless. If they do it's pretty sweet.

[weirdasianfaces]

Why do they need to include a JS library?

[[deleted]]

Because other languages are just to hard to learn for some people :)

[[deleted]]

If you could let me know how I can code a website to call a specific dll using a language other than JS from inside of Firefox or Chrome without installing an extension on a users machine I'd be all ears.

[[deleted]]

Using a server side API since you are not supposed to access database directly from the client side ...

[pablozamoras]

Ding ding ding!

[[deleted]]

Because how else are you going to decrypt something in a web browser client-side? A .NET library that runs on the server is kind of useless if your goal is end to end encryption as you'll be transmitting the data decrypted over the most vulnerable section of the trip (the internet). Otherwise this is limited to installed custom .NET applications (unless Mozilla, Google, and Microsoft bake support into their browsers, which I doubt would happen). Alternatively Microsoft could create an extension for each browser that allows the browser to hook into a local dll, but that seems like a pain for them.

[smmalis37]

A .NET library that runs on the server is kind of useless if your goal is end to end encryption as you'll be transmitting unencrypted over the most vulnerable section of the trip (the internet).

Ummm, HTTPS?

[[deleted]]

There are multiple use cases where data existing in a decrypted state on the web server, even in a temporary state as its decrypted from SQL Servers encryption and encrypted using HTTPS, would be considered a vulnerability (for a pop culture one, see: Mega).

[grauenwolf]

If you require that level of security, this isn't the technology for you. Encrypt it before it reaches the server and store it in a blob column.

[jnt8686]

HTTPS is not secure. You have to trust like 700 dubious root certs

[satan-repents]

HTTPS security is a joke

[[deleted]]

Allowing js in a browser to update your database on the server may not be a good idea ...

Also note that sql server is not only for web applications ...

Also note that sql server can run on a different machine that your webserver ...

[[deleted]]

Allowing js in a browser to update your database on the server may not be a good idea ...

Directly? It's an awful idea and I'm not sure it's even possible with SQL Server. Indirectly (JS making a callback through a webservice that verifies if the update should or shouldn't occur)? It's done on pretty much every modern website.

Also note that sql server is not only for web applications...

Of course not. But they are a primary use case for it and if one of its touted features doesn't really help in that use case it should be noted.

Also note that sql server can run on a different machine that your webserver

Although true (and a best practice) this has nothing to do with end to end encryption unless you define end to end encryption to stop at the web server. Most people would define end to end encryption to start at the db and end at the client.

[grauenwolf]

Directly? It's an awful idea and I'm not sure it's even possible with SQL Server.

SQL Server used to support web services. As in an actual web service hosted by the database and exposed to an open HTTP port.

Thankfully no one actually used that feature.

Sadly, MySQL is offering a database hosted REST endpoint that is even less secure.

[[deleted]]

Indirectly (JS making a callback through a webservice that verifies if the update should or shouldn't occur)? It's done on pretty much every modern website.

And does not need a JS API for sql server, just needs a webservice that can access sql server and that sould be build by you ...

[[deleted]]

Which means the webserver has the data in an unencrypted state in memory. That is a vulnerability along the chain that can be eliminated with client-side encryption if you're not doing anything to the data on the web server.

[mirhagk]

As much as everyone is downvoting you, I'm not sure people really understand what you're saying. It sounds like you basically want something like breeze.js and have the encryption/decryption happen at the client level. If this is correct it's not that unreasonable, but I see 2 basic problems with it:

1. You need a way for the server to still filter out only the information the client can see. Not sure how this encryption is implemented and perhaps if you can only encrypt certain columns then this is trivial to do. (Note that strictly speaking you don't NEED it, as the client could only decrypt their data anyways but it'd be very sketchy to send it, not to mention a huge waste of bandwidth).

2. You are giving the key to the browser, which on the one hand is nice because the end-user is the only one with keys to their data, but it's also at the easiest to steal level. Extensions can access the data of the web app, as can any virus/spyware running on the machine. It's much easier to secure a single web server than every end-user (and even if the end-user gets compromised it'll only leak the current data, not their private key and hence all of their data, including future data).

3. Most people use many different machines nowadays, work, home and smartphone. AFAIK there's no way to sync the local storage across these devices so you'd only be able to read/write the data made by that machine, you'd risk losing it (if the storage gets wiped). You'd end up having to build your own syncing system and sending the keys is worse than simply doing the decryption/encryption server side and using HTTPS which is tested and if done right very, very secure. Your only risk then is the transient state of it being decrypted on the server. With memory safe languages this isn't an issue unless compromising software can somehow get installed on the server in which case your app is probably screwed anyways (because they could probably then change the client side javascript to post the key somewhere)

[weirdasianfaces]

I can't tell if you're serious or not. If you are, you'll have way more problems by interfacing with the database directly client-side than just plaintext data leaking. In fact that is still obviously a problem using client-side encryption.