7

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

enter image description here

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

  • 1
    Any particular reason that you want to manage SPNs yourself rather than let SQL (and its service account) do that for you? – Nic Jul 18 '17 at 14:02
  • 1
    as far as SPNs go, if it works, does it matter what the format is? I second @Nic comment – Bob Klimes Jul 18 '17 at 14:09
  • @Nic Because that would require granting several dozen service accounts new Active Directory rights, our Active Directory admin said a one-time manual add is easier to manage. Also found some links saying funny things can happen when SPNs try to sync across multiple domain controllers when they are dynamically added/removed upon start/stop/cluster failover. All that said, let me ask this: when you do allow the service account to add the SPN upon startup, what does it look like? A single entry with FQDN and port? Or without port? Or two entries? – BradC Jul 18 '17 at 14:09
  • @BobKlimes Well, some of them don't work, that's the problem. I guess there's no harm in having more entries than necessary, but just trying to understand which ones actually do the job. – BradC Jul 18 '17 at 14:10
  • 1
    @BradC I definately had issues with cluster failover and multiple DCs. Those were my only SPNs created manually. – Bob Klimes Jul 18 '17 at 14:13
5

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:

enter image description here

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

  • It's not Descendant User Objects, it's Descendant Computer Objects. – Isaac Kleiman Feb 5 at 18:50
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.