Linked server 2000 to 2005

[schusser]

Hi,

I'm being asked to set up a linked server where there are both SQL Server 2000 and 2005. I've been successful with just 2000 servers but cannot see the 2005 servers.

Will this need to be done on the 2005 server in order to work? The 2000 server will be upgraded in a few months to 2005 but we need easier access before then.

Any guidance would be appreciated.

Thanks,
schusser

 

[JayKusch]

Correct ... your 2000 instances cannot see the 2005 instances BUT 05 can see 02 (backwards compatibility).

Thanks

J. Kusch

 

[schusser]

Thanks JayKusch,

I think I'm convinced that the way to go with this is to set up the linked server from the 2005 server to the 2000 server. It should also be an easier change once the 2000 server is upgraded.

The next glitch is that the 2005 server uses windows authentication and the 2000 server is Sql Server authentication. Any thoughts on the best way to handle this? Or should we bite the bullet and use windows authentication on the 2000 server, too? Might more changes and testing now, better positioning for later?

Thanks again,

schusser

 

[JayKusch]

I am from the side of "using NT Authentication" over SQL authentication if possible.

There are many feature benifits when using NT.

Though you may run into a 3rd party app or two that has yet to mature to NT authentication and still uses SQL.

TEST TEST TEST ... is you best measure as you have indicated.

Good Luck!

Thanks

J. Kusch

 

[mrdenny]

You can get your SQL 2005 instances to talk to SQL 2000 instances, it just takes a couple of tweaks.

This thread talks about most of them.

Is your SQL 2005 system x64 or x86?

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

 

[mrdenny]

I supose the link to the thread might come in handy.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1

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

 

[NuJoizey]

a while ago, I myself was in 2000>2005 and 2005>2000 linked server hell. Here is one of my threads that i dug up for you with

some discussion links that might help.

The good news is that I was able to make it work, so that probably means anyone can! - This was a while ago, and I kind of

forgot most of how I did it, b/c one they're set up, you kind of forget about them, but see if this does anything for you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1969124&SiteID=1