“Unable to enlist in the transaction” with Oracle linked server from MS SQL Server

[Camillelola]

We have a setup where we use MS SQL Server with a linked Oracle server. Our database has views on the Oracle database. We have in-house written software (I'm the lead developer) which uses this setup.
 

We've had this setup for 5 years and it's working fine in production, on a beta server and all our development boxes, except mine.
 

I recently rebuilt my machine using Windows 10 (I'm the first using Windows 10 for this). I'm using the Oracle Database 12c Release 1 (12.1.0.2.0) client (we previously used 11g, but Windows 10 appears to require 12c).
 

When I try to perform an update against a view on the Oracle server, within a transaction, it fails. This works fine on any of our other servers, so I'm sure it's either a Windows 10 issue, Oracle 12c client issue, or I've somehow messed up the setup.
 

I've done all the setups in the past (production, beta, and all developer machine setups), so I have quite a bit of experience doing this over the years. The problem is only with a transaction. Non-transactional queries and updates work just fine. Here's an example that will fail.

begin tran
SET XACT_ABORT ON;
update CLIENT set SUPERVISOR_FLAG = 'Y' where CLIENT_CODE = 'XYZ123'
commit tran

The error I get is:

OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER" returned message "Unable to enlist in the transaction.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER" was unable to begin a distributed transaction.

Multiple SQL Servers link to the same back-end Oracle server and they have no problem with this, so I can rule out a configuration issue on the Oracle server.

These are my DTC settings and these are the same as other machines that are working:

[Image attachment]

 

For my Oracle Provider for Ole DB (OraOLEDB.Oracle) settings, I have "Dynamic parameters", "Nested queries", "Allow inprocess" and "Supports 'Like' operator" enabled. This is also the same as other machines.

Under Server Options the settings are:

• Collation Compatible - False
• Data Access - True
• RPC- True
• RPC Out - True
• Use Remote Collation - True
• Collation name - [blank]
• Connection Timeout - 0
• Query Timeout - 0
• Distributor - False
• Publisher - False
• Subscriber - False
• Lazy Schema Validation - False
• Enable Promotion of Distributed Transactions - True

Again, these match the settings on other working servers.

I'm stumped. I'd really appreciate any help anyone can offer on this.

 

Thanks & Regards

Camillelola

[Morgan MLGman]

-- Moved to the Servers subforum --

[leadeater]

Probably not the best forum to ask this sort of question, there are dedicated/more focused SQL forums out there.

[Jarsky]

Have you allowed port 135 and the DCOM ports in your firewall for MS DTC traffic?

You might also want to try the DTCping tool: https://blogs.msdn.microsoft.com/distributedservices/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/