• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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

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: 1.2.3.4\REMOTESERVER..

Under Linked Server Properties I listed "1.2.3.4\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 (1.2.3.4) 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-
0
ottenm
Asked:
ottenm
  • 3
  • 2
1 Solution
 
Eugene ZCommented:
can you register the 1.2.3.4\REMOTESERVER from your EM by using the same login that you try to use during the linked server setup?

0
 
ottenmAuthor Commented:
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 1.2.3.4\REMOTESERVER.
0
 
ottenmAuthor Commented:
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".

Peace!
0
 
Eugene ZCommented:
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 1.2.3.4\REMOTESERVER" networt properties port)
then try set linked server with the alias and user login that has rights on the 1.2.3.4\REMOTESERVER"
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now