Encrypted Procedures not synchronized on SQL 2005 Target DB
beardedfreak
Posts: 2 Bronze 2
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
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
Comments
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.