The Recording
The Panelists
- Kevin Feasel
- Mala Mahadevan
- Tracy Boggiano
Notes: Questions and Topics
Data Platform WIT Day
Mala and Kathi Kellenberger are hosting Data Platform WIT Day on March 5th, and Tracy will be one of the speakers. Registration is free via Meetup.
PASS Content via Redgate
Our first topic was around PASS content being available through Redgate. They’ve made PASS Pro content available on the Redgate Hub, and accessing that previously-gated content is now free.
In addition, they have taken control of the PASStv YouTube account. They’re uploading the PASS Summit 2019 Learning Pathway videos as well as PASS Virtual Summit 2020 videos.
We also spent some time on the relative merits of Redgate directly advertising on the PASStv YouTube account. Here’s my stance (after Solomon made good points in chat): if Redgate intends to release PASS content as a good deed, then yes, slap Redgate logos on things, make it available via Redgate’s website, and advertise away. They bought the rights to this content and PASS had the authority to sell them the rights, so no complaints there.
If Redgate intends to foster a new community organization, the better approach is to keep it as vendor-neutral as possible. I don’t think a SQL Server community really succeeds if it’s tied closely into one commercial enterprise—even if that enterprise were Microsoft, but especially not a third-party vendor. It plays havoc with the ability to be a 501(c)(3) (something PASS never was due to its early closeness to Microsoft) and probably drives away other vendors who supported PASS in the past but don’t want to give too much support to their competitor.
Ultimately, that’s Redgate’s choice to make, but I don’t think there’s a way to thread the needle.
Always Encrypted Issues
Our next topic was around Always Encrypted, sent in by @rednelo7625. Here’s a summary of the question:
I have a table with about 5000 rows called [Case] with a column named “Title”. It was encrypted with an encryption type of Randomized. After a year, I needed to change the encryption type to Deterministic because of a new query which includes “Title” in group a by clause. You can only do that if the type is Deterministic.
[…]
So far this week 142 cases have been added to the table. And I’ve seen 1 user still get the same 500 error when inserting a new record through the website:
Operand type clash: nvarchar(10) encrypted with (encryption_type = ‘RANDOMIZED’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘CEK_Auto1’, column_encryption_key_database_name = ‘DBNAME’) is incompatible with nvarchar(max) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘CEK_Auto1’, column_encryption_key_database_name = ‘DBNAME’).
Also, now when I rebuild the index on the table I get the following DBCC error:
Msg 206, Level 16, State 2, Line 29
Operand type clash: nvarchar(max) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘DBNAME’, column_encryption_key_database_name = ‘DBNAME’) is incompatible with varchar
So, it appears that somewhere the column is still associated as Randomized or not at all…
There are two error messages of note here. The first is that nvarchar(10) clashes with nvarchar(max). The second is that nvarchar(max) clashes with varchar.
For the first message, my best guess is that some stored procedure or .NET calling code has a parameter set to 10 bytes and is trying to update the column. When working with Always Encrypted, the data lengths must match up, so if you define the column as nvarchar(max), all calls from .NET must have a data length of -1 (equivalent to max).
For the second message, that seems kind of weird and is around index rebuilding. My recommendation there was to rebuild the table. Essentially, take a downtime and:
- Change all of the encrypted columns back to plaintext
SELECT * INTO dbo.BackupTableName FROM dbo.CurrentTableNameto move all data over to a new table.- Drop foreign key constraints or stored procedures which reference the table. Drop the table.
- Re-create the table with plaintext columns.
- Re-load the data from
dbo.BackupTableName. - Encrypt using the combination of deterministic and randomized encryption as desired.
- Re-create the stored procedures which reference the table. Also re-create any foreign key constraints as needed.
- Make sure any .NET code is using correct data lengths.
I’m not sure if switching from deterministic to randomized encryption in Always Encrypted is officially supported but there isn’t much information available online about it. Given rednelo’s table size (about 5500 rows), I’d rather just re-create the thing than try to troubleshoot it and possibly see other errors pop up in the future.
Power BI Dashboard in a Day
If you are new to Power BI, the Dashboard in a Day is a good way of understanding some of what is possible with the product. Microsoft has an actively updated list of events, so check one out in your time zone. If you’re already familiar with Power BI, you won’t gain much from it, though—it’s really intended to be an introductory overview of the product.