Skip to content
← Back to blog
5 min read

I built a Kerberos SPN tool because setspn.exe made me feel stupid

  • powershell
  • sql-server
  • kerberos
  • dba

Here's the thing about Kerberos authentication errors in SQL Server: they always happen on a Tuesday. Specifically a Tuesday afternoon. Specifically when you have somewhere else to be.

Users start getting "Cannot generate SSPI context." You start getting messages. You fire up setspn -L domain\sqlsvcaccount and stare at the output trying to remember which SPNs are supposed to be there and which ones are duplicates from that server rename six months ago that nobody documented. You fix something. It either gets better or gets worse. It takes you an hour to find out which.

I got tired of that particular experience. Hence: SqlSpnManager.

The funny part about how this happened

SqlSpnManager was the first of my PowerShell modules to land on PowerShell Gallery — which makes it sound like it was the first one I built. It wasn't even close. I had four or five modules going at home before this one was ready to ship. The others aren't done yet. SqlSpnManager made it out first because Kerberos kept winning, and eventually I got motivated enough to do something about it properly.

What setspn.exe actually gives you

setspn.exe is the built-in Windows tool for managing Service Principal Names. It works. It also gives you this kind of output:

Registered ServicePrincipalNames for CN=sqlsvc,OU=Service Accounts,DC=corp,DC=local:
        MSSQLSvc/sqlserver01.corp.local:1433
        MSSQLSvc/sqlserver01.corp.local
        MSSQLSvc/sqlserver01:1433
        MSSQLSvc/sqlserver01

That's one instance. Now multiply it by however many SQL Server instances you're managing. Then factor in that you need to cross-reference what's registered against what should be registered based on the actual port configuration. Then add in the named instances. Then add in the possibility of duplicate SPNs (which cause their own authentication failures) and missing SPNs (which cause different authentication failures).

You're basically doing a diff in your head between what the tool printed and what you know about your environment. That's not a tool; that's a reading comprehension exercise.

How SqlSpnManager approaches it differently

The module runs a pipeline:

Step 1: Discover the service account.

Get-SqlSpnAccount -ComputerName sqlserver01

This identifies the account running the SQL Server service — the account whose SPNs need to exist in Active Directory.

Step 2: Discover the infrastructure.

Get-SqlSpnInfrastructure -ComputerName sqlserver01

This finds the actual SQL Server instances on the target machine, along with the ports they're listening on. Named instances, default instances, custom ports — it reads what's actually running, not what you think is running.

Step 3: Build a plan.

New-SqlSpnPlan -ComputerName sqlserver01

This is where it gets different. Instead of immediately running setspn -A and hoping for the best, the module produces a plan: a list of SPNs that should exist, compared against what actually exists in Active Directory. Missing registrations. Duplicates. Anything that doesn't match.

Step 4: Test the plan.

Test-SqlSpnPlan -Plan $plan

Validates the plan before a single SPN is touched. Catches problems — like trying to register an SPN that already belongs to a different account — before they create more problems.

Step 5: Execute.

Invoke-SqlSpnExecutionEngine -Plan $plan

Only now does anything actually change in Active Directory.

The interactive entry point

If you don't want to chain all that yourself, Start-SqlSpnManager walks you through it interactively:

Start-SqlSpnManager

It prompts for the target, the scenario (default instance, named instance, a specific port), and then shows you the plan before asking for a final confirmation. If you're scripting this unattended, use the underlying functions directly and skip the prompts entirely.

The handoff problem

There's a conversation that happens in almost every Windows shop: the DBA knows the SQL instances cold, but doesn't have AD write rights. The AD admin has the rights but has no idea what SPNs need to exist or why. So the DBA says "I need these SPNs registered." The AD admin says "which ones?" The DBA sends a list. The AD admin comes back: "something's wrong." The DBA says "what's wrong?" The AD admin says "I don't know." And it goes from there.

Export-SqlSpnRegistrationScript exists for exactly that moment.

Export-SqlSpnRegistrationScript -Plan $plan -Path C:\Temp\register-spns.ps1

Run the plan through the module, export the result as a script, hand it to whoever has the AD rights. They get something reviewable and runnable. You get SPNs registered without needing a standing AD delegation or a long back-and-forth about what MSSQLSvc/hostname:1433 means.

It's not a workaround. It's just how the handoff should work.

What it won't do

SqlSpnManager doesn't touch anything it can't verify first. If it can't discover the infrastructure, it won't guess at SPN names. If the plan has a conflict it can't resolve, it surfaces the conflict and stops. It's not trying to be clever; it's trying to make the right action obvious and the wrong action hard.

It also doesn't handle MSDTC SPNs. That's a separate problem and a separate fight for another day.

Installing it

Install-Module SqlSpnManager

The website — including a walkthrough of the full pipeline with more context on the AD permissions model — is at sql-spn-manager.com.

Two years ago, I would've just fumbled through setspn the same way I always did and called it good. Building this forced me to actually understand the SPN model well enough to codify it. That part was worth the time even before anyone else used it.

What's the worst Kerberos situation you've had to dig out of? I'm genuinely curious whether anyone else has the "service account was moved and nobody told IT" story. I definitely do.