SQLCLR: How to add assembly Npgsql.dll to database?

Question

I'd like to execute PostgreSQL commands from SQCLR procedure. When I am adding Mono.Security.dll I receive error:

Msg 6218, Level 16, State 2, Line 33
CREATE ASSEMBLY for assembly 'Mono.Security' failed because assembly 'Mono.Security' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000001C][found address of Int32][expected unmanaged pointer] Unexpected type on the stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000039][found address of Int32][expected unmanaged pointer] Unexpected type on the stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000059][found address of Int32][expected unmanaged pointer] Unexpected type on the stack.

Is it possible to use Npgsql in SQLCLR procedures?

Answer 1

First, I will answer the question as stated. But second, it is most likely far better to just add a Linked Server to PostgreSQL, and I address that after dealing with the SQLCLR error.

---

SQLCLR info

If this is going to work at all via SQLCLR, the quickest / easiest way is to:

• ALTER the database to be TRUSTWORTHY ON
• Include WITH PERMISSION_SET = UNSAFE in your CREATE ASSEMBLY query
• If any referenced assemblies are in the same folder, they will get uploaded automatically. So maybe try starting with loading Npgsql.dll?

Now, this working requires that all of the assemblies that you are loading into SQL Server be "pure" MSIL assemblies. If any are "mixed" (containing both MSIL and native C++), then they cannot be loaded and you will have to find some other solution, such as writing a console app that you can call via xp_cmdshell or something else.

If the above does work, then an even better method that does not require setting your database to TRUSTWORTHY ON is to create an Asymmetric Key from the private key in those assemblies (assuming that they are strongly named).

---

Non-SQLCLR info

ALL OF THAT BEING SAID: if at all possible, create a Linked Server to PostgreSQL and then make Linked Server calls in regular T-SQL and in queries submitted via SQLCLR (since SQLCLR doesn't execute SQL, it just passes it to SQL Server like any other client software). This would avoid some potential problems you might run into using UNSAFE assemblies. Here are two resources that deal with setting this up:

• SQL Server and PostgreSQL Linked Server Configuration - Part 2

• SQL Server 2012: Add a linked server to PostgreSQL

---

UPDATE:

If the version of SQL Server being used is 2012 or newer, then there might actually be a slight change in behavior in the verification process that is called when executing CREATE ASSEMBLY. Looking at the exact error message, we can see that the source of the problem is in Mono.Math.BigInteger+Kernel::Multiply. Since the Mono project is open source, we should be able to look at the source code. I found the source file, Mono.Security/Mono.Math/BigInteger.cs on GitHub, and the Multiply method of the Kernel class is found at line 2097 with a signature of:

public static unsafe void Multiply (uint [] x, uint xOffset, uint xLen, uint [] y,
                                 uint yOffset, uint yLen, uint [] d, uint dOffset)

Whether or not SQL Server should be complaining about this is the subject of the following thread that talks about a very similar issue (also getting the "Unexpected type on stack" error) relating to the Oracle driver:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4e6a5407-7daa-417d-a7ab-2522dc4ee927/sqlclr-create-assembly-validation-in-sql-2012-vs-2008?forum=sqlnetfx

The suggestion there is to use System.Data.OleDB with an appropriate OLEDB provider. I found the following two options from the Software Catalogue - Drivers and interfaces page on the official PostgreSQL site:

• PGNP: there does not appear to be a free version of it, but they do have a trial version.
• PgOleDb: free, but appears to not have been updated since 2006-04-17.

Another option might be to use ODBC via System.Data.Odbc and an ODBC provider. I found the following two options from that same PostgreSQL.com page:

• psqlODBC: the official PostgreSQL ODBC Driver. As of 2015-08-16, the most recent update to this driver was on 2014-10-26.

• ODBC Driver for PostgreSQL: there does not appear to be a free version of it, but they do have a 30-day trial version.

Both the OLEDB and ODBC options should work in both SQLCLR as well as Linked Servers.