Red Gate forums :: View topic - Option to overwrite "WITH ENCRYPTED" objects
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

Option to overwrite "WITH ENCRYPTED" objects

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Verifex



Joined: 03 Apr 2008
Posts: 5

PostPosted: Wed Aug 27, 2008 8:05 pm    Post subject: Option to overwrite "WITH ENCRYPTED" objects Reply with quote

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.
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 525
Location: Red Gate Software

PostPosted: Fri Aug 29, 2008 1:28 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
mpeschka



Joined: 16 Aug 2006
Posts: 7
Location: Dayton, Ohio

PostPosted: Fri Oct 10, 2008 3:51 pm    Post subject: Re: Option to overwrite "WITH ENCRYPTED" objects Reply with quote

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.
Back to top
View user's profile Send private message
Simon C



Joined: 26 Feb 2008
Posts: 140
Location: Red Gate Software

PostPosted: Fri Oct 10, 2008 5:35 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
mpeschka



Joined: 16 Aug 2006
Posts: 7
Location: Dayton, Ohio

PostPosted: Fri Oct 10, 2008 6:33 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Fri Oct 10, 2008 6:49 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group