SQLCLR assembly registration failed (Type load failed)

Question

I'm trying to register SQLCLR assembly in SqlServer

CREATE ASSEMBLY [DatabaseCLR]
FROM 'T:\DatabaseCLR.dll'
WITH PERMISSION_SET = SAFE
GO

but during registration I get error message

Msg 6218, Level 16, State 2, Line 1 CREATE ASSEMBLY for assembly 'DatabaseCLR' failed because assembly 'DatabaseCLR' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : DatabaseCLR.BinaryUtils::HasSetBits][mdToken=0x6000039] Type load failed.
[token 0x02000008] Type load failed.

which is similar to one described in this question. However the situation is a bit different. In my assembly I do not use User-Defined Types.

If I usePERMISSION_SET = UNSAFE the assembly registers successfully. It doesn't seem that I use unsafe code though ("Allow unsafe code" checkbox is not checked in project properties) (or do I?).

The assembly code (simplified) is:

using System;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

namespace DatabaseCLR
{
    public class BinaryUtils
    {
        [SqlFunction(Name = "BinaryHasSetBits", IsDeterministic = true, IsPrecise = true)]
        public static SqlBoolean HasSetBits(SqlBytes data)
        {
            if (data.IsNull)
                return SqlBoolean.Null;

            if (data.Storage != StorageState.Buffer)
                throw new NotSupportedException(string.Format("Storage type {0} is not supported.", data.Storage));

            long
                len = data.Length,
                ulen = len / sizeof(ulong),
                tail = len % sizeof(ulong);

            ByteToUlongConverter conv = new ByteToUlongConverter(data.Buffer);
            for (long i = 0; i < ulen; i++)
                if (conv.ulongs[i] != 0)
                    return SqlBoolean.True;

            for (long i = len - tail; i < len; i++)
                if (data.Buffer[i] != 0)
                    return SqlBoolean.True;

            return SqlBoolean.False;
        }
    }

    [StructLayout(LayoutKind.Explicit)]
    internal struct ByteToUlongConverter
    {
        [FieldOffset(0)]
        public byte[] bytes;

        [FieldOffset(0)]
        public ulong[] ulongs;

        public ByteToUlongConverter(byte[] bytes)
        {
            this.ulongs = null;
            this.bytes = bytes;
        }
    }
}

The assembly provides functions for bitwise operations on binary types. I'm using struct with [StructLayout(LayoutKind.Explicit)] attribute for casting byte[] array to ulong[] array (to speed up processing). I guess that use of StructLayout causes error as in the related question. However it is not on UDT, and I do not see how I can fix it in this case.

Are there any chances to register assembly with PERMISSION_SET = SAFE ?

I do register my sample function as

CREATE FUNCTION dbo.BinaryHasSetBits
(
    @data varbinary(8000)
)
RETURNS BIT
AS EXTERNAL NAME [DatabaseCLR].[DatabaseCLR.BinaryUtils].[HasSetBits]
GO

I'm using x64 editions of

SqlServer 2014 (assembly compiled for .Net 4.0)
SqlServer 2008 (assembly compiled for .Net 2.0)

Do you need the using interopServices? Do you need structLayout and FieldLayout? — Solomon Rutzky, Nov 11 '16 at 08:34
Better tag it, some sqlclr errors are due to some incompatibility between sql server version and .net version. — McNets, Nov 11 '16 at 09:15
First of all - yes, your code us actually unsafe, possibility to violate checkbox is just compiler feature. The second - why you think your solution will be faster than searching in bytes array, something like Array.Exists(data.Buffer,b=>b!=0) — vitalygolub, Nov 11 '16 at 09:16
@vitalygolub, _mscorlib_ is in the [supported](https://msdn.microsoft.com/en-us/library/ms403279.aspx) list, so I'm surprised that I should use `PERMISSION_SET=UNSAFE`. I did tested two approaches under Profiler (treating `byte[]` as `ulong[]` allows data to be processed faster than treating `byte[]` as it is). — i-one, Nov 11 '16 at 10:04
@srutzky, yes, if possible (though it seems that I have to choose between speed and safety in this case). I'm open if there is another safe speeding-up alternative. — i-one, Nov 11 '16 at 10:40
it is unsafe because of System.Runtime.InteropServices; [look, for example, here](https://books.google.fi/books?id=13ayznPwlysC&pg=PA193&lpg=PA193&dq=does+System.Runtime.InteropServices+require+unsafe+permission+for+sql+clr&source=bl&ots=OhMP6pWmGa&sig=ACfU3U22UgYSg0qrxtkkpYjq7shpXVD9sA&hl=en&sa=X&ved=2ahUKEwjO0NyQ6MrlAhUh_CoKHT2rDIYQ6AEwAXoECBgQAQ#v=onepage&q=does%20System.Runtime.InteropServices%20require%20unsafe%20permission%20for%20sql%20clr&f=false). — Oleksandr, Nov 02 '19 at 05:58
@Oleksandr, AFAIR, it is due to use of `LayoutKind.Explicit`, changing it to `LayoutKind.Sequential` allows registering with `PERMISSION_SET = SAFE`. But use of `LayoutKind.Sequential` breaks intended semantics. So, `SAFE` or `Explicit`, not both. — i-one, Nov 02 '19 at 11:26

score 1 · Accepted Answer · answered Nov 07 '19 at 14:48

The error is due to use of LayoutKind.Explicit.

Changing it to LayoutKind.Sequential

[StructLayout(LayoutKind.Sequential)]
internal struct ByteToUlongConverter
{
    ...
}

makes possible registering assembly with PERMISSION_SET = SAFE.

But using LayoutKind.Sequential instead of LayoutKind.Explicit breaks semantics in this case.

So, PERMISSION_SET = SAFE or LayoutKind.Explicit, not both.

SQLCLR assembly registration failed (Type load failed)

1 Answers1