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.)
Best Answer
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:
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:
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...