January 16, 2008 at 8:19 am
We had sql server 2005 cluster server. We created linked server before we set the MSDTC on cluster. After that, we are unable to create any linked server using SQL login. Always say:
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
TCP Provider: No connection could be made because the target machine actively refused it.
OLE DB provider "SQLNCLI" for linked server "XXX" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "XXX" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 10061)
Actually we can connect to SQL Server remotely, checked MSDTC allow remote connecting, allow connect.
Can someone help this?
January 16, 2008 at 3:48 pm
Linked Server to the cluster or from the cluster to another server?
Regards,Yelena Varsha
January 17, 2008 at 5:16 am
From teh cluster server to all other sql servers all failed to create
January 17, 2008 at 5:23 am
Have you verified that the other SQL Servers have Remote Connections enabled?
Check the Surface Area Configuration Tool on each of the servers. If Remote Connections is not enabled, you'll need to turn it on before you can use Linked Servers.
January 17, 2008 at 5:25 am
Yes, those all SQL server 2000 servers. We have a SQL 2000 server linked to those servers now. Thank you for the help!
January 17, 2008 at 5:29 am
You imply that you have several other MS SQL Servers. If we take the clustered server as server A, and you've tried to make it talk to both servers B & C without success, have you tried making server B talk to server C? If so, did it work? If not, was the error the same?
It could be a firewall issue on your target server, or some sort of firewall on your cluster is disallowing the outbound connection attempt.
January 17, 2008 at 5:32 am
Sunny,
What Service packs for SQL 2000 are you running on all your machines?
And do what Ian suggested, try to set up linked servers between the non-clustered servers and see if it works.
January 17, 2008 at 5:34 am
Oh, and what service pack is running on your 2005 Cluster? SP1, SP1 with hotfix or SP2?
January 17, 2008 at 5:37 am
The other SQL Server 2000 with service pack 4. The currecnt SQL Server 2000 is a cluster SQL Server, we just migrate to this new SQL Server 2005 cluster server. We had set up the linked server for two. Ans those are working. But after set MSDTC as cluster. Now can create the rest of linked server. Thank you for help!
January 17, 2008 at 5:41 am
The new SQL Server 2005 is on server pack 2. Also if the server I had problem A(2005 cluster SQL Server), the other Server SQL 200 is B. I just test it. I can create linked server from B to A, but I can't create linked server from A to B. Thank you for the help!
January 17, 2008 at 5:45 am
Wait! I can linked from B to A, but when I explore the data, I had error say SQL Server is not exist. Any idea?
January 17, 2008 at 5:53 am
What's the exact text of the new error message?
January 17, 2008 at 5:58 am
Here is the error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. (Microsoft SQL Server, Error: 17)
January 17, 2008 at 6:14 am
Make sure the user account that you've got set up with this linked server has the appropriate permissions on the database for the T-SQL query you're trying to run. Also, check the user name & password as you might have a typo.
January 17, 2008 at 6:17 am
I create a very simple test account: account name: A and passowrd:a
I also try to connect as SQL login use this account and it all fine.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply