Build your first app with SQL 2016 Always Encrypted - AE learning series part 01
Dear reader, this is the first post of a learning series I decided to compile over the Christmas Holidays. I am working on one of my first implementations on AlwaysEncrypted and as a new technology, we, as SQL guys, need to pump-up our knowledge and share what we found along the way, especially if it can help others.
Note before we continue: everything in this post and the next ones are my personal experiences and thought and not the ones of my employer. Most of the time they are aligned J. Also – I will not be repeating or just copying other people’s work. I’ll rather share their articles, so note that the series will have a lot of links either for detailed guides of implementing something or for some additional reading (before going to bed for instance)
Short overview of Always Encrypted in SQL Server 2016
So what is AlwaysEncrypted – it is a brand new technology in SQL Server 2016 that will help enterprises and developers to protect sensitive and PII information. You might say that this is not something new and that you can achieve that with other SQL Server features (such as cell level encryption), however AE has couple of extremely good advantages – first, it now protects the PII data from the system administrator as well; secondly – the encryption/decryption functions are carried out by the client driver, so the SQL Server does not have to handle additional CPU pressure for those operations.
The overall picture looks like this:
So the important thing here is:
- SQL Server stores only the already encrypted data
- Encryption/decryption is pushed to the client layer
There are multiple restrictions and specifics around the implementation of AlwaysEncrypted, so follow the next articles for details.
Building your first app
Instead of trying to develop a new app myself for the demo, I decided to reuse something that is already in place – a step-by-step guide for building MVC Web App connected to a local dev server. Please find the detail steps here:
1. Setup the database and encryption keys (details in the following article) –
- Create a database that is hosted on SQL Server 2016 build (at time of writing this is CTP 3.1).
- Create a database master key (if it does not already exist)
- Create a Column Master Key in your database (my CMK for the demo will be named CMK_DEMO_AE_WebApp)
- Create a Column Encryption Key (CEK_DEMO_AE_WebApp)
- If you are working on different machines or have a remote SQL Server, make sure that the user that you are developing has the certificate of the CMK imported in its personal store!
This is how my database looks like after implementing the keys:
2. Create the schema and insert some sample data
- Use the schema described in the following article. Make sure to reflect the proper Column Encryption Name in the scripts, as they are using one named CEK1 and in my demo it is CEK_DEMO_AE_WebApp.
- Note that except the Courses table, you will not really have any other data. You will use the web app to insert it.
- Start here, as the original example with the Database Project will not be really helpful as in time of writing, SSDT did not support AlwaysEncrypted in DB Projects
3. Continue with the building the MVC Application -
You can follow the series (on the left side of the page from the above link) for completing the application build
After you build your application – you will have no data in the Student or Enrollment tables. So you can just add some data using debug mode (this is important, as it is a bit easier just using the app to add some data). If you need to add bulk of data – I will explain that in a later post)
At this point I hope you managed to build your first app that uses SQL Server 2016 Always Encrypted technology.
As a result, if you entered via the app some data in the Students table, then you should have something like this in your table:
Note that the LastName column is showing cypher text data instead of the actual value (which is not the case if you work with the app itself). So this is the magic of AlwaysEnrypted.
Posts from the AE Learning series