Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

While migrating from SQL authentication (user-password) to integrated security I ran into an unexpected combination of requirements.

  • SQL clients are services running under a LOCALSYSTEM or NETWORKSERVICE account
  • SQL clients use integrated security to connect to the local SQL Server
  • The system is in an AD domain.
  • Database connections must be successfully established even when network connectivity to the domain controller is down for hours or days at a time.

On the luckier side, the SQL clients and SQL Server (2005 SP4) always run on the same box.

The conclusion of reading, experiments done so far and a support call to MS seems to have been that I cannot meet all these requirements at the same time, regardless of whether Kerberos or NTLM is used.

On the other hand I see some references to cached credentials that are giving me hopes that it might be possible to somehow connect to the database using cached credentials, under some conditions, following a previous successful domain login (service startup, or previously established connection).

  1. Is anyone successfully using cached domain credentials to connect to a local SQL Server from a service running under a domain account, during prolonged DC outages? How is that configured? What are the security implications?

  2. Are the four requirements above really incompatible?

share|improve this question
    
+1 good question, and very interesting. –  Thomas Stringer Jun 4 '12 at 16:42
    
I'm not 100% familiar with this, but my first reaction would be to ask: if the SQL client services each ran under a specific AD account instead of NETWORK SERVICE, would that solve the problem? That seems like a good thing to do security-wise anyway. –  Jon Seigel Jun 4 '12 at 17:29
    
@JonSeigel - Unfortunately not. All domain accounts have this problem (NETWORKSERVICE translates to the computer account). In fact we are considering to use a local non-domain account which is obviously a step back from security but a less drastic one than removing the whole system from the domain. –  Jirka Hanika Jun 4 '12 at 18:19
    
Your requirements are fundamentally flawed. You cannot ask to use integrated authentication on one hand, and on the other hand request availability when the AD is unreachable. Pick your priority: does your system require integrated authentication (and accept you 're taking a dependency on AD availability) or you require connectivity when AD ins unreachable or unavailable (and accept you cannot use integrated authentication). You cannot have the cake and eat it too... –  Remus Rusanu Jun 4 '12 at 18:53

1 Answer 1

up vote 2 down vote accepted

Service accounts can't make use of cached credentials. Only interactive users can use cached credentials.

As everything is running on the same server, create local Windows accounts and setup the services to run under those local Windows accounts. Local accounts have no requirement that Active Directory actually be online.

share|improve this answer
    
This was the key bit of info I was missing. Can you please confirm my understanding? Suppose that I create a domain account that is allowed to log on interactively. Then credential caching will happen as configured when this user logs on as a normal user; but if I try to use the same account as a service logon account, such a service startup or database authentication will never result in caching the credentials in registry. Is that correct? –  Jirka Hanika Jun 4 '12 at 19:36
    
@JirkaHanika No, when the service tries to start it'll throw an error message and the service won't start. When verifying if the account can login it won't be able to as there's no domain controller to verify against. –  mrdenny Jun 4 '12 at 20:26
    
I get that. But you are saying that when AD is online, the service can log on, but no credentials will be cached for later use, because this particular log on is not interactive. Correct? –  Jirka Hanika Jun 4 '12 at 20:30
    
Correct, when services log on the credentials aren't cached, and when services start they don't use cached credentials. –  mrdenny Jun 5 '12 at 13:07
    
Thank you for the extra info. We will go the local account way and maybe even buy your book :-) –  Jirka Hanika Jun 5 '12 at 13:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.