1

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?

Improve this question
5
  • The error is a connection issue, not an authentication issue. How did you create the linked server? Did you use a hostname or an IP address? If you used an IP address you'll need to recreate the linked server. – squillman Sep 12 '12 at 14:30
  • @squillman -The answer is yes. I've created the linked server through TSQL and through SSMS GUI. I've used IP and hostmane. I've tried many variations and have dropped and recreated the linked server a dozen times. – MAW74656 Sep 12 '12 at 14:33
  • @squillman - See recent edit. – MAW74656 Sep 12 '12 at 14:55
  • "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
  • @JonSeigel - I believe it was just moved to another server, nothing fancy. I assume he made a backup and restored it. – MAW74656 Sep 12 '12 at 21:06
1

I'm assuming that you are using a "sql server login" for the vendor's server, and you are using that when creating the linked server.

When you use a linked server, a query running on your server connects to the vendor's server. If a firewall blocks your local server from connecting to the vendor's server, your connection attempt will fail.

If connecting directly from your workstation to the vendor's server you go through a different firewall or no firewall, then the connection may succeed.

This scenario matches the behavior you describe. My usual tests would be: 1. Can I ping from my server to the vendor's server? Both by IP and by hostname? ping usually gets through firewalls. 2. Can I connect using SSMS from my server (using an RDP session) to the vendor's server? Both by IP and by hostname?

If you can ping OK but not connect with SSMS, this usually indicates that the ports for SQL Server probably need to be opened on the firewall.

In short, check all of the firewalls involved. That would include any software firewall on your server or on the vendor's server, or any hardware firewall between them.

In these situations, ping and traceroute are your friends. Ping and traceroute may help you locate the IP of the router that doesn't send your packets on to the vendor's server.

Improve this answer
0

Did the vendor use 2008(r2) prior to the move?

If so, there is a possibility that when they moved they didnt transfer all of the inbound external ports. You may have been part of a redirect policy for your login and when they moved the policy is still pointing to the old server address.

Although this is not an Authentication issue, i think it is part of your Authentication. Cause the server moved and now you cant access.

Improve this answer
9
  • I'm not completely understanding here. Redirect policy for what? – MAW74656 Sep 12 '12 at 15:07
  • In 2005, you could manage the rights of a UserLogin/Credential to specific server(s)/Database/Schema. As such, if they moved the server, the address changed (IP[4/6], HostName, Instance) and did not make the appropriate change to the UserLogin/Credential then your going to dead space. Which is what you are doing now, being pointed to a dead-end street. – GoldBishop Sep 12 '12 at 15:13
  • So this forwarding option is setup on the database user? – MAW74656 Sep 12 '12 at 15:15
  • And if this is the cause, why can I connect to the server just fine manually? Its the linked server which will not function. – MAW74656 Sep 12 '12 at 15:17
  • It can be, it can be associated with the AD-Group they are members of, the Credential Group, or the Account used to login to the Server. Pending on how they setup the account your using pends on which UserLogin tree they edit. – GoldBishop Sep 12 '12 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.