DTC Errors 7395 And 1206 Between SQL Server 2005 And SQL Server 2000 Sp4 - Part1

Our software vendors upgraded their software and our environment - and applications do not interface as they ought. The vendors do not appear to have any further ideas (to those listed below) and at this stage neither do I. It is now a week since the *upgrade* went live. Looking for new suggestions....


Full story (epic) below. Object names have been changed to protect the *innocent*.





Our organisation had 2 applications:-

appBank
appHouse

both running on the respective platforms:

Server name svrCAT:
Windows Server 2000 sp 4 / SQL Server 2000

Server name svrDOG:
Windows Server 2000 sp 4 / SQL Server 2000



Transactions were "posted" from svrDOG to svrCAT via a linked SQL Server, using login dbL1nkLogin.

dbL1nkLogin was made a user of (with relevant table permissions) of the respective databases:

dbForBank
dbForHouse



*** At this point all was working ***


Upgrading appBank to appBigBank required SQL Server 2005 (and more diskspace).


A new server was purchased:

Windows Server 2003 R2 sp 2 / SQL Server 2005 (Server name svrHORSE)



A linked server was created from the SQL instance on svrHORSE to svrDOG using login name dbL1nkLogin.
dbL1nkLogin was made a user (with relevant table permissions) on database:

dbForBigBank


Attempts to "post" transactions were "posted" from svrDOG to svrHORSE via the linked SQL Server fail with
the appBank application generating the following errors:

On first attempt:

************* FILE ERROR *************
Error Code...... 3902-
Error Message...The COMMIT TRANSACTION request has no co
.rresponding BEGIN TRANSACTION.
Table Name......** NOT APPLICABLE **
Description.....** NOT APPLICABLE **
Function....... COMMIT
sys991: Program already in use
COBOL error at 000444 in sys500
Called from 00076A in sys990ssv
Called from 000457 in sys991
Called from 01177D in gls489
Called from 000DFA in gls396
Called from 003961 in gls394
Called from 000258 in glp900
Called from 003DC1 in syp250

On subsequent attempts:

************* FILE ERROR *************
Error Code...... 1206-
Error Message...The Microsoft Distributed Transaction Co
.ordinator (MS DTC) has cancell
Table Name......tblHouseFinancials
Description.....DYNAMIC SQL CALL INTERLUDE
Function....... UPDATE
*FAILED* Processing Job 'WIN/'



* Parameters and controls within the appBank application were verified.

* Password on dbL1nkLogin was reset in the SQL instances on svrHORSE and svrDOG

* Compared the link server settings on svrHORSE to svrCAT:

Security tab: Be made using security context - Remote Logging: dbL1nkLogin / password

Server Options: Collation Compatible checked (on svrHORSE only)
Data Access checked
RPC checked
RPC Out checked


* Made dbL1nkLogin dbo on databases dbForBigBank and dbForHouse.
* Changed default database from master to dbForBigBank on svrHORSE
* Rebooted svrHORSE and svrDOG (multiple times)
* Verified that svrHORSE could ping svrDOG, and vice versa
* Using DTCping.exe, Verified that the SQL instance on svrHORSE could ping svrDOG, and vice versa
* On svrHORSE, Administrative Tools -> Component Services -> Computers has the following settings for "My Computer" Network DTC Access:


Network DTC Access - checked
Allow Remote Clients - checked
Allow Remote Administration - checked
Allow Inbound - checked
Allow Outbound - checked
No Authentication required - checked
Enable XA Transactions - checked
DTC Logon Account - NT AuthorityNetworkService

These settings were already set. My understanding of KB329332 suggest they are correct. As I could not establish the configuration date of these settings rebooted the server to ensure they'd taken effect.



* Loaded SQL Server 2000 sp4 on svrDOG (Rebooted afterwards in case services not stopped and started correctly)
srvDOG now has MDAC 2.8 sp1 (2.81.1117.6) while srvHORSE has a later version (2.82.3959)- I assume sp2. While they are not the same version, MDAC 2.8 sp2 KB 231943 advises that 2.8 sp2 is not available via the web - so I think it's the best I can do).


* Stopped and manually started MSDTC and SQL Server (via Administrative Tools - Services) in case MSDTC was not being started first.



* KB873160 suggests opening port 135 and adding msdtc.exe as an exception in the Windows Firewall. At present there is no firewall on svrHORSE - which I would assume negates this necessity (at present). In Administration Tools - Services there was a service for the firewall running. Stopping this service has no effect on this issue.



* ( The svrHORSE machine was not created from an ghosted image ).


** None of these things have resolved the issue. **



.... to be continued...

KD