June 4, 2007 at 1:42 pm
I have a problem that sounds like it should be simple and common, yet is not as straight forward as it sounds.
I have a SQL Server cluster. If I take any SQL Server in our environment and try to create a linked server to this SQL Server cluster, I get "SQL Server does not exist or access denied". This is not a login/password issue and it is not a mistyping of virtual service name running on the cluster. The virtual service name is in DNS and I'm not using any aliases, etc.
I can use SQL authentication from Query Analyzer with the same login/password from any of the servers to the clustered service.
This was working fine. Suddenly when tried, it has simply stopped working. The linked server connections hadn't been tried for about a week. At the beginning of that week there had been an IP change to accommodate some infrastructure changes. My *guess* is that, some how, this IP change could be causing my problem. However, again, it appears that this problem is isolated to linked server connections to the clustered service.
Does any one have any insight into this confusing problem?
June 5, 2007 at 2:22 am
IP Change: ist the virtual name in DNS up to date ? Try a ping with the ip no. and a nslookup to make sure name resolution is fine.
June 5, 2007 at 10:34 am
Hi Moreno.
Thanks for the reply. As mentioned, this isn't something that simple. The problem is currently limited to linked server. All connectivity directly to the virtual name, not including linked server connections, works fine.
We have made a significant discovery. If we go:
exec sp_setnetname 'MyVirtualSQLName', NULL
...on the server we're using to link to the clustered service, the connection starts working fine.
I'm still uncertain as to exactly what the problem is and what our permanent solution will be moving forward.
Any additional info would be greatly appreciated.
June 5, 2007 at 2:21 pm
I thought I'd post follow-up so that this was added to the library of documentation available on this site.
Interestingly enough, I jumped the gun on thinking that
exec sp_setnetname 'MyVirtualSQLName', NULL
...had any positive consequence. Rather, without a server name the remote server defaults to local and our tests where on a server that had duplicate tables to the clustered service where we had our linked server connection and was returning local results. This makes sense and was assumed to be progress since we magically had what appeared to be results coming back.
The actual problem was that clustered instances don't like IP changes. There is a registry entry on both clustered node's registries under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Cluster
The key name is ClusterIpAddr and it contains the virtual instance's IP. I changed that IP to the new IP and all was well again.
Why did this only show itself on Linked Server connections??? I don't know. I do know that, after three days with this problem, it finally was affecting all connections to the DBMS.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply