help creating a "linked server" (.... does not exist or access denied)
Posted on 2009-02-12
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: 188.8.131.52\REMOTESERVER..
Under Linked Server Properties I listed "184.108.40.206\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 (220.127.116.11) 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-