Possible bug with Always Encrypted wizard on a table with an existing disabled index

Copper Contributor

When using the Always Encrypted Wizard (in SSMS in the "Object Explorer" right click table/column then choose "Encrypt Columns" to get to the wizard...).  If the table you are trying to encrypt has a disabled index prior to running the encryption wizard then it will try to re-enable the index at the end of the process.  If the index has any issues when re-enabled (EG: a unique key constraint failure) then it will break the Always Encrypted process at the very end of it (which can take a while on existing tables).  The log will show an error stating the encryption stopped because an index failed to re-enable because of an error (A unique key violation in my case).   This means you have to check if the table has any disabled indexes and drop them before you start the encryption wizard.  (see SQL below to find disabled indexes on a table) Shouldn't SQL Server know which indexes are disabled before it runs the wizard and not try to re-enable them? (As part of the process in the wizard, it is probably setting all indexes on the table to disabled at the start of the process and when it finishes encrypting the data it then tries to re-enable all indexes on the table even if they were disabled before the wizard was started.)

-- Find any disabled indexes on the table before you start the encryption wizard

SELECT i.name AS Index_Name, i.index_id, i.type_desc, s.name AS 'Schema_Name', o.name AS Table_Name
FROM sys.indexes i
JOIN sys.objects o on o.object_id = i.object_id
JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE i.is_disabled = 1
AND o.name = 'TABLE_NAME_HERE'

 

It would be nice to get this fixed so we don't have to drop disabled indexes before running the Always Encrypted Wizard in SSMS or at least fail at the beginning of the encryption process and say you need to drop or enable any disabled indexes on the table first.

 

0 Replies