Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 trustworthy bit randomly being switched off

Author  Topic 

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-28 : 03:12:26
we run a distributed system involving many database servers. On each server, we have a database called Meta which has no tables, only some helper functions and procs that other procs in other databases call.

In this Meta database, there is an UNSAFE CLR assembly which some of these helpers use. It's unsafe because it needs to read from the registry for certain stuff. For this reason, Meta has its TRUSTWORTHY bit set to on.

So here's the problem. Ever couple of months, the trustworthy bit in Meta on one or another server seems to be getting switched off. This causes errors because the CLR assembly can no longer be loaded.

our ops team thinks this switching may be correlated with creating new databases, but it's not consistent. usually you can create a new database and it's no problem, but every so often you create a new one and the trustworthy bit in Meta gets flipped. Sounds crazy to me that that's what they say.

When the error does happen, the only foolproof method ops has found to fix it is to drop and recreate Meta. when they do that, everything is good again.

It's virtually impossible to debug because it almost always works correctly - very difficult to debug something with a two month failure cycle...

Here's the error we see when it happens:

"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."

Has anyone seen this before? Anyone know what might indirectly be causing the trustworthy bit to flip back off after it's be set on?


elsasoft.org

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-28 : 22:08:42
May need trace that in profiler.
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-28 : 23:16:16
thanks for the suggestion, but that's not really an option. these are production servers that are quite busy - I can't run a profiler trace on all servers for months on end.

I have never been able to repro this on my lab machines - it only seems to happen in prod.






elsasoft.org
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-29 : 12:17:42
How about a DDL trigger?

Since the statement to change TRUSTWORTHY is

ALTER DATABASE MyDB SET TRUSTWORTHY { ON | OFF }



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-29 : 14:32:41
in the error report do you have a
"For more information about this error: ..."
part?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-07-01 : 02:29:52
Peter: I'll suggest to ops implementing a DDL trigger - that should catch it.

Mladen: from the callstacks I've been given I don't see anything like that. In any case I've never found anything useful by following those links anyway. have you?


elsasoft.org
Go to Top of Page
   

- Advertisement -