Post reply

Kerberos Authentication not Working

  • December 2, 2011 at 9:33 am

    #1416013

    ajsongy (12/2/2011)

    Delegation authority in AD for allowing an account permission to modify SPN can be found here.

    Thanks you, i will look into it.

    Greg Roberts

  • December 2, 2011 at 9:34 am

    #1416014

    Nils Gustav Stråbø (12/2/2011)

    I can't find any information anywhere that says anything about the SPN name being MSSQLSERVER. It has always been MSSQLSvc, ref http://msdn.microsoft.com/en-us/library/ms191153.aspx

    The service account does not need any permissions to register the SPN if you or your netadmins have done it manually using setspn.

    Did you run setspn -X to verify that no duplicate spns are registered?

    Good news is MSSQLSvc is also there. I can manually drop the MSSQLSERVER reference (which is the default instance) and see if that helps.

    setspn -X did not reveal duplicate registrations for that server. Other servers (some of which have been retired) do have duplicates, but none with the Kerberos issue.

    Thanks,

    Greg

    Greg Roberts

  • December 2, 2011 at 5:10 pm

    #1416223

    Does the server have an "A" record in DNS, or is is a CNAME? CNAME records aren't supported with kerberos.

    To troubleshoot kerberos on the client:

    1. install a network capture utility (I prefer wireshark)

    2. close out of SSMS.

    3. start a capture on your network interface.

    4. use the kerbtray utility to purge your ticket cache.

    5. open SSMS, and try to connect to the server.

    6. stop the capture.

    I'd start by filtering on ip of the sql db server or the kerberos protocol "ip.addr == 10.10.10.10 || kerberos"

    You should see 3 packets for the TCP connection (TCP [SYN], [SYN, ACK], [ACK])

    followed by 4 or 5 TDS packets (Hello Sql Server!, etc),

    followed by some kerberos packets( TGS_REQ, TGS_REP --ticket granting service request/replies) .

    in the first TGS_REQ, You ask the domain controller for the public key of the service you're trying to connect to. (This is why the SPN has to be registered.). You should be able to drill down to the KDC_REQ_BODY:

    + KDC_REQ_BODY rc4-hmac

    Padding: 0

    + KDCOptions: 40810000 (Forwardable, Renewable, Canonicalize)

    Realm: YOURDOMAIN.LOCAL

    + Server Name (Service and Instance): MSSQLSvc/yourTargetSqlServer.yourDomain.local:1433

    ...

    The next packet should be the success reply from the domain controller (TGS_REP)

    if you drill into it, you should see the "Client Name (Principal): YOURWINDOWSLOGIN "

    followed by the Ticket section (containg the same server information as above)

    -or-

    You could get an error message in the reply. If so, post it.

    No KRB Packets at all? Sql Server doesn't think it can do kerberos Authentication. (Server in a workgroup, not on a domain)

    You should see a TDS "Response Packet, NTLMSSP_CHALLENGE" when sql falls back to NTLM

    **Note** this same process works for just about any kerberos issue client. (SSRS/sharepoint delgation)

    Troubleshooting http is easy as well:

    you send a GET,

    reply is a 401.1 UNAUTHORIZED (reply header should contain the auth schemes supported, delegate and/or NTLM)

    TGS_REQ

    TGS_REP

    you resend your request, including the authorization header

    reply should be the page. (200, OK)

    unfortunately, network capture is the best way to figure out kerberos issues.

    good luck!

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply