Linked Server to cluster fails (sometimes)

Howdy.
Here's the question: why does my linked server function properly only part
of the time and how do I fix it?
Details-
I have a linked server set up in a production environment. The link is to a
database that is on the same server as the linked server, but I want to redi
rect the link to another copy of the database on a *cluster* elsewhere on th
e network. I am trying to t
est this by setting up a link to the production database on a test SQL Serve
r.
The linked server is used by another database-I will call this the "primary"
database. Generally the primary database is accessed via stored procedure c
alls and in some cases these stored procedures invoke stored procedures that
exist within the linked da
tabase. No writes are done through the linked server.
The linked server is set up to use a specified security context that is a re
ad-only SQL user that has appropriate permissions in the pertinent database
on the cluster.
The SQL Server on which I have created the linked server is *not* my develop
ment box. When I use Enterprise Manager running on my development box and cl
ick on "Tables" under the linked server, I get this error: "Error 17: SQL Se
rver does not exist or acce
ss denied." However, when I log directly onto the SQL Server box (e.g., via
Terminal Services) and do the same thing I am able to view the list of table
s in the linked database with no error.
I also have two .NET applications that touch the linked server. These applic
ations invoke the same stored procedures within the primary database which i
n turn remotely invoke the same stored procedures via the linked server. One
of these applications is a
"rich client" application that uses Windows forms controls deployed onto a w
eb page via Fusion technology and the other is a standard web application (A
SP.NET). The rich client application gets its data remotely via calls to a w
eb service while the data
access tier for the web application runs in the same process space as the ap
plication's server side code. The rich client application accesses data via
the linked server correctly while the web application reports the same error
as the one reported when I
try to view the linked tables on my development box via Enterprise Manager (
"SQL Server does not exist or access denied"). I need to get both of these
applications to work.
Can anyone shed any light on this problem?
Thanks in advance for any help anyone can offer.
MattHey Matt,
In addition to what Allan suggests:
If the linked server works when the cluster is failed over to one node and
not the other;
Check to see if one side has an alias created for the remote server.
Use the SQL Client network utility to view this on each node.
Or check the registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
Otherwise, run the test while making a network trace on both the client
machine, and the Cluster.
Verify that it is using the same protocols as a connection made from the
local node of the cluster.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Allan:
Thank you for your help. I will forward your comments to the DBA who is hel
ping me. In addition, I will insert comments below.
Matt
"Allan Hirt" wrote:

> Some things to think about :
> 1. Make sure you are referencing the virtual server
> itself, not the address/IP of any of the individual nodes.
Hmmm. I am referencing the cluster by name, not IP, so I assume that is the
virtual server you mention. I apologize for being unfamiliar with clusters
, but this is my first exposure to them.

> 2. Remember that upon failover, any connection to the
> virtual server will probably be lost and you need to take
> that into account in your application/DB. You should test
> to see what happens in a failover and what it means to
> your app.
Are you talking about failover within the cluster? Do I need to manage conn
ections made via the Linked Server? I'm assuming that, if a failover occurs
either in the middle of a call across the linked server or just before the
call is made, then I will a
t least have an exception to handle. I'm also assuming that once the failov
er occurs functionality will be restored to the linked server since it was s
et up pointing to the virtual server. Is this correct?

> 3. If it is elsewhere on the network, make sure you have
> the routing to be able to get there.
This shouldn't be an issue.

> 4. By your error, going with #3, you either do not have
> access or the routing is possibly weird.
Well, the problem isn't occurring in conjunction with a failover. To my kno
wledge, no failovers have occurred during my testing. There seems to be som
ething different (under the seams) about the way my two applications use the
linked server, and this is
what I'm having trouble identifying.

>
>|||Kevin:
Thank you for your help. As with Allan's reply, I will forward this to the
DBA.
Like I mentioned to Alan, I don't believe the error is tied to a failover.
I will ask the DBA to do the trace as you suggested.
Matt
"Kevin McDonnell [MSFT]" wrote:

> Hey Matt,
> In addition to what Allan suggests:
> If the linked server works when the cluster is failed over to one node and
> not the other;
> Check to see if one side has an alias created for the remote server.
> Use the SQL Client network utility to view this on each node.
> Or check the registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
> Otherwise, run the test while making a network trace on both the client
> machine, and the Cluster.
> Verify that it is using the same protocols as a connection made from the
> local node of the cluster.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Network traces aren't that difficult to do. They can really help solve
some of the communication related problems posted. Best advice is to make
a working trace and compare it to the failing one.
Read this kb, and it will help you understand what the traffic should look
like.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.