I have observed that a temporary loss of a domain controller can cause
problems creating new ado connections between a client machine running
ado and a separate sql server machine that are members of the domain.
I understand why this happens when creating connections with windows
authentication. What is a mystery is that it also sometimes effects
new connections that use "sql authentication". Below is a description
of my test scenario.
a. Setup 3 machines.
i. one domain controller machine. windows 2000 based. I have tried
both a regular domain setup and a domain setup in compatibility mode.
ii. one sql server machine that is a member of the domain. windows
2000.
iii. one client machine running an ado test program that communicates
with the sql server machine. I have tried both 2000 and xp.
b. Start your test ado program and create a connection.
c. Fire a query.
d. It should work.
e. Unplug the network cord on the domain controller.
f. Create a new connection and fire a new connection about a minute or
so after.
g. It should work. Apparently the client caches account information
from the domain controller for a certain amount of time. The time
seems to be shorter by default in xp than 2000.
h. Wait 20 minutes or more.
i. Create another new connection. You will notice a timeout error.
If your using windows authentication the timeout will happen 100% of
the time. That is to be expected. If your using sql authentication
the timeout seems to happen about 50% of the time. I can't explain it
other than some strange Microsoft bug. I speculate that it may be
some bug with the way ado caches connections. Perhaps a previously
setup windows authentication connection gets reused by a request for a
sql authentication connection. However I haven't been able to prove
it.
Any insight you can offer would be appreciated. I also have a test
program you can use to reproduce this behavior if you are interested.
Thanks,
Frank