Linked SQL Server showing strange behavior

I have two SQL Server Instances on two servers. One server is my webserver and database server and the other one is just a database server. i have an application that calls a stored procedure located on the webserver/database server that runs a query on the OTHER database server. I use linked tables in my first instance to make the call possible.

Everything was working just fine for months until the database server was restarted and the IP address was changed. The name of the database is the same however and my first SQL Server instance has no problems running queries on the other databases tables. However, when you try to run the application i get the following error:

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection

I have mixed mode authentication selected and my security uses the security context with username=sa and password=sa.

So here's the weird part.

The application will only run correctly when i manually run a SQL command from my webserver's SQL Analyzer on the linked SQL Server. however, after a few minutes, the same error comes back!! so as a temporary fix, i scheduled a dts job to run a simple query on the linked server every two minutes, so the application keeps working! It's almost as if the webserver's sql server forgot that the linked server is there, and by running a simple query in query analyer, the connection gets refreshed and everythings normal again - for about 3 minutes!

I am completely stumped by whats happeneing and appreciate any help. Thank you.Are SP levels the same on both servers? Is the service running under a domain account? Is the linked server configured through impersonation or mapping?|||My first guess would be that you have more than one problem.

I suspect that connection caching or connection pooling is what allows the connection to be made as long as you force a periodic connection using the proper credentials. Once the connection exists, it "stays alive" for a while waiting for the next connection that ought to use the same credentials.

I suspect that either a change to the sa password on one or both machines, a domain change, an infrastructure change (possibly router/firewall related), or something similar is what is causing the connections to be refused. It appears that your connection is being made using both SQL and Windoze authentication, but that something isn't quite right. It may have worked in the past, but it doesn't work now... As long as a cached connection still exists, the problems with this connections parameters don't matter, but whenever a new connection needs to be made from scratch, it fails.

-PatP|||Thanks for your help guys. I'll attempt to figure out exactly what happened with the IP address and the network when the computer was restarted. I'll also learn more about connection pooling in SQL Server.

The linked server is configured not through impersonation (the checkbox is blank). i use just a standard security context of user/pass = sa/sa. The local login's are blank.

I tried to enter the domain/user windows domain account in the local login list and hit the impersonate box, however it doesnt let me enter the password and I'm not too sure what "Remote User" and "remote password" means and why I can't type anything in it.

Out of all the Server optoins i have only data access, RPC and RPC out checked. and COnnection timeout and Query timeout is set to 0.

servers and performance

Hi,
I have a client that he needs to configure a linked server
on a server cluster with SQL Server 2000, windows 2000,
two instances: one default and one named instance.
Linked Server should be configured in the named instance
calling a database from default instance
There is any problem about performance using linked server
in that condition?
Thank's for information
I was searching in Microsoft pages and I didn't find
information over that topicsThere are no performance issues specific to that configuration. There is
the general consideration that remote queries use more resources and
encounter greater latency than local queries, so if a high percentage of
requests involve both servers then you could experience poor performance.
There is no specific guidance on when you would see a problem, but absent
real data I always use 80/20 as a rule of thumb. That is, 80% of the
requests should be completely satisfied locally and no more than 20% should
require access to the linked server. I use 90/10 when I need to make a more
conservative estimate.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"ruth G" <caubd@.isa.com.co> wrote in message
news:007301c36801$f96d1f00$a401280a@.phx.gbl...
> Hi,
> I have a client that he needs to configure a linked server
> on a server cluster with SQL Server 2000, windows 2000,
> two instances: one default and one named instance.
> Linked Server should be configured in the named instance
> calling a database from default instance
> There is any problem about performance using linked server
> in that condition?
> Thank's for information
> I was searching in Microsoft pages and I didn't find
> information over that topicssql

servers and Cluster

I am having an issue using linked servers on
active/active cluster. I have two instances, and when
both instances are on the same machine, things work as
expected. When I move one of the instances to the other
machine, it does not. It appears to work correctly when I
link the named instance to the default instance. I am
having issues linking the default instance to the named
instance.
What is the text of the query you are issuing? What error is returned from
the Linked Server query? What are the SQL Server and operating system
versions?
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.