Extracting Sql: domains

Hi
I'm trying to get distributed transactions working between 2 machines within
2 different windows domains.
I can't get around following error:
Server: Msg 7391, Level 16, State 1, Line 1
OLE DB provider "SQLNCLI" for linked server <our server> returned message
"No transaction is active.".
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server <our server> was unable to begin a distributed transaction.
That the environment:
- server A is Win2k3 + SP 1
- server B is Win2k3 + SP 1 (within other windows domain; connection through
VPN)
- both servers running SQL 2005 + SP1
- Distributed Transaction Coordinator service is enabled on both servers
(security set to: 'No authentication required')
- The services are running with 'Log On As' Network Service
- windows firewall is disabled on both servers
- all ports are opened (bidirectional) between both domains
- @.@.servername is set correctly on both servers
That's what I try from server A:
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
but
begin tran
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
commit tran
When running this query from another ServerC within the same domain of
ServerB everything works fine. So, I guess there's some limitations across
the domains ?!
Do you have any further suggestions ?
Many thanks in advance
MichaelHi Michael
Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
John
"micpauls" wrote:
> Hi
> I'm trying to get distributed transactions working between 2 machines within
> 2 different windows domains.
> I can't get around following error:
>
> Server: Msg 7391, Level 16, State 1, Line 1
> OLE DB provider "SQLNCLI" for linked server <our server> returned message
> "No transaction is active.".
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server <our server> was unable to begin a distributed transaction.
> That the environment:
> - server A is Win2k3 + SP 1
> - server B is Win2k3 + SP 1 (within other windows domain; connection through
> VPN)
> - both servers running SQL 2005 + SP1
> - Distributed Transaction Coordinator service is enabled on both servers
> (security set to: 'No authentication required')
> - The services are running with 'Log On As' Network Service
> - windows firewall is disabled on both servers
> - all ports are opened (bidirectional) between both domains
> - @.@.servername is set correctly on both servers
>
> That's what I try from server A:
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> but
> begin tran
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> commit tran
> When running this query from another ServerC within the same domain of
> ServerB everything works fine. So, I guess there's some limitations across
> the domains ?!
> Do you have any further suggestions ?
>
> Many thanks in advance
> Michael|||Yes, I already tried, but without success. I still get the same error message.
"John Bell" wrote:
> Hi Michael
> Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> John
> "micpauls" wrote:
> > Hi
> >
> > I'm trying to get distributed transactions working between 2 machines within
> > 2 different windows domains.
> >
> > I can't get around following error:
> >
> >
> > Server: Msg 7391, Level 16, State 1, Line 1
> > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > "No transaction is active.".
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server <our server> was unable to begin a distributed transaction.
> >
> > That the environment:
> > - server A is Win2k3 + SP 1
> > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > VPN)
> > - both servers running SQL 2005 + SP1
> > - Distributed Transaction Coordinator service is enabled on both servers
> > (security set to: 'No authentication required')
> > - The services are running with 'Log On As' Network Service
> > - windows firewall is disabled on both servers
> > - all ports are opened (bidirectional) between both domains
> > - @.@.servername is set correctly on both servers
> >
> >
> > That's what I try from server A:
> >
> > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> >
> > but
> >
> > begin tran
> > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > commit tran
> >
> > When running this query from another ServerC within the same domain of
> > ServerB everything works fine. So, I guess there's some limitations across
> > the domains ?!
> >
> > Do you have any further suggestions ?
> >
> >
> > Many thanks in advance
> > Michael|||Hi
Have you checked out http://support.microsoft.com/kb/839279? I would also
try changing the service to be a domain account.
John
"micpauls" wrote:
> Yes, I already tried, but without success. I still get the same error message.
> "John Bell" wrote:
> > Hi Michael
> >
> > Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> >
> > John
> >
> > "micpauls" wrote:
> >
> > > Hi
> > >
> > > I'm trying to get distributed transactions working between 2 machines within
> > > 2 different windows domains.
> > >
> > > I can't get around following error:
> > >
> > >
> > > Server: Msg 7391, Level 16, State 1, Line 1
> > > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > > "No transaction is active.".
> > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > linked server <our server> was unable to begin a distributed transaction.
> > >
> > > That the environment:
> > > - server A is Win2k3 + SP 1
> > > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > > VPN)
> > > - both servers running SQL 2005 + SP1
> > > - Distributed Transaction Coordinator service is enabled on both servers
> > > (security set to: 'No authentication required')
> > > - The services are running with 'Log On As' Network Service
> > > - windows firewall is disabled on both servers
> > > - all ports are opened (bidirectional) between both domains
> > > - @.@.servername is set correctly on both servers
> > >
> > >
> > > That's what I try from server A:
> > >
> > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> > >
> > > but
> > >
> > > begin tran
> > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > > commit tran
> > >
> > > When running this query from another ServerC within the same domain of
> > > ServerB everything works fine. So, I guess there's some limitations across
> > > the domains ?!
> > >
> > > Do you have any further suggestions ?
> > >
> > >
> > > Many thanks in advance
> > > Michael|||Hi
windows firewall is completely disabled on both servers.
Security settings for MSDTC were already set apropriate to
"http://support.microsoft.com/kb/839279?" on both servers.
I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
find this account on serverA because this is a domain controller. How can I
do this ? Should I try changing the account for "Distributed Transaction
Coordinator" service to some other domain admin account ?
Michael
"John Bell" wrote:
> Hi
> Have you checked out http://support.microsoft.com/kb/839279? I would also
> try changing the service to be a domain account.
> John
> "micpauls" wrote:
> > Yes, I already tried, but without success. I still get the same error message.
> >
> > "John Bell" wrote:
> >
> > > Hi Michael
> > >
> > > Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> > >
> > > John
> > >
> > > "micpauls" wrote:
> > >
> > > > Hi
> > > >
> > > > I'm trying to get distributed transactions working between 2 machines within
> > > > 2 different windows domains.
> > > >
> > > > I can't get around following error:
> > > >
> > > >
> > > > Server: Msg 7391, Level 16, State 1, Line 1
> > > > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > > > "No transaction is active.".
> > > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > > linked server <our server> was unable to begin a distributed transaction.
> > > >
> > > > That the environment:
> > > > - server A is Win2k3 + SP 1
> > > > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > > > VPN)
> > > > - both servers running SQL 2005 + SP1
> > > > - Distributed Transaction Coordinator service is enabled on both servers
> > > > (security set to: 'No authentication required')
> > > > - The services are running with 'Log On As' Network Service
> > > > - windows firewall is disabled on both servers
> > > > - all ports are opened (bidirectional) between both domains
> > > > - @.@.servername is set correctly on both servers
> > > >
> > > >
> > > > That's what I try from server A:
> > > >
> > > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> > > >
> > > > but
> > > >
> > > > begin tran
> > > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > > > commit tran
> > > >
> > > > When running this query from another ServerC within the same domain of
> > > > ServerB everything works fine. So, I guess there's some limitations across
> > > > the domains ?!
> > > >
> > > > Do you have any further suggestions ?
> > > >
> > > >
> > > > Many thanks in advance
> > > > Michael|||micpauls wrote:
> Hi
> windows firewall is completely disabled on both servers.
> Security settings for MSDTC were already set apropriate to
> "http://support.microsoft.com/kb/839279?" on both servers.
> I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
> find this account on serverA because this is a domain controller. How can I
> do this ? Should I try changing the account for "Distributed Transaction
> Coordinator" service to some other domain admin account ?
>
You shouldn't run SQL under the NetworkService context. Create a real
domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
use that domain user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, for your help. I already run SQL services using a separate domain
(admin) account. Yesterday the problem has been resolved by adding
appropriate hostname/IP address entries to the 'hosts' file and the 'lmhosts'
file, to enable network transport services to resolve the 2 server hostnames
accross the 2 different domains. Adding linked servers by IP address wasn't
sufficient. RPC in/out options within the linked servers properties must by
enabled.
"Tracy McKibben" wrote:
> micpauls wrote:
> > Hi
> >
> > windows firewall is completely disabled on both servers.
> > Security settings for MSDTC were already set apropriate to
> > "http://support.microsoft.com/kb/839279?" on both servers.
> >
> > I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
> > find this account on serverA because this is a domain controller. How can I
> > do this ? Should I try changing the account for "Distributed Transaction
> > Coordinator" service to some other domain admin account ?
> >
> You shouldn't run SQL under the NetworkService context. Create a real
> domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
> use that domain user.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Tuesday, March 27, 2012

Failed to get distributed transaction startet

Failed to get distributed transaction startet

Extracting Sql

Blog Archive

About Me