Lesson 4: Service Accounts Strategy (What to Do in Lab vs Production)

Section 5 — Lesson 4: Service Accounts Strategy (What to Do in Lab vs Production)

Welcome back. Before you install SQL Server, you need to make an important DBA decision:
what accounts will run the SQL Server services?

Service accounts are a security and reliability foundation. A weak service-account setup can create:
permission problems, failed backups, broken jobs, auditing gaps, and serious security risk.
A strong service-account setup supports least privilege, stable operations, and clean troubleshooting.

Lesson description: You will learn what a SQL Server service account is,
which SQL Server services typically exist, why DBAs avoid using overly-privileged accounts,
what to use in a lab versus what to use in production,
and the professional strategy for least privilege, auditing, password management, and operational safety.
By the end you will choose a service-account approach for your lab that is simple and correct,
while fully understanding what you would do differently in a real company.

1) What Is a Service Account?

A service account is the identity (Windows account) a service runs under.
SQL Server is made of services such as:

• SQL Server Database Engine (the core database service)
• SQL Server Agent (runs jobs, schedules, automation)
• SQL Server Browser (helps clients discover instances; often not needed)
• Full-Text (if installed)
• SSIS/SSAS/SSRS (if you install them)

DBA reality: When something fails (a job, a backup, an integration),
the service account is often part of the root cause.

2) Why DBAs Care (Security + Stability)

Service accounts matter because they control:

• access to database files, backup folders, and log locations
• ability to run SQL Agent jobs (PowerShell, CmdExec, SSIS, etc.)
• access to network shares (for backups to a file server)
• permissions in Active Directory environments
• auditing clarity (who did what)

DBA rule: The safest system is the system with the least privilege required to function.

3) The Accounts You Will See in SQL Server Installs

3A) Built-in “virtual service accounts” (common and safe)

SQL Server supports virtual accounts such as:

• NT SERVICE\MSSQLSERVER (default instance Database Engine)
• NT SERVICE\SQLSERVERAGENT (Agent)
• NT SERVICE\MSSQL$InstanceName (named instances)

These accounts are local to the machine and designed for services.
They are usually a strong choice for single-server setups and labs.

3B) Local System / Network Service (DBAs typically avoid as defaults)

• LocalSystem: extremely privileged on the local machine (too powerful)
• NetworkService: less privileged, but can still create unclear access patterns

DBA guidance: In production, avoid using LocalSystem for SQL Server Database Engine unless you have a very specific controlled reason.

3C) Domain accounts (common in production)

In enterprise environments, SQL Server services often run under domain accounts, for example:

• DOMAIN\svc_sql_engine
• DOMAIN\svc_sql_agent

Domain accounts are used when SQL Server needs controlled access to network resources
(backup shares, replication, cross-server jobs, Always On, etc.).

4) Lab vs Production: The Correct Strategy

4A) What to do in your lab (simple, safe, and realistic)

For this course lab, use one of these two approaches:

• Recommended lab choice: Use SQL Server default virtual accounts
  (the installer configures them correctly and safely for local use).
• Optional lab choice (advanced practice): Create a local Windows user account like
  SQLSvc and run services under it, then practice granting only required permissions.

Why the virtual account is ideal for labs: It reduces friction and avoids domain complexity,
while still reflecting modern SQL Server best practices.

4B) What to do in production (professional standard)

In production, DBAs usually:

• use dedicated service accounts (often domain accounts)
• separate Engine and Agent accounts when needed for auditing and risk isolation
• avoid shared accounts across many servers unless managed carefully
• apply least privilege with explicit permissions to required folders and network shares
• manage passwords via policy (or use managed service accounts)

DBA principle: Production is about controlled access and predictable operations at scale.

5) Least Privilege: What SQL Server Actually Needs

Your SQL Server Database Engine service account typically needs:

• read/write permissions to SQL Server data and log file directories
• read/write permissions to tempdb directories
• read/write permissions to backup directories (local or network)
• ability to start the service (configured via Windows service control)

Your SQL Server Agent service account typically needs:

• permissions to execute job steps (T-SQL always, and sometimes CmdExec/PowerShell/SSIS)
• access to output/log directories used by jobs
• access to network shares if jobs write backups or exports to a file server

DBA warning: “Just make it local admin” is not a strategy. It is a shortcut that creates risk.

6) Password Management and the Real DBA Problem

In production, passwords expiring can cause outages:
SQL services fail to start, jobs fail, backups fail.
DBAs prevent this with:

• strong password policies and documented rotation procedures
• using managed service accounts (where available) to reduce password risk
• alerting/monitoring for service failures

Lab note: In your lab, keep it simple. You are learning the concepts first.

7) When You MUST Use a Domain Account (Production Scenarios)

Domain accounts become important when SQL Server must interact with other systems across the network, such as:

• backing up to a remote file server share
• Always On Availability Groups / clustering scenarios
• replication across servers
• cross-server automation that uses Windows authentication
• enterprise monitoring agents or centralized job orchestration

DBA mindset: If SQL Server touches other servers, identity and permissions must be planned.

8) A Simple Lab Implementation (What You Should Do Right Now)

For this course, choose one:

1. Standard lab path (recommended):
   Use the default SQL Server installer service accounts (virtual accounts).
   This is clean, stable, and correct for a standalone lab VM.
2. Advanced lab path (optional):
   Create a local Windows account SQLSvc (strong password),
   then use it for SQL Engine and/or Agent to practice folder permissions.

Course recommendation: Choose the standard lab path now,
and later you can revisit the advanced path as an optional skill upgrade.

Practice (Write This in Your DBA Notebook)

1. Which service account strategy did you choose for your lab (virtual account or local SQLSvc)?
2. Why did you choose it (stability, simplicity, realism)?
3. In production, when would you switch to domain accounts?
4. List the folders that SQL Engine must access (data/log/tempdb/backup paths).

Lesson Summary

• Service accounts control what SQL Server services can access and execute.
• DBAs use least privilege to reduce security and operational risk.
• Labs should be simple: default virtual accounts are usually the best choice.
• Production often uses dedicated domain accounts (or managed accounts) for network access and auditing.
• Poor service-account strategy can cause real outages (services won’t start, jobs and backups fail).

Next Lesson: Install SQL Server (Edition Guidance + Components)

Next, you will install SQL Server with the correct components,
choose an edition suitable for learning, and make installation decisions the way DBAs do in real environments.