Problem with Linked server

Question

Problem with Linked server

Oracle_91

SSChampion

Points: 14548
More actions
May 21, 2010 at 12:20 am

#221799

Hi,
Problem with the Linked Server.
On the src server from which the Linked server is fetching/pulling the data, the DTC (distributed Transaction Coordinator) service is running under Network Service service account.
On the src server we have 2 databases db1 and db2 and Linked Server default database after loging is "db1".
Now from target server, when i say
select * from <linkedservername>.<db1>.dbo.<tname> , i am getting the result.
But when am saying the i.e trying to access the "db2" on the same source server
select * from <linkedservername>.<db2>.dbo.<tname> , i am getting below ERROR.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
i checked the Linked server properties
Under Security Tab
Be made using the login's current security context radio button is selected.
Under Server options
-Data Access
-RPC
-RPC Out
-Use Remote Collation
are checked.
Do we need to create another Linked Server?
Do we need to give any permission on db2 to Linked Server?
Any immediate fix???
Thanks in Advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

I have never encountered this problem so far... the first thing I can see different on our servers is that we always have the setting of Use Remote Collation = False. Could you try that? I have no idea whether it will help, but might be worth a try... maybe db1 and db2 have different collations?

Well, and of course you have to check that the login you are using exists in the database and has enough privileges. You might try to change the setting in Security to "using this security context" and specify login+password that you know will work od db2.

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

I was expecting an something like below.

Server: Msg 7314, Level 16, State 1, Line 1 OLE DB provider 'DC' does not contain table '"db2"."dbo"."t1"'. The table either does not exist or the current user does not have permissions on that table.

But not the case.I used using the security context and have provided "sa" as Remote login and supplied the password for "sa". But still am getting the same error.

"sa" can access any database right??? why is it showing the above error??

Oracle_91 SSChampion Points: 14548 More actions · Answer 3

I have explicitly created a user who has db_accessadmin,db_datareader roles on db1 and db2 and supplied that user and pwd of that login in the Linked Server then it worked fine.

Here, am only selecting the data.

My question, why did'nt it worked for "sa"??? I right cclick he properties of "sa" and i can see all the db_owner for all the databases on the remote database server. But why it failled for "sa"??

Thanks in Advance.