Hi all,

I've recently upgraded (complete OS and SQL rebuild) a SQL 7
active/active cluster to a Windows 2000 muliple instance cluster.
Everything is fine apart from the fact that i get an error message
when creating a linked server from another SQL 7 server to one of the
nodes in the SQL 2000 cluster.

I have tried through enterprise manager and using sp_addlinkedserver.
I am using the Microsoft OLE BD Provider for SQL server and connecting
using the SA login. The SA login and password are the same on both
boxes.

In both cases the server appears in enterprise manager but I get the
message "ERROR 2: Access Denied" when I double click on tables or
views.

I have no problem setting up a linked server from the SQL 2000 Node to
the SQL 7 node.

Anyone got any ideas? I had a full head of hair yesterday and about
30% remains..

Any help would be much appreciated.
Thanks,
Matt

It sounds like the SQL Server startup account for the SQL Server 7.0
instance does not have permission to the node that the SQL Server 2000
instance is running on.
If you login using that start up account can you access the node that the
SQL Server 2000 instance is running on?

Rand
This posting is provided "as is" with no warranties and confers no rights.

Update to the latest MDAC (2.8?)on your SQL 7 installation. You are most
likely running a non-instance-aware version of MDAC. Clustered SQL looks
like an instance to a client. SQL 7 does not include instance-awareness in
the client libraries.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com




"Matt James" <matt.marks (AT) haynet (DOT) com> wrote

Don't run MDAC v 2.6 or later on a SQL 7 cluster server. Your cluster will
stop working as it is not compatible.

http://msdn.microsoft.com/library/de...dataaccess.asp

--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike (AT) NOSPAMepprecht (DOT) net

Specialist SQL Server Solutions and Consulting

"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

Good catch. I missed that it was a SQL 7.0 Cluster.

Doh!.

"Mike Epprecht [SQL Server MVP]" <mike (AT) NOSPAMepprecht (DOT) net> wrote