Always Encrypted?

[jb8628]

Hello, thank you for all you hard work on this connector. I am interested in using Always Encrypted with SQL Server 2017. This is supported by the underlying pyodbc, so I was wondering whether there is a way to make this work with the connector?

Would be a great way of handling more sensitive data.

[Benjamin-Knight]

The simplest method I can think of is to create a custom post hook macro that you pass in the columns you want encrypted along with the details of the key and encryption details that will generate the encryption for when DBT creates the model.

[jb8628]

Yup, thanks. I guess I was wondering whether using built-in SQL Server functionality could avoid having to create and manage column encryption in dbt (plus surrounding tools). Always Encrypted would handle encryption at the connection level - see e.g. here.

Guess it's not looking likely, so will consider other options.

[Benjamin-Knight]

The issue you have is that DBT will drop and recreate your table so it must know how to encrypt the column when its created, you can't avoid having DBT be aware of the requirements for column level encryption.

Unless you are referring to reading encrypted data as a data source and not encrypted data in your own models? Although taking encrypted data and storing it as unencrypted doesn't seem like something you would want to do lightly.

[jb8628]

My understanding is that anything that is written through an "Always Encrypted" configured connection will be encrypted where this is specified for the column in the SQL server. Thus, if DBT used such a connection there would be no loss of encryption at rest.