sp_addlinkedserver gives error

[djj55]

Hello, In SQL 2000 I have two servers DB1 and DEVSER\DBTEST. I would like to create a link from the test server to the live server. In Query Analyzer I executed:

EXEC sp_addlinkedserver 'DB1', N'SQL Server'
EXEC sp_addlinkedsrvlogin 'DB1', 'true'

But when I execute:

select * from db1.mydatabase.dbo.mytable

I get

Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

I use Windows security to connect.

Any help would be appreciated.
Thank you,
djj

 

[mrdenny]

You can't use Windows security to connect from an instance on one server to an instance on another server. It's called the NTLM double hop issue. You need to setup the linked server to map your windows account to a SQL account on the production server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[djj55]

Thank you! That was the problem. When setting up the linked server (through Enterprise Manager) I used a SQL rather than Windows account and that worked great.

Thanks again,
djj