Encrypted Procedures not synchronized on SQL 2005 Target DB

[beardedfreak]

Hi,

We use the SQL Toolkit (version 5.3) to update our clients installations with the latest schema from a snapshot of our SQL 2000 development database.

Much of our business logic is located within stored procedures (over 750) which are all encrypted to protect our intellectual property (and prevent tampering).

Several of our clients are using SQL 2005 and our upgrade process is now failing. A manual sync between SQL 2000 and SQL 2005 gives the following warning: "[object] cannot be decrypted. Its contents cannot be compared and it cannot be synchronized."

I was planning to blindly update all procedures, functions and views in the target DB but cannot achieve this through the 'BuildFromDifferences' method. Is this functionality available in version 6?

If the objects have to be scripted seperately, which is the best way to deal with the interdependency as I assume they must be dropped and then recreated in the correct order. We have Packager and could possibly create a script to apply after the table syncronization. I'm not a fan of the seperate script because the table sync may complete successfully but the procedure script may fail leaving the DB in an unknown state - possibly requiring a restore on a Production DB.

Any help or advice would be greatly appreciated.
Many Thanks

[Brian Donahue]

Hi,

There isn't much that I can suggest except to drop the encryption from all of the stored procedures. SQL Compare could compare encrypted objects only because of an exploit on SQL Server 2000 (It's not terribly serious -- the user running SQL Compare has to be logged in as a SYSADMIN).

SQL Server 2005 closed this hole, so the encrypted objects can no longer be decrypted. SQL Compare Engine 6.x still lacks the capability to decrypt objects on SQL Server 2005 and it may not be possible and/or feasable to do this in the future.

Sorry about the confusion.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521