none
Why no kerberos auth sessions

    Question

  • I inherited a 3-node 2014 AlwaysOn AG cluster.  Each node has a default instance plus six named instances.  All instances are configured with non-default ports.  I'm running SSMS on my workstation connecting to all the instances on the three nodes. I was unable to connect to one default instance on Node A and a named instance on Node B just using servername or servername\instancename as appropriate.  I get the infamous error "The target principal name is incorrect.  Cannot generate SSPI context."  However, I was able to connect to all other instances.  For the two that I was unable to connect to, I added the non-default port in the connection in SSMS like "servername\instancename, port#", and was able to connect.  BTW, the browser service is running on all the target servers.



    I ran Kerberos Configuration Utility on all three nodes, and all SPN entries report either "Missing" or "Misplaced" status.  The two instances I was unable to connect to are the ones that have the "Misplaced" entry.



    I also ran the following query from my workstation against all the instances, and I don't see any Kerberos sessions listed:

    SELECT
        s.session_id
      , c.connect_time
      , s.login_time
      , s.login_name
      , c.protocol_type
      , c.auth_scheme
      , s.HOST_NAME
      , s.program_name FROM sys.dm_exec_sessions s
      JOIN sys.dm_exec_connections c
        ON s.session_id = c.session_id


    So why would I not be able to connect to only two of the instances unless I add the port #?

    Should I expect to see Kerberos connections if I fix all those "Missing" and "Misplaced" SPN entries?

    Friday, May 11, 2018 10:41 PM

All replies

  • Perhaps reading that article helps you to solve the problem

    https://blogs.msdn.microsoft.com/meer_alam/2015/05/10/the-target-principal-name-is-incorrect-cannot-generate-sspi-context/

    /*Checking the msdcs for the SQL Server, we found a wrong entry being populated for the SQL Server machine. So we corrected the entry in the logon server DC (for the client) and then replicated the change to all involved DCs. We first had problem in getting the zone information so we re-created the zone and received the updates of the zone. This finally did the trick for us, the issue now got resolved at this point, to the completion. All users are now able to connect over Kerberos.*/

     

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 13, 2018 6:21 AM
  • Hi District,

    Since you can connect to other instances with servername or servername\instancename as appropriate, I would suggest you check if the configuration of two instances are same with others.

    Then have a try following options:

    1. Granting read/write servicePrincipalName permissions to the service account using ADSI Edit, as described in https://support.microsoft.com/en-us/kb/811889

    2. Removing the SPNs that previously existed on the SQL Server computer account (as opposed to the service account) using

    setspn -D MSSQLSvc/HOSTNAME.domain.name.com:1234 HOSTNAME

    where 1234 was the port number used by the instance.

    3. Login to both your SQL Box and your client and type:

    ipconfig /flushdns
    nbtstat
    -R

    4. Verify that the IP that is resolved when pinging the SQL Server is the same as the one in the Configuration Manager. To check, open SQL Server Configuration Manager and then go to SQL Server Network Configuration > Protocols for MSSQLServer > TCP/IP. Make sure TCP/IP is enabled and in the IP Addresses tab, make sure that the IP that the server resolves to when pinging is the same one here.

    Check if this issue reproduced.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 14, 2018 9:08 AM