What should my SPN entries look like for each SQL instance?

Question

I'm finding contradictory information for how exactly to format SPNs (Service Principle Names) to get the proper Kerberos connections, and how many I need for each SQL instance.

This 2017 MS document contains the following:

Beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication on TCP/IP, named pipes, and shared memory. The supported SPN formats for named and default instances are as follows.

• Named instance: MSSQLSvc/FQDN:[port|instancename]
• Default instance: MSSQLSvc/FQDN:port|MSSQLSvc/FQDN

The new SPN format does not require a port number. This means that a multiple-port server or a protocol that does not use port numbers can use Kerberos authentication.

I took this last paragraph to mean that I only need a single entry, one of the following:

• Named instance: MSSQLSvc/sqlbox1.mydomain.org/instance2
• Default instance: MSSQLSvc/sqlbox1.mydomain.org

That seems to contradict this older (2011) MS document, not just about the port number, but also concerning what name to use:

To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, you must create an SPN for both the NetBIOS name and the FQDN.

When I look at the SPNs that already exist in my environment, I see a wide variety of combinations, some servers have up to 4 entries:

• MSSQLSvc/sqlbox1
• MSSQLSvc/sqlbox1:1433
• MSSQLSvc/sqlbox1.mydomain.org
• MSSQLSvc/sqlbox1.mydomain.org:1433

Even MS's own Kerberos configuration manager seems to want to generate the last two versions (with appropriate obfuscation):

Similarly for existing named instances, I see a weird mix, some of them almost certainly invalid:

• MSSQLSvc/sqlbox1:1522
• MSSQLSvc/sqlbox1:instance2
• MSSQLSvc/sqlbox1.mydomain.org:1522
• MSSQLSvc/sqlbox1.mydomain.org:instance2
• MSSQLSvc/sqlbox1.mydomain.org/instance2
• MSSQLSvc/sqlbox1.mydomain.org:1522:instance2

So what should my DSNs actually look like, for both default and named instances, if I just use TCP in my environment?

Should I include the port, or not? Or include one with the port and one without?

Use the FQDN only, or do I need the entries with just the Netbios name? Or would that only be if we were using named pipes (which we are not)?

(For context, we run SQL 2005 through 2014, some clustered, others standalone. Connectivity is via TCP only, named pipes is disabled in config manager. We will be fixing/creating these manually instead of allowing the SQL service account to create them upon server start.)

Answer 1

If you're only using TCP/IP to connect to your instances, you only need the ports specified. The Instance Names are used when connecting to the SQL Instances via the Named Pipes protocols. Sadly the MS article doesn't come right out say which format is required for which protocol, but it is derived from (many tests in my environment) and the following MS article sentance:

For named pipes and shared memory connections, an SPN in the format MSSQLSvc/FQDN:instancename is used for a named instance and MSSQLSvc/FQDN is used for the default instance.

Regarding FQDNs vs NETBIOS names, I will recommend FQDNs as they aren't as prone to problems if you run up against random DNS server issues.

Lifted from my blog post on the matter, formats should look as follows:

The source reference from MS can be found here.

Now to make your Network Admin's Day (e.g OU configuration that allows for Self Registering SPNs)

Your Network Admin can create an OU on the domain which contains all your SQL Server Service accounts that can be configured in such a manner that the Service Account can create an SPN for itself and itself alone. The method is mainly following Ryan Reis's blog, but has some slight tweaks so that over-grants are not performed.

This process describes the creation of an OU on the domain that allows accounts within it to self-register their own SPNs:

1. As an account with elevated rights on the domain, open ADSI Edit (adsiedit from the command prompt)
2. Right-Click on ADSI Edit -> Connect to...
3. Connect to Default naming context
4. Navigate to/Create the OU container holding the service accounts you wish to grant SPN rights to
5. Right-Click on the OU -> Properties
6. Click on the Security Tab
7. Click the Advanced button
8. Highlight SELF and click Edit... or if the SELF special user does not show up in the list of Group or User names, click Add... and enter SELF for the object name
9. Click the Properties tab
10. Select Descendant User objects from the drop down list next to Apply to: Note: This is the slight adjustment to the steps outlined in Ryan's blog post for the reasons better outlined by this ServerFault/StackExchange post.
11. Check the Allow box next to the following:
    • Read servicePrincipalName
    • Write servicePrincipalName
12. Click Ok (on permissions entry window)
13. Click Ok (on Advanced Security Setings window)
14. Click Ok (on OU properties window)
15. Add service accounts running SQL Server services to the OU
16. (Optionally) Restart SQL Server Services running under said account(s)
17. Enjoy Treats

After following the above steps, the OU container in question is now configured such that any account added to it will be able to register and delete SPNs for itself and itself alone.  This is exactly the right amount of permissions as these accounts will be unable to trample over SPNs registered by other accounts.

The purpose of restarting SQL Server in step 16 is to ensure the SPNs are registered as expected.  SQL will try to remove any registered SPNs on shutdown and add them on Startup, so the restart is really only needed if no SPNs currently exist for said SQL Server service.

The final note on this approach is that if you are running SQL Server in a traditional Failover Clustered Instance (FCI) configuration, it is NOT Recommended to add this instance's service account to this OU, per KB 2443457.

I really need to post Part 2 of my Kerberos series...

Answer 2

When the SQL Server service creates SPN, it creates two for each instance. This is the format it uses.

Default Instance:

MSSQLSvc/servername.domain.com
MSSQLSvc/servername.domain.com:1433

Named Instance:

MSSQLSvc/servername.domain.com:54321
MSSQLSvc/servername.domain.com:instancename

For your named instances, if creating SPNs manually, you will need to have a static port instead of the default dynamic port.

Answer 3

As at 2022-11-30 there seems to be a recently introduced bug (very likely related to KB5019964 and the issues described here: https://mspoweruser.com/kerberos-authentication-issues-due-to-november-updates/) where older operating systems cannot connect to a SQL server that has port numbers in the SPN due to Kerberos errors: "The target principal name is incorrect. Cannot generate SSPI context".

In our testing, we found the issue occurs if the client OS is Windows 2012 or older, including Windows 10. (We did not have Windows 11 available to test). There is no problem when connecting from Windows 2016 or later.

The temporary fix is to delete any SPNs that contain port numbers. We found this to be true for both default (1433) and named instances. For our named instance, we are using this style SPN successfully:

    MSSQLSvc/SERVERNAME:INSTANCENAME
    MSSQLSvc/SERVERNAME.FQDN.GOES.HERE:INSTANCENAME

Hopefully Microsoft come up with a proper fix to their broken patch.