SSIS And Always Encrypted

Jakub Szymaszek links to two articles on using SSIS with an Always Encrypted database.

Using Always Encrypted:

The SQL Server 2016 Always-Encrypted feature is only supported by the ADO.NET  provider currently. It is not supported by the OleDB provider and therefore any OleDB-provider-related transformation tasks such as Fuzzy Lookup will not support Always Encrypted feature.

In the “Execute SQL Task”, parameter binding for some encrypted SQL types is not supported, because of data type conversion limitations in Always Encrypted. The
unsupported types are money, smallmoney, smalldatetime, UniqueIndentifier, DatatimeOffset, time and date.

Lookup Transformations

Add an ADO NET source connect to the table “Customers” (please ref to here get more detail about how to use ADO NET Source to connect encrypted table).

Then create a cache connection manager “Customer Cache” and set the column information as below:

Based on article #2, it looks like you can’t simply use a Lookup transformation on an Always Encrypted column; you need to pull the results into cache first and then query the cache.  That’s not exactly difficult, but if you have an encrypted column, make sure you’re not writing those columns out in plaintext because of the cache option you selected.

Related Posts

Temporal Table Permissions

Kenneth Fisher shows us the permissions needed to create temporal tables: Msg 13538, Level 16, State 3, Line 6 You do not have the required permissions to complete the operation. Well, that’s not good. What permissions do I need exactly? Well, again, according to BOL I need CONTROL on the table and its history table. For those […]

Read More

Biml Metadata, With And Without BimlStudio

Ben Weissman has a pair of posts regarding metadata models in Biml.  First up, he gives us the high-roller solution: If you’re lucky enough to be a BimlStudio user, you have access to the Biml Metadata feature! This feature allows you to build a Metadata model that fits your exacts needs which can then be […]

Read More

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031