Encrypting existing data – AE learning series part 02

  • Article

In the first post of the series we built our first application with Always Encrypted infrastructure. Now it is time to insert some data and the options are not that many. Because the AE is only supported by ADO.NET connections, you must use a tool that supports it, therefore you can use:

  • Use SSMS Encrypt Data wizard (it has some limitations)
  • Use SSMS Import Export Wizard (choosing ADO.NET destination adaptor)
  • Use SSIS – again, use transformations, that support ADO.NET library

Encrypting data using SQL Server Management Studio Encryption Wizard

This option is pretty straight-forward. It is part of SSMS and you only have to select the tables/columns to encrypt. A great walk-through you can find in the following article.

According to me, this option would be great for in-place encryption of couple of columns. Under the hood the wizard is creating a new table, moving the data and dropping the old table (unencrypted). The good thing is that the wizard by default checks all limitations and gives you only the columns you CAN encrypt.

Importing data using Import Export Wizard

Using the import/export wizard is a described here.

I would use the wizard when I want to control the process myself and not count on the SSMS Encryption Wizard, which is doing a lot of things in the background. This is especially good option for large databases where you have huge tables/databases and you have small maintenance windows during which you can encrypt the data.

Importing data using SSIS

A great new article has just been released on using SSIS for encrypted data. Read this post for details.

SSIS still does not support entirely AE because not all transformations support ADO.NET connection managers. Still, being able to use SSIS should cover many scenarios for bulk importing data, moving it from one table to another or as part of most of the ETL/ELT processes.

How about BCP or other tools for ETL? Well, there is an option for that as well. Refer to Migrating Sensitive Data Protected by Always Encrypted.

So, now you can migrate some data to your database so you can have more realistic data to work with.

 

Thank you for reading and happy holidays :-)

Posts from the AE Learning series