January 17, 2008 at 6:22 am
That tells me the account you were using had something wrong with it. Maybe a typo or maybe a permissions issue. Once you figure out what the problem is with the old account, you should be good to go.
January 17, 2008 at 6:27 am
Wait, you miss understander it. This account I can connect through the management studio, but when used on the linked server, it had error. What I mean because the user account and password is so simple, I won't type wrong. I also re-typed the account and password. the linked server still won't work.
January 17, 2008 at 6:38 am
Are you sure you haven't typo'd the Server name itself?
January 17, 2008 at 6:41 am
100% sure. I guess it is the msdtc or distributed transaction had some problem. I had set up the MSDTC allow remote connection and no password needed.
January 17, 2008 at 7:07 am
Try deleting the Linked Server and recreating it. Sometimes that's the only thing that can help in situations like this.
January 17, 2008 at 7:09 am
just delete it then re-create it, no luck.
January 17, 2008 at 7:14 am
Then the only thing left is the firewall issue Ian mentioned.
January 17, 2008 at 4:23 pm
In the command prompt on server A enter:
telnet ServerArealName 1433
make sure you enter the real name of the server.
if you can ping the server but the command above gives you "can not open connection to the host on port 1433: connect failed" then you do have port issues. If you are working with the named instances also try port 1434.
P.S. SQL Port will be 1433 only if you did not change the defaults. You have to enter the real port number.
Regards,Yelena Varsha
March 21, 2008 at 9:18 am
I believe I am having the same problem. We have a SQL 2005 cluster that I can't create a linked server to a SQL 2000 server off site, however another normal SQL 2005 server can. The network people say that there is no firewall issue. When I try to open Surface Area Configuration for Services and Connections I get the error "You cannot configure surface area of clustered services by connecting to a computer name. Connect to the virtual server to configure clustered services." In Cluster Administrator I don't see where these options are even available.
When I open Surface Area Configuration for Features I get the error "An error has occurred while establishing a connection to the server...the default setting...does not allow remote connections". I want to check the settings but don't know where, so I'm still researching 🙂 Will let you know if I find anything.
March 21, 2008 at 9:26 am
Linda,
I don't think the issue is in Cluster Administrator.
When the error message talks about Virtual name verses Computer name, there is a distinction. If the servers are literally named Server1, Server2, Server3, etc., but your cluster name is ProdServer, then you need to connect to ProdServer, not Server1.
Does that make sense?
March 21, 2008 at 9:40 am
You are so right--I should have noticed that. (I couldn't see the forest because of the trees....) Anyway, the SQL Server settings are correct but I still see no reason why the linked server can't connect. I have the alias on the Cluster set up exactly like the alias on the other server and still nothing. Both the regular server and cluster are 64-bit but if one works the other should as well, right? I am not a cluster guru so I will gladly accept any other ideas. After that, I may need to approach the network people again....
March 21, 2008 at 9:45 am
So your linked server is from 2005 to 2000 right? Can you linked server to other 2005 instances? There is a weird issue when using a linked server from 2005 to 2000 ... You need to re-apply a piece of SP4 to your SQL2000 servers (instcat.sql) - see this article for more details.
I haven't been following this thread and I don't know much about clustering, so I apologize if this does not pertain to your situation.
Good luck.
March 21, 2008 at 9:45 am
Linda,
Don't answer this on the forum but...What ports are you using for your cluster? Have you checked with the network people to make sure that those are the ports they actually have open on the network?
Also, have you checked the other SQL Server (the one you're trying to link) to make sure it's accepting remote connections and that it doesn't have port issues? Check the server properties as well as the Surface Area Configuration to make sure there's nothing funky going on.
March 21, 2008 at 10:03 am
Adam, interesting article but it addresses distributed queries and I can't even create a good link 🙂 I'll keep an eye out for this bug though. Thanks.
March 21, 2008 at 10:09 am
Brandie, the database server I am attempting to link to is a clustered SQL 2000 and the source database server is a clustered SQL 2005. There is a different SQL 2005 server that can link to the SQL 2000 cluster so the 2005 cluster should be able to as well. I've decided to follow up with networking on the firewall issue again--maybe they missed an IP address or something. Thanks.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply