Distributed transaction does not work with SQL Server 2005 on Windows Server 2003
I'm having problems trying to put triggers working between SQL Server 2005 connected by linked servers.
I'm using two SqlServer 2005 both running on Windows Server 2003 Standard Edition SP1.
Both doesn't belongs to a Domain. They are configured in they own workgroup.
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
MSDTC version 5.2.3790.1830
What I checked on both servers:
- Service "Remote Procedure Call" is running using the account "NT AUTHORITY\NetworkService";
- On Component Services -> Computer -> My Computer Properties -> tab MSDTC -> Security Configuration all box are checked. On the Transaction Manager Communication the chosen is "No Authentication is Required".
- On Local Computer Policy -> Administrative Templates -> System -> Remote Procedure Call the "Restriction for Unauthenticated RPC clients" is enabled and the restriction to apply is "None".
- Windows Firewall is disabled
- Registry key is set to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOffRpcSecurity is set to "1"
Schema on server [myServer2]:
USE [myDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[myTable](
[code] [varchar](5) NOT NULL,
[name] [varchar](10) NULL,
PRIMARY KEY CLUSTERED
(
[code] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SQLStatement run on server [myServer1]
begin distributed transaction
insert into [myServer2].myDB.dbo.myTable values ('ACD','LX')
commit transaction
The error message:
OLE DB provider "SQLNCLI" for linked server "myServer2" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "myServer2" was unable to begin a distributed transaction.
Some one can help me find the problem?
Thank you in advance
Joao
