locked
Kerberos Authentication for SQL Server 2008 R2 RRS feed

  • Question

  • Hi,

    I am using SQL Server 2008 R2.

    The SPN created for MSSQL are as follows.

    MSSQLSvc/Testlabs-SP.testlabs.com

    MSSQLSvc/Testlabs-SP.testlabs.com:1433

    MSSQLSvc/Testlabs-SP

    MSSQLSvc/Testlabs-SP:1433

    Testlabs-SP.testlabs.com is my FQDN, Testlabs-SP is my ServerName and the SQL is using default instance.

    In the active directory for the SQL service account, under delegation tab i have set "Trust this user for delegation to any service (Kerberos only)"

    while debugging the Kerberos i got the following error. KDC_ERR_BADOPTION.

    The full details of the error is below. Pleas advise on how to solve this issue

    Log Name:      System
    Source:        Microsoft-Windows-Security-Kerberos
    Date:          11/2/2012 4:24:27 PM
    Event ID:      3
    Task Category: None
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      TESTLABS-DC.testlabs.com
    Description:
    A Kerberos Error Message was received:
     on logon session
     Client Time:
     Server Time: 8:24:27.0000 2/11/2012 Z
     Error Code: 0xd KDC_ERR_BADOPTION
     Extended Error: 0xc00000bb KLIN(0)
     Client Realm:
     Client Name:
     Server Realm: TESTLABS.COM
     Server Name: testlabs-dc$@TESTLABS.COM
     Target Name: testlabs-dc$@TESTLABS.COM@TESTLABS.COM
     Error Text:
     File: 9
     Line: efb
     Error Data is in record data.
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="Microsoft-Windows-Security-Kerberos" Guid="{98E6CFCB-EE0A-41E0-A57B-622D4E1B30B1}" EventSourceName="Kerberos" />
        <EventID Qualifiers="32768">3</EventID>
        <Version>0</Version>
        <Level>2</Level>
        <Task>0</Task>
        <Opcode>0</Opcode>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2012-02-11T08:24:27.000000000Z" />
        <EventRecordID>1617</EventRecordID>
        <Correlation />
        <Execution ProcessID="0" ThreadID="0" />
        <Channel>System</Channel>
        <Computer>TESTLABS-DC.testlabs.com</Computer>
        <Security />
      </System>
      <EventData>
        <Data Name="LogonSession">
        </Data>
        <Data Name="ClientTime">
        </Data>
        <Data Name="ServerTime">8:24:27.0000 2/11/2012 Z</Data>
        <Data Name="ErrorCode">0xd</Data>
        <Data Name="ErrorMessage">KDC_ERR_BADOPTION</Data>
        <Data Name="ExtendedError">0xc00000bb KLIN(0)</Data>
        <Data Name="ClientRealm">
        </Data>
        <Data Name="ClientName">
        </Data>
        <Data Name="ServerRealm">TESTLABS.COM</Data>
        <Data Name="ServerName">testlabs-dc$@TESTLABS.COM</Data>
        <Data Name="TargetName">testlabs-dc$@TESTLABS.COM@TESTLABS.COM</Data>
        <Data Name="ErrorText">
        </Data>
        <Data Name="File">9</Data>
        <Data Name="Line">efb</Data>
        <Binary>3015A103020103A20E040CBB0000C00000000003000000</Binary>
      </EventData>
    </Event>

    To make sure i did run the following command

    Select   
          auth_scheme
    from
    sys.dm_exec_connections where
    session_id = @@SPID

    it returns NTLM

    Is there anything wrong in my configuration, please advise on how to enable kerberos


    Ready for Action


    Saturday, February 11, 2012 8:37 AM

Answers

  • Hi Striking,

    To use Kerberos authentication with SQL Server requires both the following conditions to be true:

    • The client and server computers must be part of the same Windows domain, or in trusted domains.
    • A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

    If Kerberos cannot be used, Windows will fall back to Windows NT Challenge/Response (NTLM) authentication.


    Reference:
    Kerberos Authentication and SQL Server: http://msdn.microsoft.com/en-us/library/cc280744.aspx.
                                                                http://msdn.microsoft.com/en-us/library/cc280744.aspx.
    Register a Service Principle Name:  http://msdn.microsoft.com/en-us/library/ms191153.aspx.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Wednesday, February 22, 2012 12:14 PM
    Monday, February 13, 2012 7:14 AM

All replies

  • Any experts Advise ?

    Take note SharePoint 2010 and SQL Server 2008 R2 are in the same machine

    I have set up kerberos for the SharePoint 2010 and its working

    Only for SQL Server 2008 r2 is not working


    Ready for Action


    Sunday, February 12, 2012 10:57 AM
  • Hi Striking,

    To use Kerberos authentication with SQL Server requires both the following conditions to be true:

    • The client and server computers must be part of the same Windows domain, or in trusted domains.
    • A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

    If Kerberos cannot be used, Windows will fall back to Windows NT Challenge/Response (NTLM) authentication.


    Reference:
    Kerberos Authentication and SQL Server: http://msdn.microsoft.com/en-us/library/cc280744.aspx.
                                                                http://msdn.microsoft.com/en-us/library/cc280744.aspx.
    Register a Service Principle Name:  http://msdn.microsoft.com/en-us/library/ms191153.aspx.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Wednesday, February 22, 2012 12:14 PM
    Monday, February 13, 2012 7:14 AM