Linked Server - SQL 2000 to SQL 2005

[szlamany]

We want to create a linked server from a SQL 2000 box to a SQL 2005 box.

We will have a database on the SQL 2000 box use "views" to a database on the
SQL 2005 box.

The user will connect (with an ACCESS front-end) to the SQL 2000 database.
They use SQL authentication at this time to get to that database.

The SQL 2005 box will only have WINDOWS AUTHENTICATION. Every user already
has WINDOWS GROUP rights to the DATABASE on the SQL 2005 box.

Will this work? Any suggestions on setup?

Right now we tried creating a linked server on SQL 2000 to SQL 2005 and
using this query:

SELECT * FROM FPSSQL05.STUFILES.DBO.STUFILES_T

and are getting this error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

[techgnome]

I don't know that a 2000 box can talk to a 2005 box.... I know that 2005 can talk to 2000, but I don't know that it can go the other way.... but then I've never tried to do so either. I have had trouble getting EM to talk to a 2005 instance, which is what makes me think that a 2000SQL instance won't talk to the 2005 one.

-tg

[szlamany]

@tg - thanks for that answer.

I think it might be possible - but the authentication issues are huge (found lots of pages when googling of people with these issues - turn on named pipes - user kerberos - and many more possible solutions).

I think we ultimately convinced the developer to switch to windows-authentication - and this problem goes away - as their DB can come onto the SQL 2005 box now as well.