locked
Target principal name is incorrect - cannot generate SSPI context RRS feed

  • Question

  • First off, yes, I've looked at numerous articles on this subject and haven't yet found someone with a similar situation and none of the resolutions I've found seem applicable.

    My situation:  Server 2008R2 Enterprise with SQL 2012 Standard.  I have two instances running on this server, DEV and TEST.  I do not have a default (unnamed) instance.  This is one of the differences between my situation and every other article I've looked at.  I AM able to connect remotely to the DEV instance, but the TEST instance give me the SSPI error.  To take it a step further, the SQL services aren't running under domain credentials; they're running under local credentials.  These two issues don't match anything I've found on the subject so far.

    I ran "setspn -L" against the server's machine account (since the SQL services are local, not domain) and here's what I got:

    MSSQLSvc/[server FQDN]:49592
    MSSQLSvc/[server FQDN]:TEST
    MSSQLSvc/[server FQDN]:49487
    MSSQLSvc/[server FQDN]:DEV
    WSMAN/[server]
    WSMAN/[server FQDN]
    TERMSERV/[server FQDN]
    TERMSERV/[server]
    RestrictedKrbHost/[server]
    HOST/[server]
    RestrictedKrbHost/[server FQDN]
    HOST/[server FQDN]

    The server is of course a domain member, and we only have a single domain, so there's no cross-domain/forest stuff going on.

    Remotely, SA authentication works against both instances.  Locally, both SA and Windows authentication work against both instances.  Remotely, Windows authentication works against the DEV instance, but not against the TEST instance, returning the error "The target principal name is incorrect.  Cannot generate SSPI context."

    Both database have identical settings in the SQL Config Mgr (Shared Memory and TCP/IP enabled).  Both instances are set to allow remote logon.

    The security for the various services:

    SQL Server (DEV) = NT Service\MSSQL$DEV
    SQL Server (TEST) = NT Service\MSSQL$TEST
    SQL Server Agent (DEV) = NT Service\SQLAgent$DEV
    SQL Server Agent (TEST) = NT Service\SQLAgent$TEST
    SQL Server Browser = Local Service

    On both instances, the query "exec xp_readerrorlog 0,1,'could not register the Service Principal Name',Null" comes back with two entries from when the system was installed, saying it was unable to register the SPN.  That makes sense, because SQL isn't running with domain credentials, although the SPNs ARE apparently in AD (see above).

    On both instances, the query "select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid" returns the same thing - "shared memory", "NTLM".  Basically, I can find no configuration difference between the two instances.

    Anyone got any ideas?

    Wednesday, September 11, 2013 7:24 PM

Answers

  • I removed and readded the client to the domain.  It all seems working.  Sql repair was not the solution
    • Marked as answer by Sofiya Li Thursday, September 19, 2013 8:50 AM
    Saturday, September 14, 2013 4:01 PM
  • I was getting same issue after domain controller restarted due to service break. I tried restarting all necessary services, sql, iis, timer then executing  w32tm /resyn but no luck.

    So finally I restarted DC server, and everything started working.

    • Marked as answer by Sofiya Li Thursday, February 27, 2014 9:17 AM
    Thursday, February 27, 2014 7:26 AM

All replies

  • Hi Todd,

    According to your description, we need to make sure the typed server name is right, and verify if your windows account is valid when you connect to the test instance remotely by using Windows authentication. You can refer to the following steps for checking the login name.
    1.Log in the test instance remotely by using sa authentication.
    2. Enter the security and check if the windows account is valid.

    In addition, there is more details about how to troubleshoot the "Cannot generate SSPI context" error message, you can review the article.
    http://support.microsoft.com/kb/811889

    Thanks,
    Sofiya Li
    If you have any feedback on our support, please click here.


    Sofiya Li
    TechNet Community Support


    • Edited by Sofiya Li Thursday, September 12, 2013 8:29 AM
    Thursday, September 12, 2013 8:28 AM
  • Hi,

    I'd try deleting and recreating the SPN's for the TEST instance.  Have you checked that the port number is correct?


    Thanks, Andrew

    Thursday, September 12, 2013 8:58 AM
  • I know that both of those things are correct.  I'll launch SQL Server Management studio and enter "[servername]\dev" and it'll connect, using my Windows credentials.  Then I'll click "connect" again and simply backspace over "dev" and enter "test", changing nothing else, and I get the error.  So there's no doubt that my user account is okay and that I'm entering the server name correctly.  Plus, if it were a problem with my user account, I wouldn't think it would succeed using that account on both instances locally.

    I'll take a look at that article - thanks.

    Thursday, September 12, 2013 1:13 PM
  • Well, I've done absolutely nothing with port numbers other than the defaults.  When both instances were installed, all the defaults on that kind of thing were accepted, and when I'm trying to connect with SSMS I'm not manually overriding any port defaults.

    In the TCP/IP configuration for the DEV instance, "TCP Dynamic Ports" is 49487 and "TCP Port" is blank.  For the TEST instance, those values are 49592 and blank, respectively.  I'm assuming that means that SQL is listening on the default port for both instances.

    Thursday, September 12, 2013 1:17 PM
  • I'm having the exact same problem. 

    I have Sql 2012 running on a Server 2008 R2 instance.  This has all been up and running for the last year with no issues.  On a different machine, also running Server 2012, I can now no longer connect through management studio.  I have other services that run nightly on the client box and seem to reach the SQL server with no problems.

    This just started suddenly.

    Saturday, September 14, 2013 3:02 PM
  • I set up another client machine, same domain, same networking (both are actually VMs on the same host), and this one connects with no problems.  It was a machine I had lying around, so the Mgmt Studio installation was not fresh today, but was SQL 2012.  It connects no problem.

    So it's the client machine.  Running SQL repair on the bad installation now.  I hope it was not a recent patch that caused this.

    Saturday, September 14, 2013 3:31 PM
  • I removed and readded the client to the domain.  It all seems working.  Sql repair was not the solution
    • Marked as answer by Sofiya Li Thursday, September 19, 2013 8:50 AM
    Saturday, September 14, 2013 4:01 PM
  • Todd,

    On both instances, the query "select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid" returns the same thing - "shared memory", "NTLM".

    If NTLM is being returned, then the protocol fell back and isn't using Kerberos but NTLM. So Kerberos isn't even being used in this case.

    On both instances, the query "exec xp_readerrorlog 0,1,'could not register the Service Principal Name',Null" comes back with two entries from when the system was installed, saying it was unable to register the SPN. That makes sense, because SQL isn't running with domain credentials, although the SPNs ARE apparently in AD (see above).

    They don't have to be running as domain accounts to register SPNs, though it's best practice to do so. Even if they were running under domain accounts, the WriteServicePrincipalName and ReadServicePrincipalName security options would need to be given to that account (or computer object in this case) so that it can automatically register them upon startup. Just FYI.

    Troubleshooting:

    Since none of your connections seem to be made using Kerberos, you could ask your windows admins to remove the SPNs. Alternatively, before they are removed, attempt to connect to the instance using the port numbers instead of the name by using the following format: ServerName,Port

    Before removing the SPNs it would also be possible to load wireshark or some other packet capturing software to check the actual failure reasons by inspecting the communications packets.

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, September 14, 2013 4:53 PM
  • I was getting same issue after domain controller restarted due to service break. I tried restarting all necessary services, sql, iis, timer then executing  w32tm /resyn but no luck.

    So finally I restarted DC server, and everything started working.

    • Marked as answer by Sofiya Li Thursday, February 27, 2014 9:17 AM
    Thursday, February 27, 2014 7:26 AM
  • Sofia, thanks for hint about time service.
    In my case the time difference between DC and farm servers led to such an error. 

    After the farm servers were bound to the domain time service, everything worked correctly.
    NET TIME \\dc_controller_name /SET /YES

    Monday, October 15, 2018 1:01 PM