SQL Server Performance Forum – Threads Archive

Linked Server issue

Hello – Environment –
SQL server 2000
I tried to link two servers but it doesn’t work.
Process i followed is as follows
There are two servers
Server1 (Primary)
Server2 (Linked)
domainall users (user group) In Enterprise manager i have registered both these servers with "sa" login. the password for both the "sa" logins are different. Server A
Under Secirity tab, right click on Linked Servers to create new linked server.
Wrote server2 in linked server text box.
selected SQL server option.
under security tab
local login – domainall users
impersonate – checked
selected " Be made using the login’s current security context" option
clicked OK In query analyser
selected Server1 and windows authentication ( i am a part of Domainall users group) fired query on table1 as
select * From server2.database1.dbo.table1
i am getting the below error
"Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’." if i write local log in sa and remote log in sa and if i give server2’s sa password then it works fine. but in windows authentication mode it doesn’t work. What might be the reason. and as "sa" passwords for both the servers are different. so if i don’t put sa in seurity section then it gives
"Login failed for user ‘sa’."
thanks,
Mandar

---

I think this is a identified bug with SQL Server. Try using OPENQUERY for querying the data. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

This error indicates that you are attempting to access the linked server by using Windows NT authentication to impersonate the client connecting.
To resolve this use sp_addlinkedsrvlogin to map the clients to a standard security login.
Refer to BOL for above SP and more information. Refer to this similar thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1179 and get the fix. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>