What should my SPN entries look like for each SQL instance? 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.)
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:


As an account with elevated rights on the domain, open ADSI Edit (adsiedit from the command prompt)
Right-Click on ADSI Edit -> Connect to...
Connect to Default naming context
Navigate to/Create the OU container holding the service accounts you wish to grant SPN rights to
Right-Click on the OU -> Properties
Click on the Security Tab
Click the Advanced button
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
Click the Properties tab
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.
Check the Allow box next to the following:


Read servicePrincipalName
Write servicePrincipalName

Click Ok (on permissions entry window)
Click Ok (on Advanced Security Setings window)
Click Ok (on OU properties window)
Add service accounts running SQL Server services to the OU
(Optionally) Restart SQL Server Services running under said account(s)
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...