Red Gate forums :: View topic - DBCC 3604 & "Decrypt encrypted objects on 2005 and 2008"
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

DBCC 3604 & "Decrypt encrypted objects on 2005 and 2008"

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



Joined: 24 Mar 2010
Posts: 4

PostPosted: Wed Mar 24, 2010 6:58 pm    Post subject: DBCC 3604 & "Decrypt encrypted objects on 2005 and 2008" Reply with quote

I've read a bit about this issue here:
* http://ftp.red-gate.com/messageboard/viewtopic.php?t=961&highlight=&sid=b0aa028580e878972e78492da045245d
* http://ftp.red-gate.com/messageboard/viewtopic.php?t=842&highlight=3604&sid=4dea7c6d2e0ffddb28e0184abe715626

I have a database with 869 objects in sys.objects. The breakdown of objects is at the end of this post. There is one encrypted object, a stored procedure. When I run a comparison involving this db and set the behavior "Decrypt encrypted objects on 2005 and 2008" to true, I receive 3330 entries in the SQL & App Event logs - 1650 pairs of

DBCC TRACEON 3604
DBCC TRACEOFF 3604

When I run the same comparison with the behavior "Decrypt encrypted objects on 2005 and 2008" set to false, I receive 2 entries - 1 pair.

Further, as I'm reading, I see this statement "When Sql Compare saves a snapshot or scripts folder, this option is set, and all encrypted objects are decrypted."

Questions:
* Is it absolutely necessary to perform the decryption steps for every comparison item whenever "Decrypt encrypted objects on 2005 and 2008" is set to on? Could the app not first ask the question, is this item encrypted?

* Is it possible for SQL Compare to write a message to the SQL Log / Windows App Event log saying something like "Hi, I'm such and such app and I'm doing a comparison, sorry for filling up the logs with the following messages [x, y]. Go here to learn more http://..."

* Is the statement regarding snapshots saying that for every snapshot, the behavior "Decrypt encrypted objects on 2005 and 2008" is set to true, and that this can not be changed? If the behavior can be changed, would you please specify?

While all this may seem like a bit to do about nothing, please consider, I've just spent about 3 hours reading, testing, verifying this behavior so that I can assure my new boss @ this new job that I'm not wrecking his server by using one of my favorite tools. In this instance, it's not RedGate who made MegaBytes of entries in his logs, but the brand new guy. His assumption is that RedGate must be a bunch of yahoos for doing such a thing, and that assumption rubbed straight off onto me, because I brought your tools with me. Embarassed

ONLY sys.objects
100 DEFAULT_CONSTRAINT
96 FOREIGN_KEY_CONSTRAINT
3 INTERNAL_TABLE
131 PRIMARY_KEY_CONSTRAINT
3 SERVICE_QUEUE
3 SQL_INLINE_TABLE_VALUED_FUNCTION
35 SQL_SCALAR_FUNCTION
221 SQL_STORED_PROCEDURE
5 SQL_TABLE_VALUED_FUNCTION
2 SQL_TRIGGER
41 SYSTEM_TABLE
2 UNIQUE_CONSTRAINT
217 USER_TABLE
10 VIEW
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 922
Location: Red Gate Software

PostPosted: Fri Mar 26, 2010 6:59 pm    Post subject: Reply with quote

Thank you for your post into the forum.

For SQL Compare to read decrypted objects without using a dedicated admin connection, we have to read in the pages corresponding to the system tables and reconstruct them in memory. As SQL Compare does not know which objects are encrypted, each object must be checked.

The articles you refer to in your post are approximately 4 years old and refer to an older version of SQL Compare, what version of SQL Compare are you using?

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
maassql



Joined: 24 Mar 2010
Posts: 4

PostPosted: Fri Mar 26, 2010 8:00 pm    Post subject: Reply with quote

Eddie:

Sql Compare 8.

Second, thank you for taking the time to cover my question.

4 YEARS!!!!! WOW how time flies. I've been using Red Gate's awesome products for almost 10 years now!!!!!! or 8 or 9 whatever. The point is, I tried looking the information up in the forum first Wink And frankly to me, it looks a whole lot like the symptoms described in a March 2005 post to which was posted by Daniel Handley:
Quote:
Posted: Thu Mar 10, 2005 2:24 pm Post subject:
Hi

Thanks for letting us know about this problem.

We had already spotted this and have fixed it. The fix will appear in the next minor release of the product which will be released shortly.

Regards
Dan


So, my questions still kinda remain from the original post -

Question What I read was
Quote:
SQL Compare does not know which objects are encrypted
, which is not true since this info is available in objectpropertyex(sp?), and the SQL Compare already appears to ask the question at the beginning of a run - ( if there are no encrypted objects, then don't use the traceflag code route - even if that behavior is turned on ). What you were probably trying to say is that : SQL Compare has no way to find the encrypted objects within the pages on disk, so takes the approach of reading from every page in a manner which requires the use of the Trace flag. Are you telling me that's absolutely necessary? I'm no expert on internals but it seems to me that with 2005 and 2008 there was a lot more disk if not page level info given. Even if it wasn't, I doubt that the 'name' ( however it works at that level ) is encrypted. Couldn't you find some way to identify only the objects whose pages need to take the trace flag route in your code? Again, if you're asking me why this is important, see my original post, then try running SQL Compare against an instance of Microsoft Dynamics Great Plains. Once with and once without decryption set to on. And if you're gonna tell that joke "so the doctor says, Don't move your arm that way and it won't hurt", please see my third question. One of my tasks is to take snapshots of the db schema every so often and if there is no answer to my third question, it means I am damned to extremely long scripting times. Confused

Question Is it possible for SQL Compare to write a message to the SQL Log / Windows App Event log saying something like "Hi, I'm such and such app and I'm doing a comparison, sorry for filling up the logs with the following messages [x, y]. Go here to learn more http://..."

Question Is the statement regarding snapshots saying that for every snapshot, the behavior "Decrypt encrypted objects on 2005 and 2008" is set to true, and that this can not be changed? If the behavior can be changed, would you please specify?
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 922
Location: Red Gate Software

PostPosted: Thu Apr 22, 2010 6:25 pm    Post subject: Reply with quote

Sorry for the delay in replying back to this forum post.

I can confirm there is a bug in SQL Compare. For some reason SQL Compare is generating a TRACEON/TRACEOFF pair for every page it reads.

TRACEON should only be issued once, for all the pages fetched.

Therefore I have submitted a bug report to the development team. The reference for this bug report is SC-4655. I will be maintaining what we call a "watch" on the bug report submitted, which means I will be notified on any updates to the bug report submitted.

I will post back into this forum topic once I have some further information.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
brosato



Joined: 03 Oct 2008
Posts: 73

PostPosted: Fri Sep 10, 2010 4:28 pm    Post subject: Reply with quote

Just thought I'd bump this up. I try really hard to keep the noise down in my log files. The thousands of DBCC TRACEON 3604 are a bit annoying.
Back to top
View user's profile Send private message
tgibbo



Joined: 30 May 2011
Posts: 3

PostPosted: Mon May 30, 2011 6:30 am    Post subject: Reply with quote

Any further developments on this issue?

I have been ckecking for replies on and off since finding this issue a year ago (mainly when someone has run a compare and I have to troll through the 1000's of errors created).

Anything?

cheers,

Tony.
Back to top
View user's profile Send private message
abean



Joined: 30 Oct 2012
Posts: 4

PostPosted: Fri Apr 12, 2013 12:35 am    Post subject: Reply with quote

+1, please find an alternative, or at least configure to turn it off if no encryption exists.
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