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.
But the next trick is to remember to use your developer account to run certmgr /r currentUser to import the *.pfx file to that account’s certificate store.
There was one change in the Certificate export on Windows 10 that I hadn’t noticed in the past on the Export File Format window. Microsoft added a fourth checkbox under (.PFX) called [Enable certificate privacy].
That’ll be something else to try breaking in the future to see what it removes from the key.
For my next step I found it very tempting to move and convert the certificate onto the Linux database server with openssl pkcs12 and openssl x509 to see if I could unencrypt the data on the server, but then I remembered that it violates a core design concept of Always Encrypted. This core concept is that the encrypted data is to remain encrypted on the server, en route to the server, and never decrypted on or within the database. Keeping the data encrypted in the cloud is a key use case for this feature. As someone else said, a compromised server tells no secrets.
As before, remember to add ‘Column Encryption Setting=enabled’ to any client that needs to decrypt the data.
I used NOAA data for my test, encrypting the temperature column, because, you know, it’s climate change, which may or may not be real. Actually, most of the NOAA data is real, or int. So, according to the government, climate change is real, so don’t try to use float, int or currency:
Without ‘Column Encryption Setting=enabled’ the data looks like this:
/*————————
SELECT temp
FROM [noaaB].[dbo].[SurfRad]
————————*/
temp
——————————————————————————————————————————————————————–
0x01692B5B2B9D46A04945874A841A9A1D2288F5D60A4A9CDA82B9BFB23C8FF4FD965A0C14F25349D4A9AAC03789D2DC6719558422F74D9294C7A09D29004230BAA4
0x01692B5B2B9D46A04945874A841A9A1D2288F5D60A4A9CDA82B9BFB23C8FF4FD965A0C14F25349D4A9AAC03789D2DC6719558422F74D9294C7A09D29004230BAA4
0x01A05482B2A9FA7538B3A18D7FE0E2EC2B59DDCD11632AB27872233F101DE4DB1B6E46D673216C61336B531970F85C67D3BB5051DC1196CB8ACA636952653BC1F5
0x01A05482B2A9FA7538B3A18D7FE0E2EC2B59DDCD11632AB27872233F101DE4DB1B6E46D673216C61336B531970F85C67D3BB5051DC1196CB8ACA636952653BC1F5
(4 rows affected)
(Note I’m using deterministic encryption so the first two rows and last two rows are the same. With ‘Column Encryption Setting=enabled’ the data looks like this:
/*————————
SELECT temp
FROM [noaaB].[dbo].[SurfRad]
————————*/
temp
————-
-10.4
-10.4
-10.3
-10.3
(4 rows affected)
I tried a C# test from an old app.
One thing I found was that Integrated Security doesn’t work out of the box from a windows domain to a linode server … not without a whole lot of tunneled LDAP work.
Next, I needed to add strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;
Without that, the output looks like this:
C:\ConsoleApp1.exe
2018, System.Byte[], 0
2018, System.Byte[], 0
2018, System.Byte[], 0
2018, System.Byte[], 0
With the ColumnEncryptionSetting, the output looks like this:
C:\ConsoleApp1.exe
2018, -10.4, 0
2018, -10.4, 0
2018, -10.3, 0
2018, -10.3, 0
At last, I got to the migration (aka road trip) … the upgrade and platform shift. My upgrade path was:
- noaaA – SQL Server 2016 – Windows Server 2016
- noaaB – SQL Server 2017 – Windows10
- noaaC – SQL Server 2017 – Ubuntu Linux 16.04 LTS
The first step, backing up and restoring AE from SQL Server 2016 to 2017, (Windows to Windows) was uneventful, no surprises.
After using scp to move the database backup onto the Linux PC, the next step in the platform shift was to get the backup file into the default backup directory so that it could be restored. In my case the default location was here:
/var/opt/mssql/data/localhost_noaaB_FULL_20180327.bak
Microsoft kind of leaves it up to your discretion on how and where to setup the backup directory. I was in a hurry for the test, so I just chmod’ed the directory to 777. I totally did not just recommend doing that. After the temporary fix the restore worked:
RESTORE DATABASE noaaC
FROM DISK = N’/var/opt/mssql/data/localhost_noaaB_FULL_20180327.bak’
WITH FILE = 1,
MOVE ‘noaaB’
TO ‘/var/opt/mssql/data/noaaC.mdf’,
MOVE ‘noaaB_log’
TO ‘/var/opt/mssql/data/noaaC.ldf’,
NOUNLOAD, REPLACE, STATS = 10;
11 percent processed.
20 percent processed.
30 percent processed.
41 percent processed.
51 percent processed.
60 percent processed.
71 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 416 pages for database ‘noaaC’, file ‘noaaB’ on file 1.
Processed 8 pages for database ‘noaaC’, file ‘noaaB_log’ on file 1.
RESTORE DATABASE successfully processed 424 pages in 0.032 seconds (103.393 MB/sec).
After the migration, the same key and passphrase opened up the encrypted column just the same as the other platforms. No pain at all, it just worked.
I’ve been using a Windows 10 client on paperspace.com for my testing, and so far it’s going very well. An upcoming post, and an upcoming SQL PASS meeting on April 12 will be about some of the weird things I’ve discovered when working with data in SQL Server Always Encrypted.