none
SQL SPN

    Question

  • I had a few general questions about SQL & SPNs:

    1. Do I have to register the SPN of SQL?
    2. Does it matter if the Service is running under LocalSystem or a Domain user?
    3. Despite the fact that I registered my SPNs I nevertheless see in the SQL log and error stating: The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SERVERNAME:PORTNUMBER ] for the SQL Server service. Windows return code: 0x2098<c/> state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.           Is this OK? Is there a reason?

    Wednesday, February 1, 2017 7:11 AM

All replies

  • Hello Reuvy,

    Kerberos authentication is more secure and has its various advantages over NTLM and if you want to use Kerberos, you need to register the SPN.

    SPNs are registered for Windows accounts, with that in domain environments it's a best practice using Domain Users as service accounts. It's much easier to grant permissions on the network shares and resources to the domain accounts.

    If your SQL Server service accounts is lack of some permissions on the AD then you get that error message, but that doesn't mean there's no SPN record for your SQL Server service account.

    Check this documentation please.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Wednesday, February 1, 2017 8:38 AM
  • Additional: There is a free tool available to check/manage SPN/Kerberos settings for SQL Server: Microsoft® Kerberos Configuration Manager for SQL Server®

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 1, 2017 8:49 AM
  • Hi Reuvy,

    Despite the fact that I registered my SPNs I nevertheless see in the SQL log and error stating: The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SERVERNAME:PORTNUMBER ] for the SQL Server service. Windows return code: 0x2098<c/> state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.           

    >>Is this OK? 

    It safe to ignore if you have manually register a valid SPN to the service account.

    >>Is there a reason?

    Yes. From what I see the message indicates the service account doesn’t have the permission to register SPN. The message should go away after you have granted read/write SPN permission to the service account.

    If you have any other questions, please let me know.

    Regards,
    Lin

    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.

    Wednesday, February 1, 2017 8:57 AM
    Moderator
  • Thanks. The question is if there is a difference is SQL is running under a domain account or under local system, since in the latter it nevertheless still tries to register the SPN, although under the local server machine name. Should I there nevertheless register the SPN?
    Wednesday, February 1, 2017 10:21 AM
  • From the documentation:

    "On a network that uses Kerberos authentication, an SPN for the server must be registered under either a built-in computer account (such as NetworkService or LocalSystem) or user account. SPNs are registered for built-in accounts automatically. However, when you run a service under a domain user account, you must manually register the SPN for the account you want to use."

    I think it's the best if you execute the following query and see the result yourself. It'll tell you if you are using Kerberos or NTLM.

    SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;



    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Wednesday, February 1, 2017 8:42 PM