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.)