Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: 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.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Nov 2003
    Posts
    14
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •