none
SPN's for Named SQL Instances

    Question

  • Before I log this out with Microsoft, would anyone happen to know whether use of SPN’s on named SQL Instances is bad practice from a Kerberos perspective?

    We had a SCOM issue earlier today whereby all of our local & mgmt server consoles stopped working.  We then discovred that the SQL application logs were full of this error:  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 172.xx.xx.xxx]

    I then put my Sherlock cap on and discovered that one of my SPN's for the SQL Service had been deregistered:  The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/DC1-SCOMDB.live.co-op.local:52809 ] for the SQL Server service.

    After re-registering the SPN my environment sprung back into life. 

    I just read some comments in a TechNet article that suggest Kerberos does not like SPN’s associated with named SQL instances, and so it falls back to NTLM.  So in summary......

    We have question marks over this config:
    MSSQLSVC/ServerName:InstanceName domain\account
    MSSQLSVC/FQDN:InstanceName domain\account

    And was wondering whether we should we be using a config similar to this instead:
    MSSQLSVC/ServerName:PortNumber domain\account
    MSSQLSVC/FQDN:PortNumber domain\account

    Thursday, March 08, 2012 7:29 PM

Answers

  • From OpsMgr documentation:

    When you create an SPN for a clustered SQL Server, you must specify the virtual name of the SQL Server Cluster as the SQL Server computer name.
    • To create an SPN for the NetBIOS name of the SQL Server use the following command: setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>
      noteNote
      The command to register an SPN for a SQL Server named instance is the same as that used when registering an SPN for a default instance except that the port number should match the port used by the named instance.

    http://technet.microsoft.com/en-us/library/bb735885.aspx


    - Daniele Microsoft MVP System Center Cloud and Datacenter Management http://nocentdocent.wordpress.com Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. This posting is provided “AS IS” with no warranties, and confers no rights.

    Friday, March 09, 2012 12:14 PM
    Moderator

All replies

  • From OpsMgr documentation:

    When you create an SPN for a clustered SQL Server, you must specify the virtual name of the SQL Server Cluster as the SQL Server computer name.
    • To create an SPN for the NetBIOS name of the SQL Server use the following command: setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>
      noteNote
      The command to register an SPN for a SQL Server named instance is the same as that used when registering an SPN for a default instance except that the port number should match the port used by the named instance.

    http://technet.microsoft.com/en-us/library/bb735885.aspx


    - Daniele Microsoft MVP System Center Cloud and Datacenter Management http://nocentdocent.wordpress.com Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. This posting is provided “AS IS” with no warranties, and confers no rights.

    Friday, March 09, 2012 12:14 PM
    Moderator
  • I too am having problems with this and I also think the documentation is misleading.

    Can someone please give an example based on a server called sql.test.org using service account test\sql with a named instance of sqlinstance for both NetBIOS and FQDN

    I am trying SETSPN - S MSSLSvc/SQL:SQLINSTANCE TEST\SQL

    I too am wondering if it should be

    SETSPN - S MSSLSvc/SQL:1433 TEST\SQL


    prd


    Tuesday, September 10, 2013 9:02 PM
  • Hi everyone,

    Peter did you get any answer for your scenario?

    it might also help me

    thanks

    • Proposed as answer by sandbrock Tuesday, July 01, 2014 8:48 PM
    Thursday, February 06, 2014 5:44 PM
  • I worked it out. SETSPN - S MSSLSvc/SQL:SQLINSTANCE TEST\SQL Was correct. Kerberos can be very problematic so the SPNs need to be correct for all service accounts. If you have configured several accounts then SPNs for each eg SSRS, SSAS etc need to be correct. Then in a SharePoint env you need to setup constrained delegation for the accounts used to run web application pools. Tools to troubleshoot Wireshark Application logs Iis logs Uls logs Klist Uls viewer Run wire shark on the desktop and the web front end Trigger the error Stop wireshark on both systems and analyse the logs If you have Kerberos issues the error will probably be on the WFE. THIS IS AN ADVANCED TOPIC!

    prd

    Thursday, February 06, 2014 6:12 PM