ottenm
asked on
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-
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-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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"
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"
ASKER