September 28, 2006 at 12:06 pm
Given: one SQL Server 2005 Active/Passive cluster, with services running as a domain user account on Windows 2003, in a Windows 2003 domain.
If communication is lost between the cluster and the domain, why would all connections to the DBMS that use SQL Server Authentication fail (even sa)? I would think that the only time there would be an issue is when an attempt were made to restart one of the services that logs into the domain. Otherwise, loss of the domain would mean loss of the cluster.
Further, with domain communication lost, one can still open Management Studio as the local server Administrator account. Databases can be viewed, tables accessed, everything looks normal... But attempting to view properties on security objects fails with a generic message about not having sufficient privilege on the resource.
Thoughts?
Sincerely,
Dan B
September 29, 2006 at 12:31 pm
Ny guess would be because the virtual instance name and virtual IP address are no longer valid.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 29, 2006 at 12:48 pm
No, I don't think that that is the case... They are not tied to the domain and the cluster itself doesn't fail. Everything still looks good in Cluster admin.
Connections are still made to the server, but they fail to log in properly such that the error logs fill up with 'login failed for user ... ' messages.
Sincerely,
Dan B
October 2, 2006 at 7:02 am
Dan
In a cluster environment, the operating system regularly polls SQL Server to check that it is still alive. I think it does this by connecting as a Windows account and executing the query "SELECT @@servername". Therefore, if connectivity to the domain is lost, I believe that this connection would not be able to be made and that the cluster service would therefore assume that the SQL Server resource has failed.
John
October 2, 2006 at 7:41 am
That's the maddening part. The service doesn't fail. None of the clustered resources fail. The active node stays up, active and accepting requests. On the surface it looks like a simple login failure. Other than the login failed messages, there is no indication of a problem in either the Windows event logs or the SQL Server error logs.
Further complicating the issue, I can log in as the local windows admin and get into the server instance. I can create new SQL login accounts, but once I go to view the properties on those accounts I get the error message in my first post - a generic statement that I do not have sufficient rights to the resource.
Note that this problem seems to happen only with SQL 2005. I have several SQL 2000 clusters, also on Windows 2003 and in the same domain, that have no problems (provided the services don't need to be restarted) when the domain is unavailable.
Sincerely,
Dan B.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply