6

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?

Improve this question
4
  • 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
    Commented Jun 4, 2012 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
    Commented Jun 4, 2012 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
    Commented Jun 4, 2012 at 18:53
  • @RemusRusanu - Your comment was initially helpful, but I think it's now obsolete given the accepted answer. We have the cake that we ate. The solution was integrated authentication via a local account. We had to drop the requirement to run the services under NETWORKSERVICE, though.
    – Jirka Hanika
    Commented Oct 29, 2015 at 15:31

1 Answer 1

Reset to default
5

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.

Improve this answer
6
  • 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
    Commented Jun 4, 2012 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
    Commented Jun 4, 2012 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
    Commented Jun 4, 2012 at 20:30
  • Correct, when services log on the credentials aren't cached, and when services start they don't use cached credentials.
    – mrdenny
    Commented Jun 5, 2012 at 13:07
  • Thank you for the extra info. We will go the local account way and maybe even buy your book :-)
    – Jirka Hanika
    Commented Jun 5, 2012 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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