MSDTC Doesn't seem to work

[Database Administrator]

Hi,
I have a 2 node SQL 2000 Cluster
When I run the dtctester.exe I run into the following error :
================================================== ===
DSN: dtctest
User Name: ******
Password: ******
tablename= #dtc24358
Creating Temp Table for Testing: #dtc24358
Warning: No Columns in Result Set From Executing: 'create table #dtc24358
(ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server
Driver]Distributed transaction error'
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid
cursor state
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
================================================== ====
Any help is greatly appreciated. Thanks a Lot.

Regards,
Vick

[Database Administrator]

Just a quick note on it... I'm running the dtctester.exe from a different
box... So I give the sql ip address and user/passwd..
"Vick Shaw" wrote in message
news:elTzA1GUHHA.868@TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a 2 node SQL 2000 Cluster
> When I run the dtctester.exe I run into the following error :
> ================================================== ===
> DSN: dtctest
> User Name: ******
> Password: ******
> tablename= #dtc24358
> Creating Temp Table for Testing: #dtc24358
> Warning: No Columns in Result Set From Executing: 'create table #dtc24358
> (ival int)'
> Initializing DTC
> Beginning DTC Transaction
> Enlisting Connection in Transaction
> Error:
> SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server
> Driver]Distributed transaction error'
> Error:
> SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
Driver]Invalid
> cursor state
> Typical Errors in DTC Output When
> a. Firewall Has Ports Closed
> -OR-
> b. Bad WINS/DNS entries
> -OR-
> c. Misconfigured network
> -OR-
> d. Misconfigured SQL Server machine that has multiple netcards.
> Aborting DTC Transaction
> Releasing DTC Interface Pointers
> Successfully Released pTransaction Pointer.
> ================================================== ====
> Any help is greatly appreciated. Thanks a Lot.
>
> Regards,
> Vick
>
>

[Database Administrator]

You need to properly configure DTC for Cluster and Network Connections:

http://support.microsoft.com/kb/817064/

http://support.microsoft.com/kb/301600

If you are running Win2K3 SP1, you will need to reset the DTC security
parameters:

http://support.microsoft.com/kb/899191

Since you are beginning the transaction on a remote host, that host will
also need to properly configure its DTC services.

Finally, if the communication must transit any firewalls, then you will need
to restrict RPC ports on both Client and Server for Internet Ports, and then
authorize these Ports in the firewall ACLs.

http://support.microsoft.com/kb/300083

http://support.microsoft.com/kb/250367/EN-US/

Because we authorize much of the Remote Administration utilities outbound
with an outgoing trust from our secured network, we typically configure this
restricted range to 5000–5100.

Good luck.


Anthony Thomas


--

"Vick Shaw" wrote in message
news:Oo9Vr2GUHHA.4188@TK2MSFTNGP06.phx.gbl...
> Just a quick note on it... I'm running the dtctester.exe from a different
> box... So I give the sql ip address and user/passwd..
> "Vick Shaw" wrote in message
> news:elTzA1GUHHA.868@TK2MSFTNGP05.phx.gbl...
> > Hi,
> > I have a 2 node SQL 2000 Cluster
> > When I run the dtctester.exe I run into the following error :
> > ================================================== ===
> > DSN: dtctest
> > User Name: ******
> > Password: ******
> > tablename= #dtc24358
> > Creating Temp Table for Testing: #dtc24358
> > Warning: No Columns in Result Set From Executing: 'create table
#dtc24358
> > (ival int)'
> > Initializing DTC
> > Beginning DTC Transaction
> > Enlisting Connection in Transaction
> > Error:
> > SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server
> > Driver]Distributed transaction error'
> > Error:
> > SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
> Driver]Invalid
> > cursor state
> > Typical Errors in DTC Output When
> > a. Firewall Has Ports Closed
> > -OR-
> > b. Bad WINS/DNS entries
> > -OR-
> > c. Misconfigured network
> > -OR-
> > d. Misconfigured SQL Server machine that has multiple netcards.
> > Aborting DTC Transaction
> > Releasing DTC Interface Pointers
> > Successfully Released pTransaction Pointer.
> > ================================================== ====
> > Any help is greatly appreciated. Thanks a Lot.
> >
> > Regards,
> > Vick
> >
> >
>
>

[Database Administrator]

Thanks Anthony,
After reading the KB I realize that I installed MSDTC manually on both the
cluster node by Add/Remove Widows Compenet. If I uninstall the DTC the same
way and reinstall thru the Cluster Admin it should work right ??

Thanks again..
Vick


"Anthony Thomas" wrote in message
news:u1EiXhQUHHA.4632@TK2MSFTNGP04.phx.gbl...
> You need to properly configure DTC for Cluster and Network Connections:
>
> http://support.microsoft.com/kb/817064/
>
> http://support.microsoft.com/kb/301600
>
> If you are running Win2K3 SP1, you will need to reset the DTC security
> parameters:
>
> http://support.microsoft.com/kb/899191
>
> Since you are beginning the transaction on a remote host, that host will
> also need to properly configure its DTC services.
>
> Finally, if the communication must transit any firewalls, then you will
need
> to restrict RPC ports on both Client and Server for Internet Ports, and
then
> authorize these Ports in the firewall ACLs.
>
> http://support.microsoft.com/kb/300083
>
> http://support.microsoft.com/kb/250367/EN-US/
>
> Because we authorize much of the Remote Administration utilities outbound
> with an outgoing trust from our secured network, we typically configure
this
> restricted range to 5000-5100.
>
> Good luck.
>
>
> Anthony Thomas
>
>
> --
>
> "Vick Shaw" wrote in message
> news:Oo9Vr2GUHHA.4188@TK2MSFTNGP06.phx.gbl...
> > Just a quick note on it... I'm running the dtctester.exe from a
different
> > box... So I give the sql ip address and user/passwd..
> > "Vick Shaw" wrote in message
> > news:elTzA1GUHHA.868@TK2MSFTNGP05.phx.gbl...
> > > Hi,
> > > I have a 2 node SQL 2000 Cluster
> > > When I run the dtctester.exe I run into the following error :
> > > ================================================== ===
> > > DSN: dtctest
> > > User Name: ******
> > > Password: ******
> > > tablename= #dtc24358
> > > Creating Temp Table for Testing: #dtc24358
> > > Warning: No Columns in Result Set From Executing: 'create table
> #dtc24358
> > > (ival int)'
> > > Initializing DTC
> > > Beginning DTC Transaction
> > > Enlisting Connection in Transaction
> > > Error:
> > > SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL
Server
> > > Driver]Distributed transaction error'
> > > Error:
> > > SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
> > Driver]Invalid
> > > cursor state
> > > Typical Errors in DTC Output When
> > > a. Firewall Has Ports Closed
> > > -OR-
> > > b. Bad WINS/DNS entries
> > > -OR-
> > > c. Misconfigured network
> > > -OR-
> > > d. Misconfigured SQL Server machine that has multiple netcards.
> > > Aborting DTC Transaction
> > > Releasing DTC Interface Pointers
> > > Successfully Released pTransaction Pointer.
> > > ================================================== ====
> > > Any help is greatly appreciated. Thanks a Lot.
> > >
> > > Regards,
> > > Vick
> > >
> > >
> >
> >
>
>

[Database Administrator]

I appologize for breaking up the response in two mails.
I enabled DTC access on both the nodes using add/remove Programs but I added
the MSDTC as a cluster resource thru the cluster Admin console.... I've also
applied the required Registry settings for MSDTC (security) but still I'm
not able to get it working. Alos there are no firewalls ...

Thanks again..
Vick.

"Vick Shaw" wrote in message
news:OklJjJRUHHA.4796@TK2MSFTNGP05.phx.gbl...
> Thanks Anthony,
> After reading the KB I realize that I installed MSDTC manually on both the
> cluster node by Add/Remove Widows Compenet. If I uninstall the DTC the
same
> way and reinstall thru the Cluster Admin it should work right ??
>
> Thanks again..
> Vick
>
>
> "Anthony Thomas" wrote in message
> news:u1EiXhQUHHA.4632@TK2MSFTNGP04.phx.gbl...
> > You need to properly configure DTC for Cluster and Network Connections:
> >
> > http://support.microsoft.com/kb/817064/
> >
> > http://support.microsoft.com/kb/301600
> >
> > If you are running Win2K3 SP1, you will need to reset the DTC security
> > parameters:
> >
> > http://support.microsoft.com/kb/899191
> >
> > Since you are beginning the transaction on a remote host, that host will
> > also need to properly configure its DTC services.
> >
> > Finally, if the communication must transit any firewalls, then you will
> need
> > to restrict RPC ports on both Client and Server for Internet Ports, and
> then
> > authorize these Ports in the firewall ACLs.
> >
> > http://support.microsoft.com/kb/300083
> >
> > http://support.microsoft.com/kb/250367/EN-US/
> >
> > Because we authorize much of the Remote Administration utilities
outbound
> > with an outgoing trust from our secured network, we typically configure
> this
> > restricted range to 5000-5100.
> >
> > Good luck.
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Vick Shaw" wrote in message
> > news:Oo9Vr2GUHHA.4188@TK2MSFTNGP06.phx.gbl...
> > > Just a quick note on it... I'm running the dtctester.exe from a
> different
> > > box... So I give the sql ip address and user/passwd..
> > > "Vick Shaw" wrote in message
> > > news:elTzA1GUHHA.868@TK2MSFTNGP05.phx.gbl...
> > > > Hi,
> > > > I have a 2 node SQL 2000 Cluster
> > > > When I run the dtctester.exe I run into the following error :
> > > > ================================================== ===
> > > > DSN: dtctest
> > > > User Name: ******
> > > > Password: ******
> > > > tablename= #dtc24358
> > > > Creating Temp Table for Testing: #dtc24358
> > > > Warning: No Columns in Result Set From Executing: 'create table
> > #dtc24358
> > > > (ival int)'
> > > > Initializing DTC
> > > > Beginning DTC Transaction
> > > > Enlisting Connection in Transaction
> > > > Error:
> > > > SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL
> Server
> > > > Driver]Distributed transaction error'
> > > > Error:
> > > > SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
> > > Driver]Invalid
> > > > cursor state
> > > > Typical Errors in DTC Output When
> > > > a. Firewall Has Ports Closed
> > > > -OR-
> > > > b. Bad WINS/DNS entries
> > > > -OR-
> > > > c. Misconfigured network
> > > > -OR-
> > > > d. Misconfigured SQL Server machine that has multiple netcards.
> > > > Aborting DTC Transaction
> > > > Releasing DTC Interface Pointers
> > > > Successfully Released pTransaction Pointer.
> > > > ================================================== ====
> > > > Any help is greatly appreciated. Thanks a Lot.
> > > >
> > > > Regards,
> > > > Vick
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Database Administrator]

After you follow the instructions just like the KB recommends, and assigned
the security like the other KB recommends, and you've assigned the DTC a
dedicated resource group, you have to restart SQL Server for it to acquire a
handle.

Can we see a copy of your most recent SQL Server Error Log?

Also, you followed the instructions on the client system as well?


Anthony Thomas


--

"Vick Shaw" wrote in message
news:uOy4yORUHHA.600@TK2MSFTNGP05.phx.gbl...
> I appologize for breaking up the response in two mails.
> I enabled DTC access on both the nodes using add/remove Programs but I
added
> the MSDTC as a cluster resource thru the cluster Admin console.... I've
also
> applied the required Registry settings for MSDTC (security) but still I'm
> not able to get it working. Alos there are no firewalls ...
>
> Thanks again..
> Vick.
>
> "Vick Shaw" wrote in message
> news:OklJjJRUHHA.4796@TK2MSFTNGP05.phx.gbl...
> > Thanks Anthony,
> > After reading the KB I realize that I installed MSDTC manually on both
the
> > cluster node by Add/Remove Widows Compenet. If I uninstall the DTC the
> same
> > way and reinstall thru the Cluster Admin it should work right ??
> >
> > Thanks again..
> > Vick
> >
> >
> > "Anthony Thomas" wrote in message
> > news:u1EiXhQUHHA.4632@TK2MSFTNGP04.phx.gbl...
> > > You need to properly configure DTC for Cluster and Network
Connections:
> > >
> > > http://support.microsoft.com/kb/817064/
> > >
> > > http://support.microsoft.com/kb/301600
> > >
> > > If you are running Win2K3 SP1, you will need to reset the DTC security
> > > parameters:
> > >
> > > http://support.microsoft.com/kb/899191
> > >
> > > Since you are beginning the transaction on a remote host, that host
will
> > > also need to properly configure its DTC services.
> > >
> > > Finally, if the communication must transit any firewalls, then you
will
> > need
> > > to restrict RPC ports on both Client and Server for Internet Ports,
and
> > then
> > > authorize these Ports in the firewall ACLs.
> > >
> > > http://support.microsoft.com/kb/300083
> > >
> > > http://support.microsoft.com/kb/250367/EN-US/
> > >
> > > Because we authorize much of the Remote Administration utilities
> outbound
> > > with an outgoing trust from our secured network, we typically
configure
> > this
> > > restricted range to 5000-5100.
> > >
> > > Good luck.
> > >
> > >
> > > Anthony Thomas
> > >
> > >
> > > --
> > >
> > > "Vick Shaw" wrote in message
> > > news:Oo9Vr2GUHHA.4188@TK2MSFTNGP06.phx.gbl...
> > > > Just a quick note on it... I'm running the dtctester.exe from a
> > different
> > > > box... So I give the sql ip address and user/passwd..
> > > > "Vick Shaw" wrote in message
> > > > news:elTzA1GUHHA.868@TK2MSFTNGP05.phx.gbl...
> > > > > Hi,
> > > > > I have a 2 node SQL 2000 Cluster
> > > > > When I run the dtctester.exe I run into the following error :
> > > > > ================================================== ===
> > > > > DSN: dtctest
> > > > > User Name: ******
> > > > > Password: ******
> > > > > tablename= #dtc24358
> > > > > Creating Temp Table for Testing: #dtc24358
> > > > > Warning: No Columns in Result Set From Executing: 'create table
> > > #dtc24358
> > > > > (ival int)'
> > > > > Initializing DTC
> > > > > Beginning DTC Transaction
> > > > > Enlisting Connection in Transaction
> > > > > Error:
> > > > > SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL
> > Server
> > > > > Driver]Distributed transaction error'
> > > > > Error:
> > > > > SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
> > > > Driver]Invalid
> > > > > cursor state
> > > > > Typical Errors in DTC Output When
> > > > > a. Firewall Has Ports Closed
> > > > > -OR-
> > > > > b. Bad WINS/DNS entries
> > > > > -OR-
> > > > > c. Misconfigured network
> > > > > -OR-
> > > > > d. Misconfigured SQL Server machine that has multiple netcards.
> > > > > Aborting DTC Transaction
> > > > > Releasing DTC Interface Pointers
> > > > > Successfully Released pTransaction Pointer.
> > > > > ================================================== ====
> > > > > Any help is greatly appreciated. Thanks a Lot.
> > > > >
> > > > > Regards,
> > > > > Vick
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Database Administrator]

Thanks Anthony it worked....
Basically the CID on the 2 cluster nodes had the same GUID... replaced the
registry key by creating a new GUID. Also applied the DTC security settings
in the registry.

Thank you
Vick

"Anthony Thomas" wrote in message
news:%23vmW$wcUHHA.1000@TK2MSFTNGP05.phx.gbl...
> After you follow the instructions just like the KB recommends, and
assigned
> the security like the other KB recommends, and you've assigned the DTC a
> dedicated resource group, you have to restart SQL Server for it to acquire
a
> handle.
>
> Can we see a copy of your most recent SQL Server Error Log?
>
> Also, you followed the instructions on the client system as well?
>
>
> Anthony Thomas
>
>
> --
>
> "Vick Shaw" wrote in message
> news:uOy4yORUHHA.600@TK2MSFTNGP05.phx.gbl...
> > I appologize for breaking up the response in two mails.
> > I enabled DTC access on both the nodes using add/remove Programs but I
> added
> > the MSDTC as a cluster resource thru the cluster Admin console.... I've
> also
> > applied the required Registry settings for MSDTC (security) but still
I'm
> > not able to get it working. Alos there are no firewalls ...
> >
> > Thanks again..
> > Vick.
> >
> > "Vick Shaw" wrote in message
> > news:OklJjJRUHHA.4796@TK2MSFTNGP05.phx.gbl...
> > > Thanks Anthony,
> > > After reading the KB I realize that I installed MSDTC manually on both
> the
> > > cluster node by Add/Remove Widows Compenet. If I uninstall the DTC the
> > same
> > > way and reinstall thru the Cluster Admin it should work right ??
> > >
> > > Thanks again..
> > > Vick
> > >
> > >
> > > "Anthony Thomas" wrote in message
> > > news:u1EiXhQUHHA.4632@TK2MSFTNGP04.phx.gbl...
> > > > You need to properly configure DTC for Cluster and Network
> Connections:
> > > >
> > > > http://support.microsoft.com/kb/817064/
> > > >
> > > > http://support.microsoft.com/kb/301600
> > > >
> > > > If you are running Win2K3 SP1, you will need to reset the DTC
security
> > > > parameters:
> > > >
> > > > http://support.microsoft.com/kb/899191
> > > >
> > > > Since you are beginning the transaction on a remote host, that host
> will
> > > > also need to properly configure its DTC services.
> > > >
> > > > Finally, if the communication must transit any firewalls, then you
> will
> > > need
> > > > to restrict RPC ports on both Client and Server for Internet Ports,
> and
> > > then
> > > > authorize these Ports in the firewall ACLs.
> > > >
> > > > http://support.microsoft.com/kb/300083
> > > >
> > > > http://support.microsoft.com/kb/250367/EN-US/
> > > >
> > > > Because we authorize much of the Remote Administration utilities
> > outbound
> > > > with an outgoing trust from our secured network, we typically
> configure
> > > this
> > > > restricted range to 5000-5100.
> > > >
> > > > Good luck.
> > > >
> > > >
> > > > Anthony Thomas
> > > >
> > > >
> > > > --
> > > >
> > > > "Vick Shaw" wrote in message
> > > > news:Oo9Vr2GUHHA.4188@TK2MSFTNGP06.phx.gbl...
> > > > > Just a quick note on it... I'm running the dtctester.exe from a
> > > different
> > > > > box... So I give the sql ip address and user/passwd..
> > > > > "Vick Shaw" wrote in message
> > > > > news:elTzA1GUHHA.868@TK2MSFTNGP05.phx.gbl...
> > > > > > Hi,
> > > > > > I have a 2 node SQL 2000 Cluster
> > > > > > When I run the dtctester.exe I run into the following error :
> > > > > > ================================================== ===
> > > > > > DSN: dtctest
> > > > > > User Name: ******
> > > > > > Password: ******
> > > > > > tablename= #dtc24358
> > > > > > Creating Temp Table for Testing: #dtc24358
> > > > > > Warning: No Columns in Result Set From Executing: 'create table
> > > > #dtc24358
> > > > > > (ival int)'
> > > > > > Initializing DTC
> > > > > > Beginning DTC Transaction
> > > > > > Enlisting Connection in Transaction
> > > > > > Error:
> > > > > > SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC
SQL
> > > Server
> > > > > > Driver]Distributed transaction error'
> > > > > > Error:
> > > > > > SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
> > > > > Driver]Invalid
> > > > > > cursor state
> > > > > > Typical Errors in DTC Output When
> > > > > > a. Firewall Has Ports Closed
> > > > > > -OR-
> > > > > > b. Bad WINS/DNS entries
> > > > > > -OR-
> > > > > > c. Misconfigured network
> > > > > > -OR-
> > > > > > d. Misconfigured SQL Server machine that has multiple netcards.
> > > > > > Aborting DTC Transaction
> > > > > > Releasing DTC Interface Pointers
> > > > > > Successfully Released pTransaction Pointer.
> > > > > > ================================================== ====
> > > > > > Any help is greatly appreciated. Thanks a Lot.
> > > > > >
> > > > > > Regards,
> > > > > > Vick
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[rmarko012]

Hi,
I have wasted half a day's work trying to understand the origin of this problem. Since this forum post ranks pretty high on a googe search for the search string "2147168242 Distributed transaction error", I decided to post here what's helped me solve this problem:

"Warning: the CID values for both test machines are the same"
http://blog.wadewegner.com/index.php...-are-the-same/

The problem stemmed from two duplicate machines (as a result of a VMware template) - both having the same Security Identifiers (SID's).

In case the URL above will become invalid in the future, this is the process that was required to solve my issue:

1. Use Add Windows Components, and remove Network DTC.
2. Go to the command line and run: MSDTC -uninstall
3. Go to the registry and delete the MSDTC keys in HKLM/Software/Microsoft/Software/MSDTC, HKLM/System/CurrentControlSet/Services/MSDTC, and HKEY_CLASSES_ROOT\CID (if they’re still there).
4. Reboot
5. Go to the command line and run: MSDTC -install
6. Use Add Windows Components, and add Network DTC.
7. Go to the command line and run: net start msdtc
8. Go to services.msc and change Distributed Transactions Coordinator's startup method to Automatic.