locked
Help needed with Kerberos Constrained Delegation RRS feed

  • Question

  • Hi,

    I have a requirment to configure kerberos constrained delegation to accommodate a 'double hop' from SQL to a cifs/SMB file share.

    The scenario is:

    • I have a SQL Server instance (2008 R2) running under account domain\SQLService
    • I have a file share hosted on domain\fileServer to which domain\SQLService has full access
    • The file share contains the file test.dll
    • I need to be able to run the statement CREATE ASSEMBLY test from '\\fileServer\share\test.dll' from a remote ssms session, hence need kerberos double hop to allow the authentication.

    I have got this working by setting the delegation settings for domain\SQLService to 'Trust this user for delegation to any service (Kerberos only)' so I know the SPN and permissions etc.. are correctly set.

    However, I need to get this working using constrained delegation, i.e. to configure the explicit services for the delegation rather than allowing any.

    So I have configured the delegation settings for domain\SQLService to specify 'Trust this user for delegation to specified services only' and specified 'cifs/fileServer'.

    This is resulting in failure.

    I have enabled kerberos logging on the middle tier (SQL Server) and performed a network trace.

    The logging shows event ID 3 with:

    Description:
    A Kerberos error message was received:
     on logon session
     Client Time:
     Server Time: 22:34:54.0000 3/20/2013 Z
     Error Code: 0xd KDC_ERR_BADOPTION
     Extended Error: 0xc0000225 KLIN(0)
     Client Realm:
     Client Name:
     Server Realm: TEST.LOCAL
     Server Name: cifs/fileServer.test.local
     Target Name: cifs/fileServer.test.local@TEST.LOCAL
     Error Text:
     File: 9
     Line: 12be
     Error Data is in record data.

    I have also performed a netmon trace with the working scenario (deleagtion set to allow any service) and this shows the kerberos token for cifs/fileServer.test.local in the securityBlob.

    I cannot see any other service names referenced so am at a lose as why this isn't working with the constrained configuration?

    Any help/advise as to what I am missed here ?

    Thanks,

    Phil

    Wednesday, March 20, 2013 11:01 PM

Answers

  • Finally got this working following a MS Support call...

    Turns out that two delegations are required, one from the SQL Server (SQL Service Account User) to the RPCSS service on the host server on which SQL is running (in my case this was a clustered computer account), and one from the physical SQL host to the CIFS service on the target file server.  Again, in my case SQL was running on cluster so I needed to set the second deleagtion from all physical cluster nodes.

    For a non clustered environment you would set the first deleagtion from the SQL Service to the SQL host server and the second from the SQL host server to the target file server.

    So the complication appears to be related to the way in which SQL handles the delegation via the host server and not directly from itself.

    Unfortantley MS Support were unable to provide a explaination as to why this also worked with a full deleagtion from the SQL Service and no deleagtion from the SQL host server, as on the evidence of the above it would not be expected to!

    Phil

    • Marked as answer by phil_e Thursday, May 23, 2013 3:19 PM
    Thursday, May 23, 2013 3:19 PM

All replies

  • It may help you.

    http://blogs.technet.com/b/askds/archive/2008/11/25/fun-with-the-kerberos-delegation-web-site.aspx

    http://social.technet.microsoft.com/wiki/contents/articles/14493.how-a-client-application-finds-a-service-spn.aspx


    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin


    • Edited by bshwjt Wednesday, March 20, 2013 11:34 PM
    Wednesday, March 20, 2013 11:32 PM
  • One more link for your reference.

    Set-KCD; configuring Kerberos Constrained Delegation (KCD)

    http://blogs.technet.com/b/matthts/archive/2012/10/05/set-kcd-configuring-kerberos-constrained-delegation-kcd.aspx


    Awinish Vishwakarma - MVP

    My Blog: awinish.wordpress.com

    Disclaimer This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Thursday, March 21, 2013 8:52 AM
  • Thank you for the replies / links, although interesting reads these don't really help me...

    There are no IIS/web services involved with this, and I do not beleive the issue to be related to the process of configuring the delegation, it's more a case of what I beleive to be the required configuration (that is, deleagtion to the cifs service from the SQL to the file share service) not working!?

    Regards,

    Phil

    Thursday, March 21, 2013 11:44 AM
  • Talk about coincidence I am working on an extremely similar scenario.  Problem for me is the customer can't articulate how the process is supposed to be working.  We purchased the company and now there delegation process is broke and our support staff is attempting to help.  The "Bad_Option" is telling you that there is a service (CIFS, LDAP, etc...) that hasn't been added to the constrained delegation.  I just learned this late last night.  Try adding like half the services in the constrained list and if that doesn't work take half of the remaining list and add, etc...  See if you can narrow down what is missing.

    Hopefully you can post what you find if you resolve your issue before we get ours resolved.  I also will try and post any finding we have.

    -- 
    Paul Bergson
    MVP - Directory Services
    MCITP: Enterprise Administrator
    MCTS, MCT, MCSE, MCSA, Security+, BS CSci
    2008, Vista, 2003, 2000 (Early Achiever), NT4
    http://www.pbbergs.com    Twitter @pbbergs
    http://blogs.dirteam.com/blogs/paulbergson

    Please no e-mails, any questions should be posted in the NewsGroup. This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, March 21, 2013 11:53 AM
  • Ok, I now have a situation whereby the kerberos delegation to the file server (cifs service) is working if I enable deleagtion for all services (as detailed above).  If I run a network trace whilst executing the create assembly statement from the SSMS session I can clearly identify the kerberos exchanges and the ticket detailing the service cifs/fileServer.test.local.

    If I remove the delegation for all and replace this with delegation to cifs/fileServer.test.local only then the authentication fails, in running a network trace I do not even see any attempts of kerberos authentication between any of the hosts (i.e. I filter the trace for kerberos and there is nothing there!!!).

    How can this be, what's missing here ????

    Very frustrated,

    Phil


    • Edited by phil_e Monday, March 25, 2013 9:08 PM typo
    Monday, March 25, 2013 9:07 PM
  • We ended up chasing our tail, there was nothing wrong with our Kerberos configuration.  The messages we found were just informational, not sure how to articulate it out but we had other issues unrelated to AD.

    I would suggest you try and delegate half the services as previously defined, etc... to narrow down what is needed.  From there you can figure out what in particular it wants. Such as LDAP.

    -- 
    Paul Bergson
    MVP - Directory Services
    MCITP: Enterprise Administrator
    MCTS, MCT, MCSE, MCSA, Security+, BS CSci
    2008, Vista, 2003, 2000 (Early Achiever), NT4
    http://www.pbbergs.com    Twitter @pbbergs
    http://blogs.dirteam.com/blogs/paulbergson

    Please no e-mails, any questions should be posted in the NewsGroup. This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by Cicely Feng Monday, April 1, 2013 3:07 AM
    • Unmarked as answer by phil_e Wednesday, April 3, 2013 10:54 AM
    Tuesday, March 26, 2013 1:22 PM
  • Hi,

    I would like to confirm what is the current situation? If there is anything that I can do for you, please do not hesitate to let me know, and I will be happy to help.

    Regards,

    Cicely Feng

    ---------------------------------------------------

    TechNet Subscriber Support

    If you are TechNet Subscription  user and have any feedback on our support quality, please send your feedback here .

    Friday, March 29, 2013 2:19 AM
  • Thank you for the suggestion, however it does not appear to be as simple as adding services until the delegation works.  I have identified that even with all services from the file server delegated the constrained delegation still does not work.  So at the moment the only working solution I have is to allow delegation to all.  So there is clearly something else in the mix. 
    Wednesday, April 3, 2013 10:57 AM
  • So you have tried adding services one by one and even with them all added it doesn't work until you state all services?

    -- 
    Paul Bergson
    MVP - Directory Services
    MCITP: Enterprise Administrator
    MCTS, MCT, MCSE, MCSA, Security+, BS CSci
    2008, Vista, 2003, 2000 (Early Achiever), NT4
    http://www.pbbergs.com    Twitter @pbbergs
    http://blogs.dirteam.com/blogs/paulbergson

    Please no e-mails, any questions should be posted in the NewsGroup. This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, April 3, 2013 11:59 AM
  • Yes, that is correct.

    I now have a MS support call open for this...

    Monday, April 8, 2013 10:29 AM
  • Finally got this working following a MS Support call...

    Turns out that two delegations are required, one from the SQL Server (SQL Service Account User) to the RPCSS service on the host server on which SQL is running (in my case this was a clustered computer account), and one from the physical SQL host to the CIFS service on the target file server.  Again, in my case SQL was running on cluster so I needed to set the second deleagtion from all physical cluster nodes.

    For a non clustered environment you would set the first deleagtion from the SQL Service to the SQL host server and the second from the SQL host server to the target file server.

    So the complication appears to be related to the way in which SQL handles the delegation via the host server and not directly from itself.

    Unfortantley MS Support were unable to provide a explaination as to why this also worked with a full deleagtion from the SQL Service and no deleagtion from the SQL host server, as on the evidence of the above it would not be expected to!

    Phil

    • Marked as answer by phil_e Thursday, May 23, 2013 3:19 PM
    Thursday, May 23, 2013 3:19 PM