locked
The SQL Server Network Interface library could not deregister the Service Principal Name. RRS feed

  • Question

  • Hi, 

    We have installed on two brand new Windows Server 2012 R2, one for TFS 2015 application tier and one for SQL 2014 with SP1.

    We copy the DB from a TFS 2012 and upgraded it to TFS 2015.

    Everything worked fine for almost 10 days, than users started to get the TF53001: The database operation was canceled by an administrator.

    We look at the events on the server which host TFS 2015 application tier.

    The first TF53001 was associated to the exception message: Target principal name is incorrect, Cannot generate an SSPI Context.

    After trying to find a solution for an hour, since we have 300 users which are waiting for the service to come back, we decided to reboot both servers.

    During the shutdown of the SQL server, we found in the log the following messages: The SQL Server Network Interface library could not deregister the Service Principal Name (SPN) [MSSQL.Svc/our.server.name] for the SQL Server service. Error: 0xffffffff, state 63. Administrator should deregister this SPN manually to avoid client authentication errors.

    Since the reboot (last friday), both servers look to be working fine, but we don't know what cause the problems and how to prevent the issue to reappear again?

    After publishing the question on the TFS Forum, they think it's more an SQL issue.

    Regards,

    Huy

    Wednesday, September 23, 2015 3:17 PM

Answers

All replies

  • Hi Huy,

    Administrator should deregister this SPN manually to avoid client authentication errors.

    If the SQL Server service runs under the Local System, Local Service, or Network Service accounts the SPN goes on the computer object in AD and SQL will have the permissions it needs by default to register and unregister its SPNs. 

    The above error indicates that it cannot unregister the SPN so either SQL Server is using a domain account to run the service or someone tampered with the permissions on the computer object in AD. To solve this issue, grant Validate write to service principal name for startup account of SQL Server using Active directory user and computers snap in.

    Alternatively, you can use SETSPN.EXE to delete the SPN and change your SQL Server to run under a built-in account (Local System, Local Service, or Network Service).

    Reference:
    http://mssqlwiki.com/2013/
    http://www.ryanjadams.com/2013/12/active-directory-sql-server-questions-answered/#axzz3mcNcvrYh

    Thanks,
    Lydia Zhang

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


    Lydia Zhang
    TechNet Community Support








    Thursday, September 24, 2015 2:30 AM
  • Solution 

    Therefore, you must implement a solution to that the SPN is created for your SQL Server instance where you want to use the Kerberos protocol.


    Method 1 : The method recommended by Microsoft Support. You can give in Active Directory rights below to the service account of SQL Server
    - Read servicePrincipalName 
    - Write servicePrincipalName

    Method 2 : You can also give him the rights manually using the tool SetSPN.(http://msdn.microsoft.com/fr-fr/library/cc280459.aspx )

    For SQL server Standalone 

    SetSPN -A MSSQLSvc/<ComputerName>.<DomainName>:<port> <AccountName>

    So for a multi-server instance you must configure the SPN for each instance, for each instance of SQL Server usefulness port TCP / IP only. For the port of the proceeding open SQL Server Configuration Manager>> Right click the instance>> TCP / IP protocol (default port)

    Pour un cluster

    SetSPN -A MSSQLSvc/<virtualName>.<DomainName>:<port> <AccountName>
    SetSPN -A MSSQLSvc/<virtualName>.<DomainName> <AccountName>

    For an instance of SQL Server cluster, you must use the FQDN of the virtual SQL server. You must also configure the SPN with a port and a second SPN without the port.


    Verification

    Then you can confirm that the operation went smoothly with the following command:
    SetSPN -L <AccountName>

    SQL server side to check, you can use the DMV sys.dm_exec_connections with the column auth_scheme :
    select auth_scheme from sys.dm_exec_connections where session_id=@@spid
    If kerberos is used, you should see "KERBEROS".


    Please click "Mark As Answer" if my post helped.

    Thursday, September 24, 2015 2:31 AM
  • http://mssqlwiki.com/2013/

    Hi Lydia,

    Thanks for the reply, for some precision, we use Windows Virtual Accounts, e.g. for Default instance of the Database Engine service, it is the default NT SERVICE\MSSQLSERVER.

    If we look in the SQL Log file, we can see that

    The SQL Server Network Interface successfully registered the Service Principal Name (SPN)[MSSQLSvc/our.server.name:1433] for the SQL Server service.

    The SQL Server Network Interface successfully registered the Service Principal Name (SPN)[MSSQLSvc/our.server.name] for the SQL Server service.

    Therefore we try point #4 of the above link to see there are duplicates, the output file was empty.

    So, we tried "setspn -X" and we saw that there is no duplicate.

    Currently both servers are running fine.

    Therefore we try a second shutdown, the SQL server still can't deregister the service.

    Is the TFS error we got once really related to this?

    Huy






    • Edited by H_V_L Thursday, September 24, 2015 7:32 PM
    Thursday, September 24, 2015 7:14 PM
  • Hi Huy,

    Target principal name is incorrect, Cannot generate an SSPI Context

    The above error message indicates a Kerberos authentication failure issue between the two servers, which is related to SPN. Could you please check that if virtual account has been granted the "Read servicePrincipalName" and "Write servicePrincipalName" permissions?

    There are some troubleshooting blogs about the above error for your reference.
    http://blogs.msdn.com/b/meer_alam/archive/2015/05/10/the-target-principal-name-is-incorrect-cannot-generate-sspi-context.aspx
    https://support.microsoft.com/en-us/kb/811889


    Thanks,
    Lydia Zhang

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


    Lydia Zhang
    TechNet Community Support



    Friday, September 25, 2015 8:14 AM
  • Thanks for the reply, but we haven't found the root cause of the original problem.

    Since the last reboot everthing is working fine, so we will wait to see if the problem re-appear.

    Regards,

    Huy.

    Friday, October 9, 2015 1:56 PM