My SQL Server 2008 R2 CLR works sometimes, but not others, but why?

I have a CLR DLL (called clr.dll) which we've previously loaded and used. I'm using it to load a large number of XML files (for which we have no XSD so we can't use the fiddly XML processor in SSIS as this requires an XSD). I'm loading it into a database also called CLR. For info the CLR function returns the XML file as a table by having one row per node. The CLR also has a few test functions

Previously this has worked and now it produces errors. The errors appear to occur intermittently, and occur even if the only thing executing on the server is a call to the CLR. I have a few test functions that use virtually no resource and they also don't work. i.e. it's not a resource problem.

The CLR is loaded with PERMISSION_SET = EXTERNAL_ACCESS. The database is set with TRUSTWORTHY ON. It appears that in SQL Server 2008 R2 we can't load the CLR using hashcode and my dll exceeds the 8000 chars that HASHBYTES supports anyway (suggested by other articles).

Within the CLR the functions belong to a class named UserDefinedFunctions.

This is a cut down version of the script I'm using to setup the CLR. The database has correct permissions. In summary it: (1) drops all functions in the CLR database, (2) drops and reconnects the CLR and (3) creates the functions to link to the assembly and sets the permissions on them appropriately.

USE CLR
GO

-- drop all functions
DECLARE @FunctionName NVARCHAR(400)
DECLARE @SQL NVARCHAR(MAX)

WHILE EXISTS    (SELECT *
                FROM    sysobjects
                WHERE   xtype IN ('FS', 'FT'))
BEGIN
    SET @FunctionName = (SELECT TOP 1 name
                        FROM    sysobjects
                        WHERE   xtype IN ('FS', 'FT'))

    SET @SQL = 'DROP FUNCTION dbo.' + @FunctionName

    EXEC sp_executesql @SQL
END 
GO

-- belt and braces, this should be the case anyway
ALTER DATABASE CLR SET TRUSTWORTHY ON;
GO

-- drops and load the assembly, first time
IF EXISTS   (SELECT *
            FROM    sys.assemblies
            WHERE   name = 'CLR')
    DROP ASSEMBLY CLR
GO

CREATE ASSEMBLY CLR from 'e:\clr\clr.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

-- ======== Scalar-functions ================================================================
CREATE FUNCTION dbo.DateTimeToString(@dt DATETIME, @fmt NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME CLR.UserDefinedFunctions.DateTimeToString;
GO


CREATE FUNCTION dbo.FileExists(@Filename NVARCHAR(MAX)) RETURNS BIT
AS EXTERNAL NAME CLR.UserDefinedFunctions.FileExists;
GO


CREATE FUNCTION dbo.FileGetCreated(@Filename NVARCHAR(MAX)) RETURNS DATETIME
AS EXTERNAL NAME CLR.UserDefinedFunctions.FileGetCreated;
GO


CREATE FUNCTION dbo.FileGetModified(@Filename NVARCHAR(MAX)) RETURNS DATETIME
AS EXTERNAL NAME CLR.UserDefinedFunctions.FileGetModified;
GO


CREATE FUNCTION dbo.FileGetSize(@Filename NVARCHAR(MAX)) RETURNS BIGINT
AS EXTERNAL NAME CLR.UserDefinedFunctions.FileGetSize;
GO


CREATE FUNCTION Reflection(@Data NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) -- test - returns the string passed to it
AS EXTERNAL NAME CLR.UserDefinedFunctions.Reflection;
GO


-- ======== Table-valued functions -- ReadTextFile
CREATE FUNCTION dbo.ReadTextFile(@Filename NVARCHAR(4000))
RETURNS 
TABLE
(
    LineIndex INT,
    Data NVARCHAR(4000)
)
AS
EXTERNAL NAME CLR.UserDefinedFunctions.ReadTextFile
GO


-- ReadXmlDoc
CREATE FUNCTION dbo.ReadXmlDoc(@Filename NVARCHAR(4000))
RETURNS 
TABLE
(
    NodeIndex INT,
    ParentIndex INT,
    DepthIndex INT, 
    ChildCount INT, 
    SiblingIndex INT, 
    SiblingCount INT, 
    IsTerminalNode BIT,
    Tag NVARCHAR(4000),
    IndexPath NVARCHAR(4000),
    SimplePath NVARCHAR(4000), 
    UniquePath NVARCHAR(4000), 
    ExtendedPath NVARCHAR(4000), 
    TextValue NVARCHAR(4000)
)
AS
EXTERNAL NAME CLR.UserDefinedFunctions.ReadXmlDoc
GO

-- SELECT and EXECUTE permissions are set here, but omitted for security reasons

I'm then calling my minimal test function, named Reflection, which simply returns the string passed to it:

SELECT  CLR.dbo.Reflection('hello')

Irrespective of which function I call, or which database I execute in, this error is thrown:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65544. 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. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 'clr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException:

at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

As I say, it's not a resource issue and it's not a permissions issue and it happens "sometimes". Currently, I haven't been able to get it to run this week. Last week reapplying the drop and create scripts resolved the issue.

When I execute this code as suggested by Solomon below:

SELECT  name,
        is_trustworthy_on, 
        is_db_chaining_on, 
        compatibility_level, 
        owner_sid, 
        collation_name 
FROM    sys.databases 
WHERE   name IN (N'CLR', N'Other_DB')

I get:

name      is_trustworthy_on  is_db_chaining_on  compatibility_level  owner_sid                                                   collation_name
Other_DB  0                  0                  100                  0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1C5FB0000  Latin1_General_CI_AS
CLR       1                  0                  100                  0x0105000000000005150000009530FDDAA5F3AE3A5859ABA17C2E0100  Latin1_General_CI_AS

Added 2 Oct 2019. Additionally, query 1:

SELECT  lgn.[name], 
        lgn.[type_desc], 
        lgn.[sid], 
        CASE lgn.[sid] WHEN SUSER_SID() THEN 1 ELSE 0 END AS [IsCurrent] 
FROM    sys.server_principals lgn 
WHERE   lgn.[sid] IN (0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1C5FB0000, 0x0105000000000005150000009530FDDAA5F3AE3A5859ABA17C2E0100, SUSER_SID());

Results 1:

name       type_desc      sid                                                         IsCurrent
GRP\clone  WINDOWS_LOGIN  0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1C5FB0000  0
GRP\mark   WINDOWS_LOGIN  0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1CD790200  1

Query 2:

USE [CLR];
GO

SELECT  DB_NAME() AS [DB], usr.[name], usr.[type_desc], usr.[sid], CASE usr.[sid] WHEN USER_SID() THEN 1 ELSE 0 END AS [IsCurrent]
FROM    sys.database_principals usr 
WHERE   usr.[sid] IN (0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1C5FB0000, 0x0105000000000005150000009530FDDAA5F3AE3A5859ABA17C2E0100, USER_SID()) 
OR      usr.[name] = N'dbo';

Results 2:

DB   name      type_desc     sid                                                         IsCurrent
CLR  dbo       WINDOWS_USER  0x0105000000000005150000009530FDDAA5F3AE3A5859ABA17C2E0100  0
CLR  GRP\mark  WINDOWS_USER  0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1CD790200  1

Query 3:

USE [Other_DB];
GO

SELECT  DB_NAME() AS [DB], 
        usr.[name], 
        usr.[type_desc], 
        usr.[sid], 
        CASE usr.[sid] WHEN USER_SID() THEN 1 ELSE 0 END AS [IsCurrent] 
FROM    sys.database_principals usr 
WHERE   usr.[sid] IN (0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1C5FB0000, 0x0105000000000005150000009530FDDAA5F3AE3A5859ABA17C2E0100, USER_SID()) 
OR      usr.[name] = N'dbo';

Results 3:

DB        name  type_desc     sid                                                         IsCurrent
Other_DB  dbo   WINDOWS_USER  0x0105000000000005150000009530FDDAA5F3AE3A5859ABA1C5FB0000  0

So I have learnt the following:

• the following applies to CLRs that are EXTERNAL_ACCESS or UNSAFE in SQL2008R2.
• when you create the assembly and connect SQL functions to a CLR, it doesn't load the CLR. This makes sense. Why use up memory before you need to.
• when you first execute a call to the CLR, it will check the SID of the database of the query being executed against the SID of the CLR database. These two SIDs may or may not be the same. A primary reason why they may be different is that the database the query is being run from may have been restored from another server. If they don't agree, this error occurs:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65544. 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. For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'clr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException: 
   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)

• however, if you execute a function first in the CLR database, obviously the SID of the CLR database agrees with itself and so it loads the CLR happily.
• solutions exist using module signing, which I don't yet understand, but a short term work-around is to run a function from the CLR database as soon as you've created the assembly and created the functions.
• See here for more info: https://blogs.msdn.microsoft.com/psssql/2010/03/23/unable-to-load-clr-assembly-intermittently/
• See here for info on module signing: https://modulesigning.info/