A few weekends back, I posted the question below on LinkedIn.

"Quick question for DBAs and DBREs. 
Are most of you building out new SQL Server environments using group managed service accounts or still using standard service accounts with passwords?"

Overwhelmingly, group-managed service accounts are used. 

One of the most frustrating things I continue to run into is when a gMSA is created and used for the SQL Server service account, I still encounter "The SQL Server Network Interface library could not register the Service Principal Name (SPN)...." 

If you're unfamiliar with gMSAs, I'll cover:

• What they are and the benefits they offer
• The permissions they need to enable service principal name management
• How to provision them using automation (example playbook)
• And how to prevent NTLM failback

What are gMSAs?

Group Managed Service Accounts represent a significant shift from traditional domain accounts.  A gMSA is a special type of domain account designed to be used by services running on multiple servers (think Availability Group replicas, for example).  Want to avoid taking a maintenance window to rotate your SQL Server engine service account password (you are rotating these, aren't you)?  gMSAs automate password management.  Instead of administrators creating, storing, and periodically rotating passwords, the Windows OS, along with Active Directory, handles the entire lifecycle.  

The passwords are cryptographically random and are automatically changed every 30 days by default.  This eliminates the need to stop/start services just in order to change the account password.  It also removes the need for storing those passwords in a vault like LastPass, Bitwarden, Notepad (just kidding), or the like.  See Microsoft's Group Managed Service Accounts overview page for additional details.

Another benefit, that is well-advertised, is the "automatic" management and registration of Service Principal Names. 

Many blog posts, tutorials, and articles confidently state that SPN registration is automatic.  This sets an expectation for database administrators that, upon configuring a SQL Server service to use a gMSA, the necessary SPNs will be created and maintained without any further action (leading to the frustration I stated above). 

This perception of a zero-touch configuration is a compelling feature, suggesting that a complex aspect of Kerberos setup has been abstracted away. 

I like to think of this as "simplified" management vs automatic.

Simply creating the gMSA doesn't cut it.  You still need to set permissions correctly to allow the account to register its SPNs, and this post does a great job of explaining that - https://www.sqlshack.com/configure-managed-service-accounts-for-sql-server-always-on-availability-groups/. 

We're going to build on this post in the "provisioning" section.

So what happens when a gMSA can't register its SPN? 

SQL Server falls back to NTLM authentication (less secure) instead of using Kerberos.   You can check the auth_scheme by querying the sys.dm_exec_connections view.  

And you'll see the following errors in the SQL Server error log each time the service starts.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [MSSQLSvc/DEVSQL1.SANDBOX.LOCAL:1433] for the SQL Server service.  Windows return code: 0x200b, state: 15

You're likely to encounter those pesky "The Target principal name is incorrect.  Cannot generate SSPI context" errors as well.  This error indicates that the client's Security Support Provider Interface (SSPI) was unable to generate a valid Kerberos ticket to present to the server.  This error creates a "Kerberos black hole", a complete failure of authentication with no obvious cause.  Troubleshooting this error without understanding the underlying dependency on SPN registration and the gMSA's permission can consume a significant amount of time and effort (it's a real pain to deal with.  If you haven't yet, you will).  

Now, to be perfectly clear, these are not just related to gMSAs but standard domain accounts as well.  You have to get the SPNs just right to satisfy the requirements for Kerberos.  A bit off topic for this post, but bookmark SQLCHECK.  It's a tool provided by Microsoft's CSS SQL Networking Team that you can leverage to help troubleshoot those pain in the butt SSPI errors later when they occur.

Permissions Required for Service Principal Name Automatic Registration

The gMSA must be granted specific permissions before it can manage the servicePrincipalName attribute.  This is a multi-valued, non-linked attribute that can exist on user, computer, and service account objects in Active Directory.  Its purpose is to uniquely identify an instance of a service on the network.  When a client wants to connect to a service like SQL Server, it requests a Kerberos ticket for a specific SPN (MSSQLSvc/DEVSQL1.SANDBOX.LOCAL:1433).  

The Key Distribution Center (KDC), a service running on a domain controller, looks up this SPN in AD to find the account associated with the service.  It then uses that account's credentials to encrypt the ticket, ensuring only the legitimate service can decrypt it.

Enabling a gMSA to manage its own SPN lies in the concept of the SELF security principal.  SELF is a well-known security principal that represents the object itself. 

When a permission is granted to SELF on an Active Directory object's ACL (access control list), it means the object is being given permission to perform that action on its own attributes.  For a gMSA, granting SELF the permission to write to the servicePrincipalName attribute is the method that allows a service running under that gMSA's identity to update its own SPNs in Active Directory, thereby making the "automatic" registration possible.

Permissions: Read and Write

Read servicePrincipalName is the most basic permission.  It grants the ability to read the values stored in the servicePrincipalName attribute.  While this permission is often granted by default to authenticated users, explicitly ensuring SELF has this right is a best practice to prevent any potential issues where the service might need to check for existing SPNs before attempting to write a new one.

Write servicePrincipalName allows SELF the permission to add, modify, and delete values in the servicePrincipalName attribute.  This permission is critical for the SQL Server service's registration process to succeed without error.

Common Symptoms of Incorrect Permissions

The table below contains common symptoms of what you may see if the correct permissions were not assigned to a gMSA.

Methods for Provisioning

gMSAs can be provisioned using different methods.  The key is to not stop once the gMSA is created, but also to assign the correct permissions.   These can be assigned using the Active Directory Users and Computers console or completely automated as part of your creation script.  That's where I'll focus on Ansible automation.

Why Ansible?

You might be wondering why not just use PowerShell scripts for this.  After all, the underlying commands are PowerShell anyway.  Here's the thing.  Ansible brings consistency and reliability that standalone scripts often lack.  The playbook below is idempotent, meaning it's safe to run multiple times without breaking anything or creating duplicates.  It also serves as documentation for your gMSA provisioning process.  

More importantly, this same approach scales to handle your entire SQL Server infrastructure.  From the initial OS configuration to SQL installation, database deployments, and ongoing maintenance tasks.  Instead of managing dozens of separate scripts across different teams, you get one consistent automation framework that every DBA can read and contribute to.

The Ansible playbook below can be used to:

1. Ensure the one-time Kerberos KDS Root Key exists.
2. Creates all the necessary AD security groups.
3. Creates all the gMSA user accounts (defined in the gmsa_vars.yml file) and assigns the Read and Write permissions to SELF on the servicePrincipalName attribute.
4. Adds the correct server computer accounts as members to the correct security groups, authorizing them to use the gMSAs.

playbook_createGMSA.yml

- name: Create gMSAs and Security Group Interactively
  hosts: domaincontroller
  gather_facts: true # Enabled to use the ansible_domain magic variable

  # Load variables from an external file
  vars_files:
    - gmsa_vars.yml
  
  handlers:
    - name: Grant SELF RPWP on SPN for new/changed gMSAs
      ansible.windows.win_powershell:
        error_action: stop
        parameters:
          GmsaName: "{{ item }}"
        script: |
          param([string]$GmsaName)
          # Normalize 'name$' vs 'name'
          if ($GmsaName.EndsWith('$')) { $GmsaName = $GmsaName.TrimEnd('$') }

          $dn = (Get-ADServiceAccount -Identity $GmsaName -ErrorAction Stop).DistinguishedName

          # Only add if not already present; idempotent
          $aclText = (dsacls $dn) 2>&1
          if ($aclText -notmatch '^\s*SELF\s+RPWP\s+servicePrincipalName\s*$') {
            dsacls $dn /G "SELF:RPWP;servicePrincipalName" | Out-Null
            $Ansible.Changed = $true
          } else {
            $Ansible.Changed = $false
          }
      loop: "{{ gmsa_changed | default([]) }}"
      when: gmsa_changed is defined and gmsa_changed | length > 0

  tasks:
    - name: Create KDS root key if not present
      ansible.windows.win_powershell:
        error_action: stop
        script: |
          $Ansible.Changed = $false
          if (-not (Get-KdsRootKey)) {
            Add-KdsRootKey -EffectiveTime ((Get-Date).AddHours(-10))
            $Ansible.Changed = $true
            }

    - name: Ensure AD Security Group exists
      microsoft.ad.group:
        identity: "{{ item.principals_group }}"
        scope: Global
        state: present
      loop: "{{ sql_environments }}"

    - name: Add SQL Server Computer Accounts to their respective groups
      microsoft.ad.group:
        name: "{{ item.0.principals_group }}"
        members:
          add: "{{ item.1 }}$"
      with_subelements:
        - "{{ sql_environments }}"
        - servers

    - name: Create the gMSAs
      microsoft.ad.service_account:
        identity: "{{ item.1 }}"
        dns_hostname: "{{ item.1 }}.{{ ansible_domain }}"
        allowed_to_retrieve_password:
          set: "{{ item.0.principals_group }}"
      with_subelements:
        - "{{ sql_environments }}"
        - accounts
      register: gmsa_create
      notify: Grant SELF RPWP on SPN for new/changed gMSAs

    - name: Build list of gMSAs that were created/changed
      ansible.builtin.set_fact:
        gmsa_changed: >-
          {{
            (gmsa_create.results | selectattr('changed', 'equalto', true)
                                  | map(attribute='item')
                                  | map('last')   
                                  | list)
          }}

When you combine the playbook above with a var file, like the one below, you can provision multiple gMSAs at once, ensuring they all have the correct permissions and that the correct servers have permission to use them.

gmsa_vars.yml

---
sql_environments:
  - principals_group: Prod_SqlServers
    servers: 
      - PRODSQL1
    accounts:
      - svc_Prod_SqlEng
      - svc_Prod_SqlAgt
  - principals_group: Dev_SqlServers
    servers:
      - DEVSQL1
    accounts:
      - svc_Dev_SqlEng
      - svc_Dev_SqlAgt

Running the example playbook, with these variables, would create 4 gMSAs, 2 for each server (PRODSQL1 and DEVSQL1).  It would also create the requested groups, Prod_SqlServers and Dev_SqlServers, if they don't already exist.

Preventing NTLM Failback

With the right permissions in place, you can prevent SQL Server from failing back to the less secure NTLM authentication mechanism.  Keep an eye on the SQL Server error log, especially after any restarts, and make sure you no longer see 0x200b errors.

Conclusion

So, while the creation of SPNs for gMSAs is simplified out of the box, they aren't fully automated.  However, using examples like the one above, you could build this step into your end-to-end automation when creating new SQL Server installations and skip the SSPI or "The SQL Server Network Interface library could not register the Service Principal Name" errors.  This is the sort of thing I teach in the Ansible for SQL Server DBAs course in greater detail.

What about you?  Have you made the shift from using standard domain service accounts to gMSAs, or is it a mix of both types?