So I have a SQL server database that was controlled by an outside vendor. It is hosted remotely. Then he needed to move the server to a new host. Now, I'm able to connect to the server manually from SSMS and I can add it as a linked server, but I can't USE it as a linked server, or even browse the servers catalogs.
My instance is 2000, and I assume the remote server is 2008 or 2008 R2.
When I try to use the server (try to update a stored proc that points to the linked server) I get the following error:
Trying to browse the linked server's catalogs from SSMS throws this:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. (Microsoft SQL Server, Error: 17)
A friend suggested I run Exec sys.sp_change_users_login 'Report'
, but it turns up no orphaned records, and anyway, I can login using the credentials, so that doesn't look like the problem.
EDIT: Can't connect at all with IP, but hostname connects then gives above error. HUH?
"Then he needed to move the server to a new host."
-- was the instance moved (as in a failover cluster, or virtualization) or was the database moved to a different server? Please clarify. – Jon Seigel Sep 12 '12 at 17:04