SQL Domain Controller – Interview Question of the Week #072

A client asked me a set of SQL domain controller questions, all about running SQL Server on a domain controller, and I was sure they would turn up in interviews. They do. I answered them in 2016. I have now checked every answer against Microsoft’s current SQL Server documentation, and one of my old answers needs correcting.

Q1. Can I install SQL Server on a domain controller?

Technically yes. SQL Server setup does not block it. But Microsoft’s current security guidance is direct: for security reasons, do not do it.

The interview answer is “setup allows it, Microsoft advises against it, and here is why”. Say only “yes” and you have answered half the question.

Q2. Why does setup warn me about it?

Two reasons, and the second is the one people forget.

The first is load. A domain controller handles every logon in its site. SQL Server will happily use all the memory and CPU it can get. Put them on one machine and a heavy query can slow down people logging in to the network, and a burst of logons can slow down your queries.

The second is security. A domain controller holds the keys to the whole domain. Anyone who gets control of the SQL Server service on that machine is sitting next to those keys. Every database vulnerability becomes a domain vulnerability. That is the real reason for the advice.

Q3. What about a read-only domain controller?

The current documentation is specific. On a read-only domain controller, setup cannot create the security groups or service accounts it needs, and setup fails.

Q4. Can I install SQL Server first and promote the machine later?

No. The documentation says that once SQL Server is installed, you cannot change the machine from a domain member to a domain controller, or back again. You have to uninstall SQL Server first, change the role, and install again.

I have seen what happens when somebody promotes the machine anyway. The service stops starting, with authentication errors. There is a separate post on that one, and on the workaround that got a client running again.

Failover cluster instances are ruled out entirely. They are not supported where the cluster nodes are domain controllers.

Q5. Which service account should SQL Server use on a domain controller?

This is the answer I want to correct.

In 2016 I said you could not use a local service account, and that the only option left was LocalSystem. The first half is right. The current documentation says plainly that SQL Server services cannot run under a local service account on a domain controller. The second half was wrong. LocalSystem is not the only option left, and it is the worst one.

On an ordinary server, LocalSystem is already too powerful for SQL Server. On a domain controller it is worse, because the machine it controls is the domain controller itself. The right choice is the same as anywhere else: a low-privileged domain account, created for SQL Server and used for nothing else.

Bonus: How Do You Know If a Machine Is a Domain Controller?

Handy before an install, and easy to forget on an old box that has had several jobs. One line of PowerShell:

(Get-CimInstance Win32_ComputerSystem).DomainRole

The number means:

0  standalone workstation
1  member workstation
2  standalone server
3  member server
4  backup domain controller
5  primary domain controller

Four or five, and you are on a domain controller. My own test machine returned 0, which is what you expect from a machine that is not on a domain.

The One-Line Answer

If an interviewer wants it short: setup allows it, Microsoft advises against it, you cannot change the machine’s role afterwards, and if you do it anyway, use a dedicated domain account, never a local one.

The question is not whether SQL Server can run on a domain controller, it is whether you want your database to hold the keys to the domain.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.