PostgreSQL & SQL Server – LOADLIBRARY Call for XA Resource Manager DLL Failed

linked-servermsdtcodbcpostgresqlsql server

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\psqlODBC\0905\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…

Best Answer

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!

Related Question