Issue with MSSQL 2017 CLR referencing IBM.MQ (amqmdnetstd.dll)

Question

question

jdparker asked Sep 30, '20

Issue with MSSQL 2017 CLR referencing IBM.MQ (amqmdnetstd.dll)

All,

I've been going at this for a couple of days now and hoping someone out here has experienced this and knows how to solve it. I am migrating a SQL Server 2008 R2 database running on Windows Server '08 to a new SQL 2017 Enterprise installation running on Windows Server '16. In my current database, I have a CLR I developed many years ago to facilitate coms with IBM MQ servers. This CLR uses the amqmdnet assembly from IBM. I went the CLR route because most of my applications that that would need to use MQ to get or put messages are web based and SQL Server gave me a clean platform to facilitate those interactions (websites were already heavily interacting with the database.) This CLR has worked great and given me no issues over the years.

Fast forward to now. The original assembly, amqmdnet, seems to be incompatible with SQL Server 2017 which is fine... it is depreciated anyway. The new assembly is amqmdnetstd.dll and is built on netstandard v2. I have been able to successfully rebuild my CLR assembly with the new MQ assembly and required netstandard.dll and .NET assemblies. I loaded the latest .net assemblies (4.8) and ensured that netstandard was version 2. My test CLR is a bare bones MQ PUT function to limit possible fail points to a minimum. I can run this exact code in a console application with no issues, and I can run it on a website hosted on the same server with no issues. Its only on the SQL Server installation that it fails.

If I remove any try/catch on the CLR, I get the following stack trace:

Msg 6522, Level 16, State 1, Procedure sp_clr_MQ_SimplePut, Line 0 [Batch Start Line 25]
A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_clr_MQ_SimplePut": 
System.TypeInitializationException: The type initializer for 'IBM.WMQ.MQQueueManager' threw an exception. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: The path is not of a legal form.
System.ArgumentException: 
   at System.IO.Path.LegacyNormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
   at System.IO.Path.InternalGetDirectoryName(String path)
   at IBM.WMQ.ManagedCommonServices..ctor()
System.Reflection.TargetInvocationException: 
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at IBM.WMQ.CommonServices.CreateCommonServices()
   at IBM.WMQ.CommonServices.TraceConstructor(String objectId, String sccsid)
   at IBM.WMQ.Nmqi.NmqiEnvironment..ctor(NmqiPropertyHandler nmqiPropertyHandler)
	...
System.TypeInitializationException: 
   at IBM.WMQ.MQQueueManager..ct...

Here is the full CLR with only the connection details redacted:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using IBM.WMQ;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void sp_MQ_SimplePut (SqlString strMessage)
    {
        //Conection Details
        string  strHostName     = "xxxxxxxx.xxx.xxx";
        int     intPortNumber   = xxxx;
        string  strChannel      = "xxxxxx.xxxxxxx";
        string  strQueueManager = "xxxxxxxxx";
        string  strQueueName    = "xxxxxxxxxxxx";

        //Create Queue Manager Object
        MQQueueManager mqQMgr = null;   // MQQueueManager instance
        //Create Connection Properties
        Hashtable mqProperties = new Hashtable
        {
             { MQC.TRANSPORT_PROPERTY, MQC.TRANSPORT_MQSERIES_MANAGED }
            ,{ MQC.HOST_NAME_PROPERTY, strHostName }
            ,{ MQC.PORT_PROPERTY, intPortNumber }
            ,{ MQC.CHANNEL_PROPERTY, strChannel }
        };

        //Create Queue Manager Object
        mqQMgr = new MQQueueManager(strQueueManager, mqProperties);  //<-- This is where the exception is thrown

        //Create Queue Opening Options
        const int openOptions = MQC.MQOO_OUTPUT + MQC.MQOO_FAIL_IF_QUIESCING;

        using (MQQueue mqTargetQueue = mqQMgr.AccessQueue(strQueueName, openOptions))
        {
            // Define a WebSphere MQ message, writing some text in UTF format
            var msg = new MQMessage
            {
                CharacterSet = 1208
            };

            msg.WriteString(strMessage.ToString());

            // Specify the message options
            var pmo = new MQPutMessageOptions(); // accept the defaults

            // Put the message on the queue
           mqTargetQueue.Put(msg, pmo);

        }

        //Close Queue Connection
        if (mqQMgr != null)
            mqQMgr.Disconnect();

    }
}

My troubleshooting steps so far:

I can run this exact code with no issues on both console app and asp.net site (ASP.NET site running on same server as the MSSQL installation.) Code doesn't appear to be the issue.
When I load import this CLR assembly into the dababase, I also load the exact same assembly files referenced when I built the CLR.
I created a new, clean database for this, so no carry over from a restored DB.
This database is set as TRUSTWORTHY so the CLR Strict Security feature isn't going to cause any issues, plus I've tested this with Strict Security turned off with the same results.
I have installed the latest .NET runtime (4.8) on the server itself.
I have installed IBM.WMQ Client on the server.
I have explicitly registered the amqmdnetstd.dll and netstandard.dll assemblies in the GAC.

If I put a try/catch in it gives me a less detailed exception which I've put below:

Exception.Message:
The type initializer for 'IBM.WMQ.MQQueueManager' threw an exception.

Exception.StackTrace:
   at IBM.WMQ.MQQueueManager..ctor(String queueManagerName, Hashtable properties)
   at StoredProcedures.sp_MQ_SimplePut(SqlString strMessage)

Exception.Source:
amqmdnetstd

Exception.GetType:
System.TypeInitializationException

So that's where I am. Everything points back to this being something with how SQL Server 2017 is interacting with this particular assembly. Hoping its just a ID10T error and someone on here says "Oh yeah, you just forgot to do _____!" One can hope, right?

Anyone encounter this before? Any suggestions?

Thanks!

John

sql-server sql server 2017 clr sql-server-2017

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-09-30T23:04:25Z

srutzky answered Sep 30, '20

Somehow IBM.WMQ.MQQueueManager, via a constructor for IBM.WMQ.ManagedCommonServices, is looking for a file, or doing something file system-related, though it's not clear why (maybe a custom config file, or a path for temp files for the messages?). In either case, it's doing something that falls outside of the many rules for legacy path handling (you can see the rules here: https://referencesource.microsoft.com/#mscorlib/system/io/path.cs,509 ; I assume this specific error can come from anywhere using `throw new ArgumentException(Environment.GetResourceString("Arg_PathIllegal"));` ).

For now, I'm going to assume that LegacyNormalizePath is the key here (at least we might be able to do something about that, unlike any of the IBM code). So, try the following:

Edit the C:\Program Files\Microsoft SQL Server\MSSQL14.{instance_name}\MSSQL\Binn\sqlservr.exe.config file and add the following to the <runtime> element:

<AppContextSwitchOverrides value="Switch.System.IO.UseLegacyPathHandling=false;" />

Then, restart the instance (just to be sure that the config change is picked up).

If that doesn't help then we will likely need more insight into what exactly this code is attempting to do on the file system. Since this is a .NET DLL, it should be possible to decompile it using ILSpy to see what IBM.WMQ.ManagedCommonServices is doing. HOWEVER: I do NOT know if you are legally / license-wise allowed to decompile IBM's code. The answer to that depends on what the license states and what, if any, IP laws that you are governed by might say about such things. I'm not recommending that you violate any potential agreements you might be bound by. But, if you are allowed to do such things, then that might help narrow down the problem without this being a wild goose chase.

11

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jdparker commented · Sep 30, 2020 at 11:26 PM

Solomon, first thank you for the time you took to look at this. It is greatly appreciated. I followed your recommended change and retested and unfortunately still get the same exception. Below is the content of the sqlservr.exe.config file after the added line.

<?xml version ="1.0"?>
<configuration>
    <startup useLegacyV2RuntimeActivationPolicy="true">
        <supportedRuntime version="v4.0" />
    </startup>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
              <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91"/>
              <publisherPolicy apply="no"/>
              <bindingRedirect oldVersion="11.0.0.0-14.0.0.0" newVersion="14.0.0.0"/>
            </dependentAssembly>
        </assemblyBinding>
      <disableCachingBindingFailures enabled="1" />
      <CodeHeapReserveForJumpStubs value="5" />
	  <AppContextSwitchOverrides value="Switch.System.IO.UseLegacyPathHandling=false;" />
    </runtime>
</configuration>

0 ·

jdparker commented · Sep 30, 2020 at 11:28 PM

I did restart the instance, and also dropped and recreated my assemblies and procedures just in case... any ideas for a next step? You're already in deeper water than I can swim in... :-)

0 ·

jdparker jdparker commented · Sep 30, 2020 at 11:40 PM

I will look into the IMB.WMQ.ManagedCommonServices assembly and see if I can see anything of use.

0 ·

jdparker jdparker commented · Oct 01, 2020 at 12:47 AM

So looking in the ManagedCommonServices part of the assembly, I do see code trying to identify the "workpath" by reading system.io.path.getdirectoryname(Assembly.GetExecutingAssembly().Location). I'm not sure what this would return in a SQLCLR or if this would be what is throwing the error? Might create a little dummy CLR that does just that and see what happens...

0 ·

jdparker jdparker commented · Oct 01, 2020 at 12:59 AM

This may be it... just ran this little CLR proceedure...

    public static void sp_TestPath()
    {
        string strOut = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
        SqlContext.Pipe.Send(strOut + Environment.NewLine);
    }

...and got this exception...

Msg 6522, Level 16, State 1, Procedure sp_clr_TestPath, Line 0 [Batch Start Line 32]
A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_clr_TestPath": 
System.ArgumentException: The path is not of a legal form.
System.ArgumentException: 
   at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
   at System.IO.Path.InternalGetDirectoryName(String path)
   at StoredProcedures.sp_TestPath()
<br>

Looks like the Assembly.GetExecutingAssembly().Location method returns an empty string (which makes sense since it's loaded into the database), and the Path.GetDirectoryName() function throws the exception.

Sooooooooo... is this evidence that the IBM assembly in its current form will not work in a CLR? or is there some method we can use to work around it?

0 ·

srutzky jdparker commented · Oct 01, 2020 at 02:20 AM

Hey there. Good research so far. While it's definitely helpful to confirm the exact same error, can you please remove the System.IO.Path.GetDirectoryName() from setting of strOut? I am curious what the raw value is that's being passed into that method. As far as working around this issue goes, most likely the only possibility is using reflection to update the value of Location before that IBM assembly calls that method, OR maybe updating the IBM method to supply the desired value for that path, based on how it is being used. I'm not sure if either can actually be done, or if the latter can be done, if it would even solve the problem due to how that path is being used. Another possibility, maybe, is using COM (which requires UNSAFE but you're already there anyway) to have the Assembly within SQLCLR call a method in a companion assembly (COM visible) sitting on the file system that calls that IBM code (and would have a file system Location). Not the most elegant, nor most performant, of approaches, but might could work.

0 ·

Show more comments

question