Problems Registering Oracle.DataAccess as SQLCLR assembly in MS SQL Server 2012

Question

EDIT 3.5 (Meant to put Item 3 below in the last update, but overlooked. Alas...)

Looking at how the assembly registration fails for my problem and looking at what limited info I am able to infer from ProcExplorer traces leads me to make some conclusions on a few things. No solutions, just a few inferences

1. Microsoft wants to, at some point, allow for Framework 2.0 assemblies to be loaded. I make this inference on the basis that if they were exclusively bound to the notion of precluding them, validation could fail with an immediate check on an assembly's framework metadata. The failure would be analagous to to load 4.0 assemblies in 2008R2 - with stern and specific errors to the contrary, saying Thou Shalt Not Do.

2. If I perform an upgrade to a 2008R2 database that contains a 2.0 assembly, the assembly is loaded and functions from it will fire in a SQL 2012 database. So the ability to execute 2.0 based assemblies is very much present. Getting them past the loader is the trick - hence reinforcing my belief that it won't surprise me to discover a patch or SP that suddenly enables 2.0 framework assemblies in the CLR.

3. I believe that, either through deliberate change or bug, some of the validation semantics implied by PERMISSION_ SET = UNSAFE have changed in SQL2012. My experience leads me to believe that prior versions of SQL Server's CLR Verifier performed the equivalent of a PEVERIFY /MD when PERMISSION_SET = UNSAFE is specified (not checking for things like unmanaged pointers), and PEVERIFY /IL when it is not. It appears to me, however, that in SQL 2012, the CLR Verifier performs a PEVERIFY /IL regardless of the UNSAFE permission flag. I would love to find if someone could verify this theory*

EDIT2

After continued research to this problem, I have not yet found a solution short of retrofitting the project to use the now-deprecated System.Data.OracleClient provider Microsoft created some years ago. Also, further research and emails leads me to believe there's at least one or two "battery not included" notices about changes to assembly validation between SQL 2008R2 and SQL 2012 - and this assembly seems to point precisely to that. More than a few blog posts on SQLCLR assembly registration issues have led to assertions that nothing in the validation process changed, yet registering the same assembly between two databases has generated an otherwise inexplicable problem. I cannot find how SQLServer validates an assembly so at the moment, I continue to search for a solution a bit (well, entirely) in the dark...*

There exists a long-standing SQLCLR project within our MS SQL Server database that makes various critical queries to an Oracle database. This project has been working well for about six years now, having been migrated from a 32-bit assembly in SQL 2005 and into a 64-bit assembly for MS SQL Server 2008 R2.

Despite the fact that the MS SQL 2012 Upgrade Advisor pointed out only general issues with SQLCLR migration regarding certain geographic types, I had a sneaking, ugly suspicion this migration might be truly problematic. Sure enough, I've found that migrating this project into SQL Server 2012 is now presenting what I fear is an intractable problem.

When attempting to register this same 64-bit Oracle.DataAccess.dll (2.112.1.0) that has been living now happily in SQL Server 2008R2 (and its ancestors) for some time, the database now advises that the assembly "fails verification". Edit: My understanding has always been that an assembly granted UNSAFE permissions does not go through validation checking. Is this not correct?

An excerpt of the error response follows:

[ : Oracle.DataAccess.Client.OracleDatabase::Startup][mdToken=0x6000021][offset 0x00000048][found unmanaged pointer][expected readonly address of value 'Oracle.DataAccess.Client.OpoConValCtx'] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleDatabase::Startup][mdToken=0x6000021][offset 0x00000080][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleDatabase::Startup][mdToken=0x6000021][offset 0x000000E3][found unmanaged pointer][expected readonly address of value 'Oracle.DataAccess.Client.OpoConValCtx'] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleDatabase::Startup][mdToken=0x6000021][offset 0x0000011B][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleDatabase::Shutdown][mdToken=0x6000023][offset 0x0000003C][found unmanaged pointer][expected readonly address of value 'Oracle.DataAccess.Client.OpoConValCtx'] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleDatabase::Shutdown][mdToken=0x6000023][offset 0x00000073][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleTransaction::Commit][mdToken=0x600002f][offset 0x0000008F][found unmanaged pointer][expected readonly address of value 'Oracle.DataAccess.Client.OpoTxnValCtx'] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleTransaction::Commit][mdToken=0x600002f][offset 0x000000A6][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : Oracle.DataAccess.Client.OracleTransaction::Dispose][mdToken=0x6000034][offset 0x0000001E][found unmanaged pointer][expected Native Int] ...

Realizing that SQLCLR in 2012 now uses .NET 4.0, I thought perhaps version 4.0 of the same DLL might solve the problem. So I downloaded the 64-bit version of ODAC 12.1.0.1, which provided the 4.0 specific version of the library. Yet similar (but not identical) assembly creation/validation failures were seen - particularly regarding "unmanaged pointer types cannot be verified."

I then tried to use managed code versions of Oracle.DataAccess (Oracle.ManagedDataAccess), and that assembly depends upon a secondary assembly that also fails registration due to it not being a "pure" PE format assembly (which subsequent research has led me to believe it has a disallowed mixture of MSIL and assembly). So that, to me, means the managed code version can never be loaded in the SQLCLR.

So I am left, at this point, with questions and few answers. What, exactly, has changed about assembly validation between 2005/2008/2008R2 and 2012 that will now prevent a given assembly from validating? Are there any options or solutions possible to get Oracle.DataAccess to register? Failing that will make getting the project reconfigured/retargeted to .NET 4.0 moot. Eliminating this component from our system would be a monumental headache, so any solutions or suggestions would be greatly appreciated.

Answer 1

Indeed, SQL Server 2012 works with .NET Framework 4.0, and only that. There's no way you can load multiple versions of the CLR in SQL Server. This is by design. SQL Server 2012 does also not allow mixed assemblies to be loaded anymore. What you can do is create a separate (web)service that contains the current .NET 2.0 functionality. Then call methods on that service from a pure .NET 4.0 CLR assembly that you create. I think this is the most likely solution there is to your problem.