Hi,

Is it possible to get transaction support between two ms-sql servers (ms-sql
8.0) on windows 2003 servers, on different nettworks with firewall between -
and only one way traffic on specific ports allowed?

Thanks, Willy

Willy S wrote:
Communication between servers is a two-way process. Out of the box most
firewalls will allow this without opening any port, as long as the
transaction initiator is inside the firewall. However the network
administrator might specifically block all trafic on ports other than
80; this is a matter of security policy, not technology. In which case
no SQL communication will work.

If the transaction initiator is outside the firewall then a rule must
be created to allow incoming traffic, and if the firewall is a proxy as
well then some NAT must be done. This is a very unlikely scenario in
most companies. If you go this way, then at least make sure your
firewall rules are allowing incoming traffic only from trusted IPs, and
think about doing some port forwarding because there is a huge amount
of network scanners out there tuned to probe the SQL Server ports.

In any of these scenarios of course you must enable the TCP/IP protocol
and you must know on which port the service is running (default is
1433). If you have named instances and you want to connect with the
name, you must also enable traffic on port 1434.

"lucm (AT) iqato (DOT) com" wrote:
To make this work we found that tcp port 1433 must be opened out (ok so far).
But then port 135 must be opened in both directions,
and altso some ports for RPC (wich ports can be set) - for example 5000:5100
in both directions.

This is not excepted by our security demands.
Is there a way to make DTS work with ports opened only out?
(With Start transaction, Commit or Rollback).

- WS

And yes, the transaction initiator is inside the firewall.

Without transaction support (database operations without
transactions/rollback) it works with only port 1433 opened out.

But DTS seems to not work wothout ports opened both directions.

(the former post is empty for some reason..?)

And yes, the transaction initiator is inside the firewall.

Without DTS, only database operations without commit/rollback only port 1433
needs to be open out.
But we need transaction handling (start, commit/rollback).

- WS

Willy S wrote:
Have a look here:
http://support.microsoft.com/default...b;en-us;287932

If you use DTC or something else for transactions then you might need
RPC. However if you use transactions inside SQL statements you just
need the plain old 1433 port.

Also use the IP and port in the connection string instead of the server
name (remember to use a comma to define the port, like
"192.168.1.1,1433") and disable the named pipes library.

Ahh, I forgot to tell that this is about linked servers (using
sp_addlinkedserver to add the server to sysservers).

Running this statement:

SET xact_abort ON
GO
USE pubs
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM "LINKEDSERVER".pubs.dbo.authors
COMMIT TRAN
GO

Gives this error if not all the desctibed ports is opened:

The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].