October 3, 2003 at 8:14 am
We use Windows Authentication with SQL Server 7. Our operating system is Windows 2000 Server. We have one Windows domain that includes offices in two different states. The primary Windows domain controller server is in State 'A'. We are located in state 'B'. We have a local Windows domain controller server that authenticates the users here. Recently, the primary domain controller in State 'A' was not available. We were still able to log on to the Windows network in State 'B'. However, we were not able to connect to SQL Server. It would appear that SQL Server was looking to the Primary Domain Controller in State 'A' to authenticate users. It would seem that SQL Server should be authenticating to the local domain controller here in State 'B' - and that is what the setup should be. I did some research and it appears that maybe the Kerberos authentication protocol used by Windows 2000 is involved. Any ideas as to how I can ensure that SQL Server authenticates users based on the local domain controller? I appreciate any help that can be offered.
October 6, 2003 at 8:00 am
This was removed by the editor as SPAM
October 6, 2003 at 6:46 pm
Need some clarification on your setup here because there are some things that seem a bit confusing.
This is to clarify NT 4.0 versus Active Directory:
1) Are you still using Windows NT 4.0 domain controllers or are you all on Active Directory?
2) Did you mean PDC Emulator instead of PDC?
If you are AD... Kerberos probably shouldn't be an issue here. If the sites are set up properly, SQL Server should be able to find the nearest DC (that would be in State B) and try to authenticate there. However, this requires DNS, etc. Wonder if the problem broke down there. Do both DCs run DNS or is DNS just on the first server (State A)?
Did anybody sniff the traffic to see which server SQL Server tried talking to (if either one)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 8, 2003 at 8:50 am
Thanks for your feedback, wich is appreciated! I was directed to the following article which may explain the issue: http://support.microsoft.com/?id=258025
October 10, 2003 at 5:48 am
We have had this sort of problem as well, but we are in native mode AD on W2K. The main place this has come up is in Exchange and SQL Agent jobs.
In Exchange, there are links (I forget exactly what they are called) explicitly to global catalog servers (a specific role in AD). Until we explicitly added a separate link to a backup domain controller, every time the primary DC went off line mail started failing despite having about 8 other DC's on the network.
In SQL Agent we have the same problem but no solution yet. I have one SQL Server running literally ON a domain controller, with another (holding the GC role and most others) sitting in the same rack. If the other DC goes down, SQL Agent jobs that require authentication against group membership start failing. And they are failing despite the same machine SQL Agent is running on being a DC.
I have not pursued this nearly enough yet, but it has happened numerous times. My gut tells me that SQL Agent, once running, picks a DC and then won't switch to another. But I really don't know. For now, we just keep the primary DC up.
Would like to know if others have seen this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply