How to ignore the 'WITH ENCRYPTION' phrase ?

mah530mah530 Posts: 7
edited July 28, 2009 8:22AM in SQL Compare Previous Versions
I need to compare the objects in two SQL 2000 databases. In one database, all of the objects are encrypted, in the other they are not. ( This will always be the case). When I compare the objects, all objects are considered different due to the WITH ENCRYPTION clause that exists only in one database.

How can I set up the compare so that it will ignore this one difference, and give me just the legitimate differences?

Can I set it up to ignore specific text - such as 'WITH ENCRYPTION'?

Thank you!!!
· Share on Twitter

Comments

  • Eddie DEddie D Posts: 1,807 Rose Gold 5
    Thank you for your post into the forum.

    Unfortunately there is not an option available within SQL Compare to Ignore the 'WITH ENCRYPTION' syntax when comparing or synchronizing database objects.

    Also I am unaware of a work around for the problem you have reported to us.

    I have added the details of this forum post to an existing Feature Request, requesting to add an Ignore WITH Encryption option. The reference number for this request is SC-4419. At this time the feature request is marked for review. Hopefully the requested feature will appear in a future version of SQL Compare.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • mah530mah530 Posts: 7
    That makes things very difficult here, because 90% of the time we will be trying to verify/synch the encrypted version against our master database which will never be encrypted.

    This means visually checking each object to see if the 'difference' is only due to the encryption phrase. Thousands of objects.

    I am interested in any workarounds that you could think of. I was considering scripting the encypted db, and removing the 'with encyption' , and then comparing it to the master d...but, I don't think I can even generate a script against the encypted objects...

    Any other workaround ideas??

    Thanks
    · Share on Twitter
  • mah530mah530 Posts: 7
    I started looking at the SQL Compare SDK - to see if I could try to do this myself.

    Could I try creating new Option Type - "IgnoreWithEncryption" - that would be processed in similar way as the existing option type "IgnoreWithNocheck"? Only difference would be that it would look for the WITH ENCRYPTION phrase in the object text, rather than the WITH NOCHECK on foreign keys and check constraints.

    Is it feasible? or are all needed classes sealed/unavailable to me?

    Thank you!
    · Share on Twitter
  • Simon CSimon C Posts: 140
    Unfortunately, you can't add a new option yourself that will do what you need. As well as all the classes being sealed & obfuscated, there's some internal infrastructure that has to be updated when new options are added.

    A workaround you can use, if you are using the SDK, is to write your own state machine that parses the object text in the relevant Text property if the objects aren't equal, and determines if the only difference is the WITH ENCRYPTION property missing in one.

    Simon C
    · Share on Twitter
  • mah530mah530 Posts: 7
    How do I acces the text property - didn't see it in the Difference class
    Thanks
    · Share on Twitter
  • Simon CSimon C Posts: 140
    The Text property is available on the DdlTrigger, Function, StoredProcedure, Trigger and View classes, accessible through the ObjectIn1 and ObjectIn2 properties of the Difference class
    · Share on Twitter
  • mah530mah530 Posts: 7
    Is there any way for me to modify the ObjectIn1, ObjectIn2 properties before doing the compare? Would I have rights to modify the text in these objects (trigger text, stored proc text, etc)? I would want to remove the 'WITH ENCRYPTION' phrase before the actual compare is performed.

    Is this possible? Without me having to write the actual compare process?
    · Share on Twitter
  • Simon CSimon C Posts: 140
    No, I'm afraid you're unable to modify the object model after the database registration has taken place. You would have to modify the objects on the database itself to change the resulting object model in SQL Compare.

    Simon C
    · Share on Twitter
  • mah530mah530 Posts: 7
    thank you for the reply. looks like I will not be able to use SQL compare.

    Please consider adding the option to 'Ignore WITH ENCRYPTION' in a future release of the product.

    Thanks
    · Share on Twitter
Sign In or Register to comment.