sp_OACreate returning (0x‭80131051‬) SQL 2016 x64, assembly targeting x64

I know there are tons of answers regarding issues with 32/64 bit conflicts, but in this scenario everything is lined up to run in a 64 bit process.

I am helping a customer upgrade from SQL Server 2008 R2 x64 to a new instance on SQL Server 2016 x64. The customer has some old procedures (hundreds) that use a sequence of sp_OACreate/Method/Destroy to excirsise our products COM interfaces coded in C# and exposed to COM and targets x64. Registered using the x64 version of RegAsm and is strong named and loaded in the GAC.

In my environment I have SQL 2008 R2 runnign and this all works as expected. I installed SQL 2016 and I repro the issue.

(I understand that it would be best to use a different approach however they are resistant to changing things as there are hundreds of places this is used.)

I also tested creating the object in a VB script and this works as expected. is able to run a VBScript in a 64-bit process and call CreateObject.

EXEC @hr = sp_OACreate 'Satori64.CASSTask', @TaskObject OUT, 4
EXEC sp_OAGetErrorInfo @TaskObject, @ErrorSource out , @ErrorDescription out

ErrorSource = "ODSOLE Extended Procedure"
ErrorDescription = "Class not registered"
hr = "-2147221164" (‭80040154‬)

EXEC @hr = sp_OACreate 'Satori64.CASSTask', @TaskObject OUT, 1
EXEC sp_OAGetErrorInfo @TaskObject, @ErrorSource out , @ErrorDescription out

ErrorSource = "ODSOLE Extended Procedure"
ErrorDescription = ""
hr = "-2146234287" (‭80131051‬)

After doing more research and reviewing the procmon logs I see that the way SQL Server looks up a COM object is very different from the way vbscript finds and loads it.

Thanks to some comments I dove deeper and reviewed procmon logs to see what might be happening. Between SQL 2008 R2 and 2016 the file and registry access is similar. the 2008 log shows that the Assembly is laded and the expected object is found and created. The 2016 log show that it is able to find the assembly but then promptly fails unexpectedly. While this helped it didn't answer what was going wrong.

So, I looked deeper to see if I could find why the assembly is not loading.

I added:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion]
"LogFailures"=dword:00000001
"ForceLog"=dword:00000001
"LogPath"="C:\\Temp\\Fusion\\"

and reviewed the logs for the two versions:

Successful SQL R2 2008

*** Assembly Binder Log Entry  (11/18/2019 @ 10:06:54 PM) ***

The operation was successful.
Bind result: hr = 0x0. The operation completed successfully.

Assembly manager loaded from:  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\clr.dll
Running under executable  C:\Program Files\Microsoft SQL Server 2008\MSSQL10_50.MSSQL2008\MSSQL\Binn\sqlservr.exe
--- A detailed error log follows. 

=== Pre-bind state information ===
LOG: DisplayName = Satori64, Version=4.7.1.1, Culture=neutral, PublicKeyToken=ac6bbd3b7853cfdf
 (Fully-specified)
LOG: Appbase = file:///C:/Program Files/Microsoft SQL Server 2008/MSSQL10_50.MSSQL2008/MSSQL/Binn/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = sqlservr.exe
Calling assembly : (Unknown).
===
LOG: This bind starts in default load context.
LOG: Download of application configuration file was attempted from file:///C:/Program Files/Microsoft SQL Server 2008/MSSQL10_50.MSSQL2008/MSSQL/Binn/sqlservr.exe.config.
LOG: Configuration file C:\Program Files\Microsoft SQL Server 2008\MSSQL10_50.MSSQL2008\MSSQL\Binn\sqlservr.exe.config does not exist.
LOG: No application configuration file found.
LOG: Using host configuration file: 
LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config.
LOG: Post-policy reference: Satori64, Version=4.7.1.1, Culture=neutral, PublicKeyToken=ac6bbd3b7853cfdf
LOG: Found assembly by looking in the GAC.
LOG: Binding succeeds. Returns assembly from C:\windows\Microsoft.Net\assembly\GAC_64\Satori64\v4.0_4.7.1.1__ac6bbd3b7853cfdf\Satori64.dll.
LOG: Assembly is loaded in default load context.

Unsuccessful SQL 2016

*** Assembly Binder Log Entry  (11/18/2019 @ 10:08:37 PM) ***

The operation failed.
Bind result: hr = 0x80070002. The system cannot find the file specified.

Assembly manager loaded from:  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\clr.dll
Running under executable  C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER\MSSQL\Binn\sqlservr.exe
--- A detailed error log follows. 

=== Pre-bind state information ===
LOG: DisplayName = Satori64, Version=4.7.1.1, Culture=neutral, PublicKeyToken=ac6bbd3b7853cfdf
 (Fully-specified)
LOG: Appbase = file:///C:/Program Files/Microsoft SQL Server/MSSQL13.SQLSERVER/MSSQL/Binn/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = sqlservr.exe
Calling assembly : (Unknown).
===
LOG: This bind starts in default load context.
LOG: Using application configuration file: C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER\MSSQL\Binn\sqlservr.exe.Config
LOG: Using host configuration file: 
LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config.
LOG: Post-policy reference: Satori64, Version=4.7.1.1, Culture=neutral, PublicKeyToken=ac6bbd3b7853cfdf
LOG: Fusion is hosted. Check host about this assembly.
LOG: Assembly is not in CLR Loaded list. Asking host assembly store.
LOG: Try host assembly store with assembly satori64, version=4.7.1.1, culture=neutral, publickeytoken=ac6bbd3b7853cfdf, processorarchitecture=amd64.
LOG: Try host assembly store with assembly satori64, version=4.7.1.1, culture=neutral, publickeytoken=ac6bbd3b7853cfdf, processorarchitecture=msil.
LOG: Try host assembly store with assembly satori64, version=4.7.1.1, culture=neutral, publickeytoken=ac6bbd3b7853cfdf.
WRN: Host assembly store does not contain this assembly.
ERR: Unrecoverable error occurred during pre-download check (hr = 0x80070002).

Here I am at a bit of a loss. SQL could load the assembly, but for some reason not when accessing it through COM.

I decided to start from scratch and create a new assembly, with the absolute minimum references, a single class with a single method. I strongly named it, registered it with RegAsm (x64) loaded it to the GAC.

This shows the exact same results. sp_OACreate works in 2008 R2 with no problem. Fails in 2014, and 2016.

Any ideas will be greatly appreciated!