Connecting to SQL Server using cached credentials

Question

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?

• Jon Seigel: 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.
• Jirka Hanika: @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.
• Remus Rusanu: 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...
• Jirka Hanika: @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.

Answer 1

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.

• Jirka Hanika: 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?
• mrdenny: @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.
• Jirka Hanika: 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?
• mrdenny: Correct, when services log on the credentials aren't cached, and when services start they don't use cached credentials.
• Jirka Hanika: Thank you for the extra info. We will go the local account way and maybe even buy your book :-)