The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)

Question

I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which point I get an error like this on the SQL Server machine, and the query utterly fails:

> The XA Transaction Manager attempted to load the XA resource manager
> DLL. The call to LOADLIBRARY for the XA resource manager DLL failed:
> DLL=C:\Program Files\psqlODBC0905円\bin\pgxalib.dll, HR=%3, File=%2
> Line=%3.%0

The DLL is in fact that location, so the registry seems to be pointing to the right file. The ODBC driver is 64bit and so is my OS. "File=%2" is pointing to something on the d drive, which doesn't make sense to me, since d drive is a DVD. MSDTC is running... what am I missing?

I have toggled Linked Server Properties "Enable Promotion of Distributed Transactions for RPC" to both "True" and "False" and this doesn't change the issue and does not produce a different error.

Otherwise, scouring the Internet has brought me nothing.

Last thing to point out, my query isn't actually doing any updating - it is just pulling data. So I'm not sure why MSDTC get's invoked in the first place...

• Hannah Vernon: Possible duplicate of Security implications disabling promotion of distributed transaction for linked server
• Dina: Unfortunately even when I set promotion to DT for linked server to false - there were still the same error. I have no idea why. DTC runs under network service account and I have given that account permissions to postgres odbc folder. So far no errors, but I'm giving it a day before posting an answer!
• Dina: I seem to have resolved the error above on the SQL server machine, but the web server still logs an intermittent error (tho less frequently now): "Unknown Error Detected System.Data.SqlClient.SqlException (0x80131904): The operation could not be performed because OLE DB provider "MSDASQL" for linked server "[lkname]" was unable to begin a distributed transaction." Not sure if I should start a new question since I seem to have multiple problems here. And I'm resolving them piece at a time.
• Hannah Vernon: The problem at the web server might be related to Kerberos authentication. Ask a separate question, and provide a link to this question if you feel it's relevant to do so. Add as much detail as you can, including instructions to repro the issue.
• Hannah Vernon: You should probably add the T-SQL definition of the linked server to your question here (and the new question if you decide to ask one!)

Answer 1

I fixed the problem by changing folder permissions on the Postgresql ODBC driver folder and giving read/execute access to Network Service. Because MS DTC runs under network service, which I confirmed by looking at the service properties.

That got rid of the error!