SQL Server 2016 Always Encrypted feature

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 17-Nov-2015 10:56:28   

Have you guys looked into this at all?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Nov-2015 17:25:16   

not in depth, as it's not out yet simple_smile (to my knowledge). I did look at it some time ago, briefly, and what I remember is that this feature is fully transparent to the application, but I could be mistaken. But as it's not released yet, we don't have support for the feature yet.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 17-Nov-2015 18:00:22   

I just saw this on an newsletter email and had a quick look - being lazy really but I believe you know a little about databases stuck_out_tongue_winking_eye so I thought I'd pick your brains.

If it really is as easy as picking columns/tables to auto-encrypt, would you add support for it?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Nov-2015 21:58:38   

simmotech wrote:

I just saw this on an newsletter email and had a quick look - being lazy really but I believe you know a little about databases stuck_out_tongue_winking_eye so I thought I'd pick your brains.

If it really is as easy as picking columns/tables to auto-encrypt, would you add support for it?

Yes, SQL Server is the most used DB among .NET developers targeting an RDBMS so if it's possible to support it, we will (when 2016 is released of course wink )

Frans Bouma | Lead developer LLBLGen Pro
joef
User
Posts: 8
Joined: 18-Mar-2016
# Posted on: 18-Mar-2016 13:20:48   

We're also really keen on using the Always Encrypted feature of SQL 2016.

Does anyone know if LLBLGP generated data access classes will continue to "work" in a scenario where you DO NOT have access to the encryption keys ?

Example scenario. Say we had a FirstName field that's defined as a NVARCHAR(32) and we set it up to be encrypted.

On the client, if you have a valid certificate and you put "Column Encryption Setting=Enabled" in the connection string then you're going to get back just what you're expecting (a string of up to 32 characters).

On a client which DOESN'T HAVE the certificate (or doesn't set the property in the connection string) you're going to get back the ENCRYPTED value (which, in my example, was 132 characters of alphanum characters "0x014E....").

It's worse again if the encrypted column is a DATE or an INTEGER. Again, in the case where you don't have a cert, the value that gets passed back to the calling code will be a 132 character string. This is completely different from what the generated code will be expecting back, even thought the definition of the field in the source table hasn't changed (and will still appear as DATE or INTEGER in an sp_helptext).

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Mar-2016 15:50:23   

joef wrote:

We're also really keen on using the Always Encrypted feature of SQL 2016.

Does anyone know if LLBLGP generated data access classes will continue to "work" in a scenario where you DO NOT have access to the encryption keys ?

Example scenario. Say we had a FirstName field that's defined as a NVARCHAR(32) and we set it up to be encrypted.

On the client, if you have a valid certificate and you put "Column Encryption Setting=Enabled" in the connection string then you're going to get back just what you're expecting (a string of up to 32 characters).

On a client which DOESN'T HAVE the certificate (or doesn't set the property in the connection string) you're going to get back the ENCRYPTED value (which, in my example, was 132 characters of alphanum characters "0x014E....").

It's worse again if the encrypted column is a DATE or an INTEGER. Again, in the case where you don't have a cert, the value that gets passed back to the calling code will be a 132 character string. This is completely different from what the generated code will be expecting back, even thought the definition of the field in the source table hasn't changed (and will still appear as DATE or INTEGER in an sp_helptext).

If the client doesn't have the ability to decrypt the values, we can't either, so the values returned are the encrypted values. I don't know how this would work otherwise? If you want to use this feature, you have to be able to decrypt the values in the client, IMHO, otherwise you'll get back values which you can't work with anyway (everything is encrypted).

Or am I missing something?

Frans Bouma | Lead developer LLBLGen Pro
joef
User
Posts: 8
Joined: 18-Mar-2016
# Posted on: 18-Mar-2016 16:52:36   

Otis wrote:

If the client doesn't have the ability to decrypt the values, we can't either, so the values returned are the encrypted values. I don't know how this would work otherwise? If you want to use this feature, you have to be able to decrypt the values in the client, IMHO, otherwise you'll get back values which you can't work with anyway (everything is encrypted).

Or am I missing something?

You're perfectly correct, we'd be getting back information that's encrypted. This wouldn't be much of an issue as most of the fields we'd be encrypting would be read-only from the application's point of view.

In our case we need to support an application for a remote client who cannot allow sensitive data to be seen offsite.

We'd want our LLBLGP DAL to be able to support, at some level, the reading of a value of EITHER the actual datatype OR the encrypted value.

Perhaps this could be achieved by either not throwing retrieval errors for fields with encryption or by throwing errors that can handle through application code ?

Could this be achieved by changing a combination of settings from the link below ?

https://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_validation.htm

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 21-Mar-2016 05:57:03   

Actually you have to indicate that a field is encrypted, or not. You can use settings to flag some special metadata into your model, then at generation, you have to (somehow) put a special property on entity/field to know whether the client has to encrypt/decrypt the information.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Mar-2016 15:28:34   

yeah, I think it's a bit difficult, because what to do with the encrypted value?

You could look into Authorization too: http://www.llblgen.com/documentation/4.2/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/gencode_authorization.htm

or in a type converter, which does the encryption/decryption for you (so convert an encrypted value for you or not), but with type converters it's a bit of a problem that you can't reach them on-the-fly, so it's not possible to make person A see the decrypted data and person B see the encrypted data.

Frans Bouma | Lead developer LLBLGen Pro
joef
User
Posts: 8
Joined: 18-Mar-2016
# Posted on: 15-Apr-2016 11:00:15   

We wouldn't want to implement our own authorisation - it would defeat the purpose for our use case.

Here's my question in a nutshell.

Like many other financial software companies, I need to support our application without being able to see sensitive customer information. I do not have the encryption key on my PC, therefore I will always be looking at ENCRYPTED data.

I have a database table that contains AlwaysEncrypted columns - eg

CREATE TABLE dbo.Demo_Always_Encrypted ( ID INT IDENTITY(1,1) PRIMARY KEY,

LastName NVARCHAR(45), FirstName NVARCHAR(45), _ BirthDate DATE ENCRYPTED WITH ( ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK ), SSN CHAR(10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK ) _);

I point LLBLGen Pro against the database, and try to generate a DAL.

Will LLBL be able to handle this - that is, will it return the encrypted data without complaining ?

The actual data being returned will look like this.

ID LastName FirstName BirthDate SSN


1 Larsen Gregory 0x011065959924B9E9DE4AA77626F5CCF08... 0x0130F5B22FAD807B90653ED072B14...

Again, I'm not expecting the data to be unencrypted by LLBL (it can't be, I don't have the key). I just need LLBL to pass the encrypted data on to my application.

Many Thanks !

(Sample code taken from http://www.databasejournal.com/features/mssql/exploration-of-sql-server-2016-always-encrypted-part-1.html)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Apr-2016 11:58:01   

I understand your situation and it's a bit problematic. The main issue is that the mappings you created state that a datetime value is coming back from the database, not a binary value. This means that the typed entity field mapped onto that table field is stated to be of type DateTime and the binary value can't be converted to DateTime.

So as the entity classes use types for the fields (as it's normal C# code) this won't work: the value is read from the DB, but when you access the field BirthDate through the property the system needs to return a DateTime and that doesn't work of course: the value is a binary value, the cast will fail (it has no other option).

However it's not all misery, there are solutions.

The easiest is this: create a partial class of the generated CommonEntityBase (in the EntityClasses folder). In the partial class, override the method PostProcessValueToGet(). This is the method that's called right before the value to return from a property read is handed over to the property. So on the code:

var bd = someEntity.BirthDay;

it will end up in that method right before the getter of BirthDay will perform its cast to DateTime and return the value.

In your override you have to check whether the types of the value (in valueToReturn) matches the type in fieldInfoOfFieldToGet.DataType. In the case of BirthDay, fieldInfoOfFieldToGet.DataType will be typeof(DateTime) and valueToReturn will either be null or a binary or a DateTime.

If the types match (no need to know what the value is, the types are enough), do nothing. If the types don't match (in case of the encrypted value is read from the database, so it's a binary value), you have to decide what to return there: a default date, null (the field then has to be made optional in the designer) or leave it as-is and result in an exception.

The method is used by all entities, so you have to implement this once in a general way which should be rather straight forward. It will then make sure no cast exceptions arise due to the type mismatches between encrypted data and the expected real value, it will also not require you to decrypt any data if the user isn't able to: the encrypted data is inside the entity but not decryptable and not exported outside the entity as well. It's transparent as you don't have to anything to make it work with decrypted data: when the user is able to decrypt the data as the key is on the user's system, the values will obviously match the type of the field and nothing is done in your override so it should work normally like any non-encrypted field.

Hope this helps simple_smile

(edit) if you create custom projections this doesn't work though: the value will then mismatch the end type. So this is only for entities.

Another way to solve this, which involves more work, is by using type converters, one for each .NET type used in the fields. Say you encrypt some DateTime fields and string fields. you then create two type converters, one for DateTime and one for string.

they convert from / to the same type. So the DateTime type converter converts from / to DateTime, the string type converter converts from / to string. Both are effectively a no-op if the passed in value is null or a string. However, if the passed in value is a binary, they know the value is encrypted and then you have to decide what to do: return null (which will result in the default value being used) or a default value, e.g. string.Empty for the string values.

This is also transparent: if the user can decrypt the values, the values read from the DB are in the real types, nothing is done. When the user can't decrypt the values, the values are binary and converted right after the datareader to the value you convert to in your type converter. This also works in custom projections as the runtime will try to re-use the type converter of the fields the actual outer projection is projecting from.

Type converters are really easy to create. the only downside to this approach is that you have to assign the type converters to each mapped field that's encrypted, which is perhaps a bit of work as the designer won't do that for you as the types match so it won't automatically assign the type converter. You also have to know which fields are encrypted up front and forgetting one will cause an error. You could ofcourse implement both approaches.

If you need help with a type converter, please let us know.

Frans Bouma | Lead developer LLBLGen Pro
joef
User
Posts: 8
Joined: 18-Mar-2016
# Posted on: 15-Apr-2016 15:58:25   

Hi Otis,

Thank you for the detailed response.

Would there be sample code for the PostProcessValueToGet around anywhere ?

Many Thanks,

Joe

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Apr-2016 16:31:04   

joef wrote:

Hi Otis,

Thank you for the detailed response.

Would there be sample code for the PostProcessValueToGet around anywhere ?

Many Thanks,

Joe

No, but it's straight forward. It's this method: http://referencesource42.llblgen.com/#SD.LLBLGen.Pro.ORMSupportClasses/Core/EntityCore.cs,2242

example:

protected override void PostProcessValueToGet(IFieldInfo fieldInfoOfFieldToGet, ref object valueToReturn)
{
    if(valueToReturn==null || fieldInfoOfFieldToGet==null)
    {
        return;
    }
    if(fieldInfoOfFieldToGet.DataType.IsAssignableFrom(valueToReturn.GetType()))
    {
        // type of value is compatible with type of field.
        return;
    }
    // incompatible type, decide what to do here, based on fieldInfoOfFieldToGet.DataType, e.g.:
    switch(Type.GetTypeCode(fieldInfoOfFieldToGet.DataType))
    {
        case TypeCode.DateTime:
            valueToReturn = DateTime.MinValue;
            break;
        case TypeCode.Int32:
            valueToReturn = 0;
            break;
        // etc.
    }
}

(not tested for compilation errors, but you get the idea) I think that the typeconverter approach might be more flexible as custom projections are quite common (e.g. a simple linq query to an anonymous type already uses one). See this post for an example implementation of converting numeric database fields to bool .net types in entities: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=101335&ThreadID=18111

SDK Info on this topic for v4.2: http://www.llblgen.com/documentation/4.2/SDK/hh_goto.htm#gui_implementingtypeconverter.htm

Frans Bouma | Lead developer LLBLGen Pro
Posts: 24
Joined: 19-Feb-2007
# Posted on: 04-May-2017 18:09:12   

For SQL 2016 Column Encryption, just add 'Column Encryption Setting = Enabled;' into your connection string and SQL will decrypt for your app automatically... If the Certificate for that column is available in the Certificate Store on the machine. This worked for me without type casting.

In Otis's code example with override (works great for me also), PostProcessValueToGet, you could implement a security switch/case for your different user levels and decrypt it there for a specific user or level, although you must leave the 'Column Encryption Setting = Enabled, out of connection string for this model.

-5.0 (5.0.2) RTM

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 04-May-2017 20:18:31   

Thanks for the feedack