Sep 19 2018 02:31:58 [Informational] TaskUpdates: Message:Task: ‘Performing encryption operations’ — Status: ‘Failed’ — Details: ‘Task failed due to following error: Cannot save package to file. The model has build blocking errors: Error SQL71561: Error validating element [dbo].[ViewTableName]: View: [dbo].[ViewTableName] has an unresolved reference to object [DBName].[dbo].[TableName].Environment Details Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 6.3 (Build 14393: ) (Hypervisor) Observations In fact, looking at the wizard error log report, I have seen hundreds of error messages similar to above for different elements. While investigating, it is found that the error has raised due to the usage of three part reference in one of views. And it is confirmed by Microsoft that its a bug in the Always Encrypted tool that it performs a strict integrity verification for the entire database schema instead of the targeted verification, that means, only for the columns to be encrypted.
- Use Generate Scripts(right click on the database, select Generate Scripts in the Tasks, in the wizard, we can select View) to back up the view.
- Drop all the selected views which use the three-part naming conventions.
- Apply always encrypt feature to columns.
- Execute the generated script to re-create the view if needed
Try yourself with the above code and share your comments…
--Create two databases create Database Test1 create Database Test2 use test1 --Create the below table in Test1 create Table testtable (Col1 varchar(5000)) Insert into dbo.testtable Select ('Test data') use test2 --Create the below table in Test1 create Table testtable (Col1 varchar(5000)) Insert into dbo.testtable Select ('Test data') --Create the below view in database Test2 create View vw_test1 as Select Col1, Cast(GetDate() as datetime) as [DATECREATED], Cast(GetDate() as datetime) as [DATELASTMODIFIED], Cast(newid() as uniqueidentifier) as [_IMPORTED] From Test1.dbo.Testtable --Apply "Always Encrypted", you will see the mentioned behavior.