Linked server problem

I linked two sql servers (one an x64 sql 2005 server the other a sql 2000 x86 server). The link is from the x64 box to the x86 box and I used Windows authentication to forge the links.

When the developer (who has Windows accounts on both boxes) logs in locally to the x64 box and runs a query, data is returned from the linked server. But when the same developer logs into his own client box and uses Management Studio to access the x64 box (via Windows authentication) and run the same query, he gets this error message"

"Login failed for user '(null)'. Reason: not associated with a trusted SQL Server Connection"

I presume that the developer should be able to access this data from his own box or can the linked data only be gotten from an account logged into the x64 box?

TIA,

barkingdog

I'm a little confused about what you mean by 'windows authentication' to forge the links.

when you create a linked server, you have the following options:

map a local login to a login on the remote server

connect using the user login's security context

connect using a specific (provided) login's security context

Whenever I've seen the above error, it has been because a developer had the server registered incorrectly in EM.

:

|||

Under SSMS, I thought that those options only applied when the connection trying to access the linked server was not listed in the top box ("Local server login to remote server login mappings".). So I ignored those options. Maybe I dun wrong!

The developer who is trying to access data on the linked server from SSMS from his own box is listed (using Windows authentication) in the Local Login section of the Linked server properties box of the x64 server. "Impersonate" is selected for that developer. No names are supplied for the Remote User\Remote password box as his Windows authentication will be used on the linked box. (Or so I think...)

TIA,

barkingdog

|||

I woul dfill in the other two boxes and see if that works.

I am struggling with my own linked server issues right now, related to windows authentication accross linked servers, but we would be using the

'connect using the user's login'.

You might try that and tell me if it works. ;)

|||

Here's what I have so far:

Suppose Server B is linked to Server A via a "Link Account". When I log on to Server B (using Remote Desktop) as "Link Account" I can successfully pull data from Server A, so the link is working. The problem: I logged into my own local workstation (not Server A or Server B) as "LinkAccount" fired up SSMS, connected to Server B as that account, and tried to pull the same data that I did earlier. The attempt fails saying that I am using a "null" account and no sql account exists.....blah, blah, blah.

I found this SQL 2005 BOL link that describes the situation:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9e302c29-639d-4509-ba1e-cf452582c5c3.htm

Apparently, what I wanted to do was not as simple as I thought it should be. I am creating a "double hop" and the servers need to be prepared as described in the help article. Otherwise, the error message is to be expected!