Create assembly with permission_set safe

Question

I'm trying to deploy a .NET assembly to SQL Server 2008 R2 database, but I'm just hitting this error:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6509, Level 16, State 31, Line 1 An error occurred while gathering metadata from assembly 'Assembly' with HRESULT 0x80004005.

Now the tricky part is that I've made sure that the framework version is correct, and I have a separate trivial CLR database project from which I add and test things to see if I can isolate the issue (I can't). I've even written a tool which uses Mono.Cecil and PEVerify to ensure that I adhere to the programming model restrictions that apply to CLR projects in the database. I'm obviously missing something but I don't know how to debug this, why can't SQL Server just tell me why it fails?

Isn't there something I can do to get more verbose output why SQL Server rejects the assembly?

score 1 · Answer 1 · answered Oct 02 '11 at 00:43

1

Can you paste the CREATE DDL statements please? Did you write the CREATE statement yourself? It looks like you may have used the auto-deploy from Visual Studio. Try it manually. Please give details on the assembly, does it access external data?

answered Oct 02 '11 at 00:43

Russell Hart

1,842
13
20

No external data, it's a simple `create assembly [assembly] from 0x... with permission set = safe;` I've figured it out though but I'm still not sure why this is a problem but I know how to work around it. – John Leidegren Oct 02 '11 at 14:49

score 0 · Accepted Answer · answered Oct 02 '11 at 15:09

After two days of debugging this is what I did.

Drawing from experience I used Cecil to rewrite the assembly, cancelling out methods by just replacing all method bodies with simply throw new NotImplementedException ignoring all constructors (initialization is a bit tricky and just can't just kill the method body of a constructor). After doing this the assembly was installed successfully. I didn't know what was the problem but I knew it had something to do with code. This was my assumption all along.

To find the actual method, I used a binary search. I replaced half of the methods in the assembly and if the error was not there I went left, otherwise right as long as I had a problem until I found the smallest set of methods that were responsible for the error. Given that it was more than 2000 methods and I took about 1-2 seconds to test each configuration, it saved a lot of time.

I managed to zero down on a single method rather easily and here it is (from what I could tell of my findings, this had to be in a generic class as well).

class Hashtable<K, V>
{
    IEnumerable<KeyValuePair<K, V>> GetEnumerator()
    {
        var hashtable = new Hashtable();
        return hashtable
            .Cast<DictionaryEntry>()
            .Select(x => new KeyValuePair<K, V>((K)x.Key, (V)x.Value))
            .GetEnumerator();
    }
}

Nothing in this code really results in any IL that's invalid, even though there's plenty of transformations applied by the compiler. If you put this in a database CLR assembly it will reject the assembly with an access denied error with without any further information.

Eventually, I'm gonna file this as a bug with Microsoft, the error message should not be access denied and furthermore this code is actually allowed because it doesn't do anything which isn't allowed under the safe permission set.

Create assembly with permission_set safe

2 Answers2