Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    35

    Unanswered: Basic Linked Servers Question

    Hi, I just had some questions about Linked Servers.

    I need to access a view on another SQL Server. The DBA of this remote server has given my Domain Account and another Domain Account "Read Only" access to this account. I have registered the Remote SQL Server in SSMS, connected to it and I can run the view on the Remote SQL Server.

    1. Now, to run this view in "my database", I am assuming that I need to create a Link to this Server to be able to do a "SELECT" statement on this view from my Database?

    If I go into SSMS and manually add a link, and then try to do (SELECT * FROM LinkedServer.DB..ViewName), I get there error:

    "SQLNCLI" for linked server "ISPSDB01" returned message "Communication link failure"."

    When I use "sp_addlinkedserver" as noted in this article, I get the errors:

    OLE DB provider "SQLNCLI" for linked server "LinkedServer" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "LinkedServer" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
    My DB and Remote DBQL Server 2005

    Me and the DBA of the Remote SQL Server never discussed Linking Servers, so I don't know if he thought I would access the view another way or that he thought I knew how to do it.

    Heck, if there is another to access the data without linking them, I would be interested in that as well.

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a look at the Surface Area Configuration (SAC) for the SQL instances to check that remove connections are enabled.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The message "Login Timeout Expired" means that your Local Server is using SQL Authentication to connect to the Remote Server. If you only have access using Windows Authentication, this is very unlikely to work.

    Check to see if you have set "Use Self" for authentication to the Remote Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2009
    Posts
    35
    Thanks for the replies.

    The DBA for the Database Server I need to connect too was busy today, so I wasn't able to work on the issue. Last we talked, we thought it could be a firewall issue, although, if it was a firewall issue, I would think it wouldn't even let me register the server.

    However, I have another SQL Server I was practicing on today, to see if I could link to it.

    Question, I was able to Link to this SQL Server using an account that uses mixed sign on. However, when I tried to Link the two server using my Domain account, it keeps telling me the login failed.

    Don't have the exact transact sql as I am at home but I believe it is:

    sp_addlinkedsrvlogin "LinkedServer","false","DOMAIN\username","username ","password"

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Just use the OpenRowSet function, you supply the credentials in the query, no need to setup a linked server.

Posting Permissions

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