Deserialization in a CLR Function fails

Question

I have written some routines for the phonetic conversion of a text in C#. These routines require a bunch aof defined rules for the conversion (search-string/replace-string). The idea was to store thes rules as an embedded ressource within the assembly and then read the rules from it. The starting point for the the deserialization is as follows

public static phonet42n.Core.Rules Deserialize(phonet42n.Core.Rules.Ressources ressource)
{
    string ressourceName;
    phonet42n.Core.Rules returnValue;
    System.Xml.XmlReader reader;
    System.Xml.Serialization.XmlSerializer xmlSerializer;
    phonet42n.Core.SerializableRules serializeableRules;

    returnValue = new phonet42n.Core.Rules();

    switch (ressource)
    {
        case Ressources.German_01:
            ressourceName = RESSOURCE_XML_GERMAN_01;
            break;
        case Ressources.German_02:
            ressourceName = RESSOURCE_XML_GERMAN_02;
            break;
        default:
            ressourceName = RESSOURCE_XML_GERMAN_01;
            break;
    }

    using (Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(ressourceName))
    {
        using (reader = System.Xml.XmlReader.Create(stream))
        {
            xmlSerializer = new System.Xml.Serialization.XmlSerializer(typeof(phonet42n.Core.SerializableRules));
            serializeableRules = (phonet42n.Core.SerializableRules)xmlSerializer.Deserialize(reader);
        }
    }

    foreach (phonet42n.Core.Rule entry in serializeableRules.Rules)
    {
        if (entry.SearchString != null && entry.SearchString.Length > 0)
        {
            returnValue.Add(entry.Index, entry);
        }
    }
    return returnValue;
}

The application works fine when executed in an regular executable.

When executing the registered function in SQL Server I get the following error:

SELECT [dbo].[Phonet42n]('mayer', 1)

produces...

Meldung 6522, Ebene 16, Status 1, Zeile 22
.NET Framework-Fehler beim Ausführen der benutzerdefinierten Routine oder des benutzerdefinierten Aggregats 'Phonet42n':
System.InvalidOperationException: Fehler im XML-Dokument (3,4). ---> System.MethodAccessException: Fehler beim Versuch der Methode "Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read8_SerializableRules(Boolean, Boolean)", auf Methode "phonet42n.Core.Rule..ctor()" zuzugreifen.
System.MethodAccessException:
bei System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
bei System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
bei System.Activator.CreateInstance(Type type, Boolean nonPublic)
bei System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
bei System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
bei System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
bei Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read8_SerializableRules(Boolean isNullable, Boolean checkType)
bei Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read19_Rules()
System.InvalidOperationException:
bei System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
bei System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader)
bei phonet42n.Core.Rules.Deserialize(Ressources ressource)
bei phonet42n.Core.Rules..ctor(Ressources ressource, Characters characters)
bei phonet42n.Core.HashTable..ctor(Ressources ressource)
bei phonet42n.Core.Match..ctor(Re...

Any idea?

Answer 1

Whenever there are security exceptions, you can first try setting the Assembly to PERMISSION_SET = EXTERNAL_ACCESS, and if that doesn't work you can try UNSAFE. However, if one is attempting to dynamically load an Assembly, then that is supposed to be forbidden even for Assemblies marked as UNSAFE.

Since the issue here is of wanting to include a set of rules, that could probably be done in another Assembly. Then the main Assembly can reference the one containing the rules, and you just load the one containing the rules into SQL Server first. This would allow both Assemblies to remain marked as SAFE.

Of course, if there is no pressing need to keep the rules separate, then you could also just place them directly into a Collection in a Class.

Answer 2

I did an undo on evreything except the constructor part (private/public). With or without signing the assembly I get the following message (10327):

CREATE ASSEMBLY for assembly 'phonet42n.Core' failed because assembly 'phonet42n.Core' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

Unfortunately I'm not familiar with signing an assembly: I just checked the checkbox in the property pages of the project properties:

Don't know whether additional steps are missing.

Anyhow, as suggested in the message I then tried to create an asymmetric key using the following statements:

USE master; 
GO  

IF EXISTS (SELECT * FROM [sys].[syslogins] WHERE [sid] = SUSER_SID('SQLCLRTestLogin'))
   BEGIN
      PRINT 'Dropping Login...'
      DROP LOGIN [SQLCLRTestLogin]
      PRINT 'End'
   END

GO
IF EXISTS (SELECT * FROM [sys].[asymmetric_keys] WHERE [name] = 'SQLCLRTestKey')
   BEGIN
      PRINT 'Dropping Asymmetric Key...'
      DROP ASYMMETRIC KEY [SQLCLRTestKey]
      PRINT 'End'
   END
GO

BEGIN
   PRINT 'Creating Login...'
   CREATE ASYMMETRIC KEY [SQLCLRTestKey] FROM EXECUTABLE FILE = 'D:\phonet42n.net\Core\bin\Debug\phonet42n.Core.dll' 
   PRINT 'End'

   PRINT 'Creating Asymmetric Key...'
   CREATE LOGIN [SQLCLRTestLogin] FROM ASYMMETRIC KEY [SQLCLRTestKey]   
   PRINT 'End'

   PRINT 'Granting Access...'
   GRANT EXTERNAL ACCESS ASSEMBLY TO [SQLCLRTestLogin]; 
   PRINT 'End'
END
GO 

In case of a signed assembly these statements succeed, whereas in case of an unsigned assembly I get the following Error message (15208):

The certificate, asymmetric key, or private key file does not exist or has invalid format.

By the way.. don't know whether this matters. The target .NET Framework aof the assemly is 4.5. Finally... coming to the initial error message: Adwaenyth was right with the missing public default construtor. The message was so far misleading, as the missing constructor does not affect the registrion of the assembly. You get the message only after a successfull registration at runtime, when making a call for the function phonet42n.

I really don't know, whether this is the solution by design. And I cannot not tell whether I opend the door with that for any potential threads.

I just can emphasize the the Stairway series articles especially on on SQLCLR. Excellent! It helps to get a understanding how SQLCLR works and what it is good for.

And (really) finally: the overall outcome for me is, that I will extract the rules from the XML and put load them directly by populating a collection as suggested by srutzky. Will be probably much more performant.