Problem Summary
While I am trying to configure Always Encrypted for one of my databases, I received an error as below.
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.
Solution/Work around
To solve the problem, you need to make sure the referenced objects are present in your environment. In my case, I copied all dependent databases in testing environment and followed the below steps.
- 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
Additional Information
When I tried to replicate the same issue in another environment with the below configuration, the above issue could not be replicated.
Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 14393: ) (Hypervisor)
Test Scripts
--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.
Try yourself with the above code and share your comments…