Option to overwrite "WITH ENCRYPTED" objects

Forum for users of SQL Compare schema synchronization utility

Option to overwrite "WITH ENCRYPTED" objects

Postby Verifex » Wed Aug 27, 2008 8:05 pm

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.
Verifex
 
Posts: 5
Joined: Thu Apr 03, 2008 7:57 pm

Postby Michelle Taylor » Fri Aug 29, 2008 1:28 pm

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.
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Re: Option to overwrite "WITH ENCRYPTED" objects

Postby mpeschka » Fri Oct 10, 2008 3:51 pm

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.
mpeschka
 
Posts: 7
Joined: Wed Aug 16, 2006 3:31 pm
Location: Dayton, Ohio

Postby Simon C » Fri Oct 10, 2008 5:35 pm

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
Simon C
 
Posts: 140
Joined: Tue Feb 26, 2008 4:56 pm
Location: Red Gate Software

Postby mpeschka » Fri Oct 10, 2008 6:33 pm

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.
mpeschka
 
Posts: 7
Joined: Wed Aug 16, 2006 3:31 pm
Location: Dayton, Ohio

Postby Brian Donahue » Fri Oct 10, 2008 6:49 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests