locked
SQL server 2016 : Always Encrypted - Encrypted Column wizard failed- 'Task failed due to following error: Cannot save package to file. The model has build blocking errors: RRS feed

  • Question

  • I am trying to encrypt column in a table through SMO Encrypt column wizard .I am getting an error  " 'Task failed due to following error: Cannot save package to file. The model has build blocking errors:" when click finish now(after couple of minutes of execution). Why it's trying to build whole database dependency even though the table does not any dependency with any other object?.Any idea to resolve an issue?

    Nov 15 2016 09:29:59: Log opened. TraceLevel:Informational
    Nov 15 2016 09:30:26 [Informational] WizardSummary: Message:Source database settings.

    Nov 15 2016 09:30:26 [Informational] WizardSummary: Message: Source server name: ED2K12R2FQM-06.

    Nov 15 2016 09:30:26 [Informational] WizardSummary: Message: Source database name: TE_3E_DEVDB.

    Nov 15 2016 09:30:26 [Informational] WizardSummary: Message:Re-encrypt column SSN.

    Nov 15 2016 09:30:26 [Informational] WizardSummary: Message:  Table name: AE_Palani.

    Nov 15 2016 09:30:26 [Informational] WizardSummary: Message:  Encryption key name: TE_AETest_AlwaysEncryptedCEK.

    Nov 15 2016 09:30:26 [Informational] WizardSummary: Message:  Encryption type: Deterministic.

    Nov 15 2016 09:30:27 [Informational] WorkitemExecution: Message:Work item 'Performing encryption operations' started..

    Nov 15 2016 09:30:27 [Informational] TaskUpdates: Message:Task: 'Performing encryption operations' -- Status: 'Started' -- Details: 'Task 'Performing encryption operations' started ....'.

    Nov 15 2016 10:26:43 [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].[NxUserTKDefault]: View: [dbo].[NxUserTKDefault] has an unresolved reference to object [TE_MASTER].[dbo].[TEUSERIDView].
    Error SQL71561: Error validating element [dbo].[NxUserTKDefault]: View: [dbo].[NxUserTKDefault] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[NxUserTimekeeper].[z0], [TE_MASTER].[dbo].[TEUSERIDView].[UserID] or [TE_MASTER].[dbo].[TEUSERIDView].[z0].

    Tuesday, November 15, 2016 6:40 PM

Answers

  • Hi Pals_TR,

    I have voted this connect, I think more people who meet the same problem will vote this and this issue will soon be solved.

    Besides, if you are satisfied with our help, could you please mark these replies as "Answered"? This can be beneficial to other community members reading this thread.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pals_TR Friday, December 9, 2016 6:17 AM
    Tuesday, November 22, 2016 2:45 AM

All replies

  • Don't know why it wants to build a model for the entire database, but it's probably the joy of object-oriented program: it uses a method that does too much.

    Can't you just fix the problem that causes the model to fail?

    Tuesday, November 15, 2016 9:23 PM
  • Thanks Erland..It creates 1000's unresolved reference to object issue - INFORMATION_SCHEMA_RELATIONSHIPS,INFORMATION_SCHEMA_VIEW_TABLES,INFORMATION_SCHEMA_CONSTRAINTS.. etc

    Can you check the log file and let me know if still you think that only option?

    https://drive.google.com/drive/folders/0B4m8jrlzCaOwTE54dUFZcDgzUmc?usp=sharing

    Tuesday, November 15, 2016 10:17 PM
  • Seems like you have a lot views in this database refers to system tables, and this trips the wizard. That would be interesting to research, but I am afraid that I may not be able to find the time to do that for the next few days.

    In your original post you talk about the "SMO Encrypt column wizard". This is not a name that I recognize exactly. Are you running this from SQL Server Management Studio? Or you are you using SQL Server Data Tools? The error messages and the talk about a "model" makes me think that it may be the latter.

    Wednesday, November 16, 2016 7:25 AM
  • Yes, SQL Server Management Studio studio.

    Expand Table and expand Column-->Right click Encrypt Column-->Wizard -->Finish now-->Task failed with package issue.

    I am struggling to very badly to resolve in either way(workaround or find right solution).It will be great if can provide some oversight or few things to try.. etc

    I appreciate your help on this.

    Wednesday, November 16, 2016 8:34 PM
  •  

    Hi Pals76,

    ‘Error SQL71561: Error validating element **.****: View: **.**** contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: **.**.**’

    I have read your full error message, based on the error message and my researching, I reproduced this problem. Based on my test and the above error message, this error is caused by that the two-part naming conventions cannot reference the three-part naming conventions in View. You can see it in your view, it is as shown as below picture:


    To solve this problem, we can firstly 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 and then delete all the views which use the three-part naming conventions. After that, we can operate always encrypt and then run the script got from Generating Scripts to re-create the view.

    Best Regards,
    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.







    • Edited by Teige Gao Thursday, November 17, 2016 11:43 AM
    • Proposed as answer by Teige Gao Tuesday, November 22, 2016 2:46 AM
    Thursday, November 17, 2016 11:35 AM
  • That's is indeed a little... non-intuitive.

    I am not sure that this is easily fixed, but I have submitted a bug report. You can vote and add your business case to it.
    https://connect.microsoft.com/SQLServer/feedback/details/3111906

    Kudos to Teige for finding that the view was the culprit!

    Thursday, November 17, 2016 9:44 PM
  • Thank Teige and Erland for your help.It's hard to find and also take hours to encrypt single column (just 3 rows and my db has 15000 objects) so only option is to implement targeted verification instead of entire database. 

    SQL server "Always Encrypted" team confirmed that this is BUG.They asked me to create a connect case.

    [MS Team]:

    >>We have investigated this issue, based on the log you provided and we have confirmed this is a bug in the Always Encrypted tooling, which currently >>performs a strict integrity verification of your entire database schema (while it should really perform a more targeted verification, focused on the >>columns, you want to encrypt). 

    https://connect.microsoft.com/SQLServer/feedback/details/3111925/sql-server-2016-always-encrypted-encrypted-column-wizard-failed-task-failed-due-to-following-error-cannot-save-package-to-file-the-model-has-build-blocking-errors



    • Proposed as answer by Teige Gao Tuesday, November 22, 2016 2:46 AM
    Friday, November 18, 2016 1:54 AM
  • Hi Pals_TR,

    I have voted this connect, I think more people who meet the same problem will vote this and this issue will soon be solved.

    Besides, if you are satisfied with our help, could you please mark these replies as "Answered"? This can be beneficial to other community members reading this thread.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pals_TR Friday, December 9, 2016 6:17 AM
    Tuesday, November 22, 2016 2:45 AM