none
Issue: Comparing schemas with encrypted objects

    Question

  • Hello,

    I'm facing an issue while trying to compare two database schemas. The first thing I noticed was that, when I tried to exclude an item from the comparison, the Schema Compare tool raised the following exception:

    "Value cannot be null. Parameter name: deploymentPlan".

    Further, I found that no deployment script was being generated. The Error List within Visual Studio shows more than one thousand errors, all of them related to the encrypted objects.

    The two databases I'm trying to compare do contains encrypted objects. Anyways, I also have non-encrypted objects, that I need to compare.

    It would be nice if the tool could handle it, and ignore these encrypted objects (maybe another option in the Schema Compare Options?), so the deployment script could be generated for the rest of the objects.

    Are there any options to circumvent this issue?

    Does anyone is able to confirm this is happening?

     

    Thanks!

    • Changed type Fabricio Carvalho Friday, January 06, 2012 11:56 AM Want to get replies from MS folks on that.
    • Edited by Fabricio Carvalho Friday, January 06, 2012 11:57 AM
    Friday, January 06, 2012 11:09 AM

Answers

  • Hi Fabricio,

    Some more follow up.  Comparison of schemas that include encrypted objects will succeed with warnings in the error list for each encrypted object that was found to be different.  You will need to exclude the actions associated with these objects before updating the target or generating a script if the target is a database or a dacpac or else the update will fail.  It's OK to include them if the target is a project, although updating an object in a project based on an encrypted object in a database will overwrite any body code in the object in the project with a message saying that the object was encrypted, so you will want to be careful even in that case.  We don't currently warn for encrypted objects that are apparently the same (they could of course actually be different under the covers but the encryption prevents Schema Compare from seeing this).  Longer term I think some more control over the way we handle encrypted objects, perhaps via options like you suggest, may be valuable also.  We have this noted.

    Thanks for the feedback.  Always useful! 

    Bill Gibson



    Thursday, January 12, 2012 4:34 PM

All replies

  • Hi Fabricio,

    I can confirm the issue you're seeing caused by the presence of encrypted objects.  We have recently fixed this so that the exception should not occur.  I'll follow-up with a further reply regarding your other questions.

    Bill Gibson

    Saturday, January 07, 2012 12:30 AM
  • Hi Fabricio,

    Some more follow up.  Comparison of schemas that include encrypted objects will succeed with warnings in the error list for each encrypted object that was found to be different.  You will need to exclude the actions associated with these objects before updating the target or generating a script if the target is a database or a dacpac or else the update will fail.  It's OK to include them if the target is a project, although updating an object in a project based on an encrypted object in a database will overwrite any body code in the object in the project with a message saying that the object was encrypted, so you will want to be careful even in that case.  We don't currently warn for encrypted objects that are apparently the same (they could of course actually be different under the covers but the encryption prevents Schema Compare from seeing this).  Longer term I think some more control over the way we handle encrypted objects, perhaps via options like you suggest, may be valuable also.  We have this noted.

    Thanks for the feedback.  Always useful! 

    Bill Gibson



    Thursday, January 12, 2012 4:34 PM