Always Encrypted – kkarnsdba website

On Friday I gave a presentation at the April meeting of the newly created Santa Fe SQL Server User Group santafesql.org.

I was a little nervous since I haven’t given a presentation since a DOE conference in Amarillo, TX in 1991. But in spite of my nerves, it went OK since it was a short 30-minute talk and since we had a great group of attendees. The group was patient with a couple technical difficulties and interacted well when I lost focus doing things like shifting back from slides to demos.

My three findings were amazement, horror, and satisfaction.

The first finding, amazement, was the subject of my last blog post. SQL Server’s ability to flawlessly upgrade an Always Encrypted database over an upgrade and a platform shift to Linux is amazing. I covered it more thoroughly in the blog post than I did in the presentation Taking SQL Server Always Encrypted on a road trip.

I had some pretty serious technical issues in my presentation. Continue reading “Presentation: Three SQL Server Always Encrypted Findings.”

In the last post, I built out a SQL Server 2017 instance on a Linux host at Linode.com.

The first thing I wanted to try out was SQL Server Always Encrypted (AE). I had used AE in the past on an application and wanted to see if it still worked the same. I was also really curious to see how AE would survive migrations. So, I took it on a road trip to California. I decided to take a SQL Server 2016 database, encrypt a column with AE, restore and upgrade it into a SQL Server 2017 SQL, then restore and “platform-shift” it into a SQL Server 2017 on Linux at the Linode datacenter in Fremont, CA. I expected pain. But the pain never came. Here’s what I discovered.

I’ll skip most of the details of the SQL Server Always Encrypted setup since several others have already documented it well. A guide that I found easy to understand is here: exploration-of-sql-server-2016-always-encrypted-part-1

I was skeptical about AE support on Linux, so I tested some things first on Linux before the migration. Exporting the private key in the *.pfx file was a little tricker than I remembered. That certificate can hide in the certmgr depending on which account you use on SSMS to generate the Column Master Key and the Column Encryption Key. In my case, I created the keys while running SSMS in a privileged account. What finally worked for me was to run a command prompt as the same privileged user and then run certmgr /r currentUser to extract the *.pfx file.

Continue reading “Taking SQL Server Always Encrypted on a road trip.”