Aric- (Also trying to answer Joe K's questions)
The developer "owns" all 3 of the SQL servers involved so he definitely has a vested interest in the integrity of the data on the SQL servers. SQL server runs under a domain service account only used on them. They just wanted me to create the SPN's for the domain account the service runs under and tick the "Account is trusted for delegation" on the service account and "Computer is trusted for delegation" on the SQL servers' machine accounts. Seemed to me the proper way would be to utilize "Trust this computer for delegation to specified services only" to set up the middle tier service account to be only able to talk to the back end SQL servers' services and configure the account to use constrained delegation without protocol transition by selecting "Use Kerberos Only". It also seemed like only the middle tier needed to have the machine account trusted for delegation and, finally, that it would be better to run the backend server under a separate service account with it's own SPN's. Am I close? Joe- Your point about the limiting the accounts by marking "sensitive and cannot be delegated" is well taken. As soon as I started looking at this can of worms, that occurred to me immediately. Thanks again Bob -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard, Aric Sent: Tuesday, August 09, 2005 3:01 PM To: ActiveDir@mail.activedir.org Subject: RE: [ActiveDir] Kerberos Delegation Bob, As Rick and Joe mentioned, as far as allowing a system to do something on behalf of a user, constrained delegation is a pretty good solution. Your developers need as I understand it is as follows: User connects to a front application server (i.e. web server) and authenticates to that server using Kerberos. The application needs to be able to contact multiple different SQL servers to perform a distributed query. If the application where to do with a service account, the response to the query would likely contain all of the information that the service account had that matched the query - this might contain more or less information than the user making the request has access to. In addition the audit trail on the SQL server should reflect that the application server made the access to the SQL server as opposed to the user. Using constrained delegation, the application server is provided the capability to act as the user when interacting with the identified SQL servers (only). If done properly, the application server will be delegated in a manner that explicitly identifies the SQL servers Service Principal names (which include port numbers) associated with each SQL computers object in the directory. Therefore the application server CAN impersonate the user but under the constraint that it may only occur when communicating with the remote server/service/port as named in the delegation. In your case the risk should be relatively low so long as your developer has a vested interest in the integrity of the data on the SQL servers. The only "abuse" of this specific configuration that I can think off the top of my head would be possibility for the developer to execute a stored procedure on the SQL server with more rights than he or she would typically have thereby gaining access to or altering data in the DB that they would otherwise not have access to. Now if your developer starts asking for constrained delegation from the application server to a DC, we should talk some more. :) Regards, Aric -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Free, Bob Sent: Tuesday, August 09, 2005 2:33 PM To: ActiveDir@mail.activedir.org Subject: RE: [ActiveDir] Kerberos Delegation >Assuming that you are aware of what constrained delegation is, how it operates, and what it should be used for... That's the point of my query, I certainly don't understand all I know about it and we have never allowed it, at this point I have just begun to scratch the surface. I was totally uncomfortable when it was first proposed and threw up the stop sign. I'm getting less comfortable by the minute as I read more about it. I'm reading the Kerberos Protocol Transition and Constrained Delegation article and the Troubleshooting Kerberos Delegation white paper and like I said, trying to understand all I know about it ;-( Everyone's comments so far are immensely appreciated. Thanks Bob -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard, Aric Sent: Tuesday, August 09, 2005 1:38 PM To: ActiveDir@mail.activedir.org Subject: RE: [ActiveDir] Kerberos Delegation Assuming that you are aware of what constrained delegation is, how it operates, and what it should be used for... Anytime you allow someone or something to impersonate, err, act on behalf of another security principal, there is always cause for concern. Constrained delegation certainly provides some flexibility in achieving this goal and fulfilling the applications need, but like any Domain Admin in your forest the developer and the application must be trusted. I would recommend clear documentation as to the architecture of the application, how and with what other systems it interoperates, and if you have the wherewithal (or can bring in someone who does) a code review to ensure that what is defined is accurate. I know this seems a little over-the-top, but we are taking about you accepting someone else walking around with my ID and saying "he told me it was OK that I access <fill in the blank> on his behalf." Regards, Aric Bernard -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Free, Bob Sent: Tuesday, August 09, 2005 1:07 PM To: ActiveDir@mail.activedir.org Subject: [ActiveDir] Kerberos Delegation We have a developer who wants us to allow delegation for a couple of SQL servers and their service accounts so he can do distributed queries across linked servers. This is new ground for us from an AD perspective that I have just started researching and I'd like to hear other's thoughts, policies etc. We are at 2003 functional level so from what I read, we can allow constrained delegation which is much better than un-constrained but most of the comments I come across indicate this isn't something to be taken lightly, has serious security ramifications, policies should be in place etc etc.. I can find a reasonable amount of information from the developers point-of-view, and I can see how to implement it technically (I think) but not a whole lot from the AD admin's perspective, especially as it pertains to the desirability of allowing it and how best to manage it if it is allowed. Any info greatly appreciated. Bob List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/ List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/ List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/ List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/ List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
