In our SQL Server 2022 we have a linked server connection to an Oracle 11.2 server. The connection is used to perform some very basic select and update statements, which are all inside stored procedures. It works fine in SSMS, but when we try to use it from our software (C#, .NET 4.8), we get one of the following errors:
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "TEST1" was unable to begin a distributed transaction.
or
Unable to enlist in the transaction.
The connection is made using the same user/password as in SSMS. We have tried:
- set transaction isolation level read uncommitted;
- setting up the linked server via ODBC and OleDB -> no difference
- turning every availabe setting for the linked server/provider on/off
- different versions of the Oracle client software
- fiddling around with MSDTC
We do not need transactions. If a statement isn't executed in Oracle sometimes, it does not matter.
So, what is the real difference between SSMS and MSOLEDBSQL?
EDIT:
Some additional info:
- our software runs as a Windows service (maybe a permissions issue?)
- the stored procedures are inside SQL Server, not Oracle