The title of this post should have been “How to implement wildcard search functionality with Always Encrypted, make deterministic encryption safer, and load initial data using SqlBulkInsert”, but as you understand, that’s just too long for a title.
A number of years ago I built a web application with “Always Encrypted” as VARBINARY columns, before Microsoft offered this feature out of the SQL Server Box. So in case the database server would be compromised by hackers, no client details could be revealed.
Just like Microsoft my Data Access Layer, built in .NET did the encryption and decryption, transparent for other code that accessed it. And I had the same challenge how to search on encrypted columns.
So with (maybe more than average) interest I was looking forward to know how the Always Encrypted feature of SQL Server 2016 works.
The full article is posted on SQLShack.com, so you can read further there ..