Option to overwrite "WITH ENCRYPTED" objects

Verifex

If one side of the comparison has the full text of a sproc, we should have the option of overwriting the ENCRYPTED version of the sproc with a non-encrypted version.

Also, after doing a bit of digging, it seems that the "WITH ENCRYPTED" option is listed as obfuscation by the folks at Microsoft. After doing a bit more legwork, it seems that there are ways to unencrypt "WITH ENCRYPTION" SQL objects and overwrite them with unencrypted versions.

Michelle T

I'm not sure what exactly you're asking for here - doesn't synchronizing the non-encrypted object over the encrypted object do what you want?

As for actually unencrypting "WITH ENCRYPTION" objects, we can already do it for SQL Server 2000 objects and it's possible we'll manage to do the same for 2005/2008 objects in the future. Currently the well-known method to unencrypt these objects requires use of the Dedicated Admin Connection, however, and we think most people won't want SQL Compare to use the DAC so that route doesn't look worth pursuing at present.

mpeschka

Michelle Taylor wrote:

I'm not sure what exactly you're asking for here - doesn't synchronizing the non-encrypted object over the encrypted object do what you want?

I'm trying to achieve the same thing (overwriting encrypted objects with newer versions), and it is not working. The comparison lists the objects as different, and indicates that they will be synchronized. But when you actually run the Synchronization Wizard, it states that the objects "cannot be decrypted. Its contents cannot be compared and it cannot be syncronized." Viewing the generated SQL script shows that it indeed will do nothing.

Is there any chance to have this behavior changed in a future release? It's terribly annoying to have to manually delete all encrypted objects in the target database before synchronizing them over.

Simon C

The latest release of SQL Compare (7.1) can decrypt WITH ENCRYPTION text objects on 2005 and 2008 databases, which will probably achieve what you want.

Simon C

mpeschka

Is there some specific requirement that must be met before it can decrypt them? I had tried that option before posting, but I received an error ("Index was outside the bounds of the array.")

Personally, I'm not concerned with having the object text decrypted, especially if it requires additional setup. I'd rather just have the target objects overwritten.

Brian Donahue

Hi,

I just found out three days ago that there is a bug in the decryption that causes an "Index out of bounds" exception on 64-bit editions of SQL Server. Apparently the decryption is done using a page reader which is 32-bit aligned. This should be fixed in the next version.