help creating a "linked server" (.... does not exist or access denied)

Posted on 2009-02-12
Last Modified: 2012-06-27
I'm attempting to create a "linked server" using Enterprise Manager so I can write queries and updates against a remote DB that is also SQL 2000.  I can browse into this remote DB using EM (under "Microsoft SQL Servers" there are two (groups?): "Local SQL Servers" and "Hosted SQL Servers").  The server I'm after is listed under the hosted branch with an ip address like:\REMOTESERVER..

Under Linked Server Properties I listed "\REMOTESERVER" as the name and selected "SQL Server" as the server type.  I have also tried checking "Be made using this security context" on the Security tab using an ids/pwds for the computer ( and for the SQL instance on that computer.

It all seems to set up ok, but when I try to click on Tables or Views, I always get "Error 17: SQL Server does not exist, or access denied."  Is there a way to find out which one is the problem?  If I can just browse to the server/data in EM, should I need an id/pwd for the linked server?  Can I get EM to create the definition?

I also have connection string that works from ASP.NET (same Server as above, with uid/pwd and database spec'd), is there any way to use that to create and accessible linked server?

Thanks for any help-
Question by:ottenm
    LVL 42

    Accepted Solution

    can you register the\REMOTESERVER from your EM by using the same login that you try to use during the linked server setup?


    Author Comment

    I don't know EugeneZ, I didn't set it up.  It's a critical production system, so no deleting it and retrying just to see.  I'm using the id and pwd from the ASP.NET connection string that's used many times per day (it connects from the same machine I'm trying to create the linked server on, to the same machine I'm trying to connect to, just from a .NET app instead of from EM).  I've also used the id/pwd when logging in locally to\REMOTESERVER.

    Author Comment

    A few more deletes and retries and now it is working.  Somewhere in there I called sp_addlinkedsrvlogin, but not sure I had the right params in there when I called it.

    Thanks EugeneZ, you can have the points for giving it a try.  I've never been able to wade through anything other than "Accept as Solution".

    LVL 42

    Expert Comment

    you can try on the sql server where you create linked server :

    create alias for the named instance in the sql server Client Network Utility where you select ip address tcp\ip protocol and port #
    (check your\REMOTESERVER" networt properties port)
    then try set linked server with the alias and user login that has rights on the\REMOTESERVER"
    LVL 42

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now