#004 Managing Users and Service Accounts

Service accounts, user accounts, managed service accounts, group managed services accounts. 

What's the difference?

One of the most important aspects of being a DBA is securing data.  That often involves managing access to SQL Server instances and databases through domain accounts.  

This can be done through individual accounts or groups and SQL Server server roles, database roles, or by assigning individual permissions.

We won't cover groups or SQL Server permissions today, but I think it's important to distinguish between these objects in Active Directory.  And, gain a bit of hands-on experience by creating a few.

This week's challenge will be five parts:

• Review the differences between these account types.
• Determine if your domain supports managed service accounts.
• Create two user accounts.
• Create a standard service account
• Create a managed service account (MSA)

Ready?

Estimated time to complete: Less than 3 hour.

Step 1: Review the differences between these account types

By now, you've built a domain controller in your sandbox environment.  Although it is a straightforward installation and configuration (much easier nowadays), it gives you access to the other side. 

Usually, we don't create accounts and this process is handled by system and/or security admins.

There are a few nuances we won't cover that you may find in production environments.  These may include separating these account types by OUs or Organizational Units (to allow for applying different group policy settings).  See the SQL Server DBA's Handbook: Navigating Active Directory Integration and Security for more information on OUs.

Let's break these down:

User Accounts

• Purpose: User accounts are created for individual users to provide them access to the resources within an Active Directory domain.
• Common Uses: These accounts are typically used by employees or administrators to log into computers, access shared resources like files and printers, and authenticate to applications or databases.
• Example: When you log into your workstation, and open SQL Server Management (SSMS) using your AD credentials, and then connect to a SQL Server instance using Windows authentication, you're using a user account.
• Characteristics:
  • User accounts are associated with a person.
  • They have specific permissions and rights assigned based on their role within the organization.
  • Passwords are typically set to expire periodically for security reasons.  This is one reason you may find these accounts within an OU separate from service accounts.  Service account passwords should still expire, but their expiration may not need to be as frequent as for user accounts.

Service Accounts

• Purpose: Service accounts are used by applications or services (such as SQL Server, IIS, or other server-based applications) to interact with the operating system and network resources.  
• Common Uses: These accounts run background services or scheduled tasks without user intervention.  For example, the SQL Server database engine and agent services are frequently set to run using a service account.  
• Example: If SQL Server is configured to run under an AD account, this is a service account.  
• Characteristics:
  • Service accounts often have specific permissions required by the application or service.
  • Passwords can be set to not expire to avoid service disruptions (please don't do this in your production environment).
  • They are typically not used to log in interactively (no desktop login).
  • There's no difference between creating a service account VS a user account.  The difference is in its purpose.  Both are AD accounts.

Managed Service Accounts (MSAs)

• Purpose: Managed Service Accounts are a special type of service account designed to provide automated password management and simplified SPN (Service Principal Name) management for services running on a single server.  
• Common Uses: MSAs are particularly useful for services that require specific network permissions and need to operate securely without the overhead of manual password management.
• Example: You might use an MSA ('msa_SQLServerServiceAccount$') for a SQL Server instance that needs to run with domain privileges.  
• Characteristics:
  • Passwords are managed automatically by AD, reducing the risk of service outages due to password expiration.  No more sharing the password or rotating it when someone leaves the organization.
  • MSAs can be used only on one server by default (unless you're using Group Managed Service Accounts).
  • They provide a higher level of security when compared to traditional service accounts because of their automated password management.
  • There's no longer a need to restart SQL Server services due to password rotations (say goodbye to late-night or weekend maintenance windows to change service account passwords and restart SQL Server services).

Group Managed Service Accounts (gMSAs)

• Purpose: gMSAs extend the functionality of MSAs to multiple servers, making them ideal for services like SQL Server running on a cluster, availability groups, or web applications deployed across multiple servers.
• Common Uses: gMSAs are used in environments where the same service needs to run across multiple servers, such as in load-balanced applications or SQL Server failover clusters (or AGs).
• Example: A SQL Server Always On Availability Group might use a gMSA to manage service accounts across multiple nodes in the cluster.
• Characteristics:
  
  • Similar to MSAs but can be used by multiple hosts (servers).
  • Automatic password management and SPN handling.
  • Requires additional configuration in AD to manage.

Step 2: Determine if your domain supports Managed Service Accounts

Managed Service Accounts require at least a Windows Server 2008 R2 domain functional level.  

Connect to DC1 (or whatever you named your domain controller) and review the domain functional level.  There are several methods you can use to determine this.  One way is to use Active Directory Users and Computers.

1. Open Active Directory Users and Computers.
2. Right-click on your domain name in the left pane and select properties.
3. In the General tab, you will see the Domain Functional Level.

Step 3: Create two User Account

Unless you've created an additional account, you've been stuck with the domain Administrator account.  

Although this allows you to do anything in the domain, this isn't realistic.  In this step, you'll create a standard user account, one without domain admin rights, and another one to use when domain admin privileges are needed.

Connect to DC1 and create two user accounts.  You can name these anything but I went with Luke and admin_Luke. 

Again, there are multiple methods you can use to perform this step: PowerShell, Active Directory Users and Computers, Ansible, etc.

Create these in the Users folder.  The process using Active Directory Users and Computers would look something like this:

Your admin_* account would need to be a member of the Domain Admins group (don't forget this step).  

Step 4: Create a Standard Service Account

This step would look exactly like Step 3.  Be sure you don't add this one to the domain admin group.  You could name this one svcSQLServerA.  Adding "svc" will help to distinguish this account from user accounts.

Step 5: Create a Managed Service Account (MSA)

This one requires that you use PowerShell and the Active Directory module.

1. Logon DC1 using your admin_* account created in step 3.
2. Open PowerShell and import the Active Directory module.
3. Next, you'll use the New-ADServiceAccount cmdlet to create the MSA.  The parameters you'll use are:
   1. Name: The name of the MSA (e.g., `msaSQLEngine`).
   2. DNSHostName: The DNS domain name of your AD domain (mine is homelab.local).
   3. Path: The distinguished name of the Organizational Unit (OU) or container where the account will be created.  By default, MSAs are stored in the Managed Service Accounts container.

Here's what the process looks like in my environment.

We'll go into more depth on group-managed services accounts later on.  For more information on these account types, check out the link below.

Understanding Service Accounts 

Helping Others and Sharing Your Results

That's it for this week. 

If you have tips other readers can learn from, please share them in the comments.  You can message me on LinkedIn or post about it and tag me with the #dbachallenges hashtag.

Feedback

If there's a DBA Challenge you'd like to see, please let me know by replying to this email.  Have ideas on how to make these more useful?  I'd love to hear those too.

By the way, two items I'd like to mention:

I've dropped the price of the Automated Sandbox Fundamentals course until the next version is released (next year).   

Automated Sandbox Fundamentals 

I'm looking for feedback on how DevOps or Platform Engineering may be impacting your role as a DBA.  If you'd be interested in a quick, 10-15 minute Zoom call, or just want to send over a quick summary via email, I'd love to hear about it.  I'm currently building the course below, and want to make sure it's addressing what is important to you in either of these practices. Specifically, from a DBA's point of view.

 Ansible for SQL Server DBAs: Automation Simplified

Good luck and I'm looking forward to seeing your results!

Luke