I'm now migrating my SQL Server 2000 to a large server running Windows Server 2003 5.2., and the fix doesn't seem to work. If I run code in QA as below, it returns the error message above:
select Field1,Field2, dbo.fn_findpt(Code,Groupid)as Func_Field
where Tcode is not null
and Team ='Alpha'
dbo.fn_findpt is a function which pulls data from an Oracle database using MSDAORA - and has worked fine for ages on the old Windows 2000 server.
I have also tried the process below kindly sent to me by Balmukund Lakhani – my comments follow:
Looks like you are getting hit by MSDTC default security settings for Windows 2003.
Anyway, lets try below.
a. Click Start, and then click Run.
b. In the Run dialog box, type dcomcnfg.exe, and then click OK.
c. In the Component Services window, expand Component Services, expand Computers, and then expand My Computer.
d. Right-click My Computer, and then click Properties.
e. In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab.
f. In the Security Configuration dialog box, click to select the Network DTC Access check box.
g. To allow the distributed transaction to run on this computer from a remote computer, click to select the Allow Inbound check box.
h. To allow the distributed transaction to run on a remote computer from this computer, click to select the Allow Outbound check box.
i. Under the Transaction Manager Communication group, click to select the No Authentication Required option.
j. In the Security Configuration dialog box, click OK.
k. In the My Computer Properties dialog box, click OK.
I went through your outlined procedure. All was checked as you described:
'Allow Inbound' checked
'Allow Outbound' checked
('Allow Remote Clients' checked??
'Allow Remote Administration' checked??
not sure about the above 2)
However, I found that the 'No Authentication Required Option' wasn't selected so that is now selected.
It prompted to restart MsTDC, which I did.
Unfortunately, when I run code in QA, I get the same message.
So the situation remains:
I can link to the Oracle Server fine on the new Windows 2003 server. It's just distributed tranactions which I can't do.
If anybody has a cure for this, I'd be really grateful – I’m getting pretty desperate.