Your understanding is correct. And it does necessitate, for effective auditing, that the application be aware of appropriate identity.
If you want to audit, using the built-in tools, then SOMEWHERE you have to impersonate. Whether you do it at the application or the database layer is up to you and impacts this choice. Regards, Michael B. Smith Consultant and Exchange MVP http://TheEssentialExchange.com From: Jim Holmgren [mailto:[email protected]] Sent: Friday, June 18, 2010 10:15 AM To: NT System Admin Issues Subject: RE: SQL Impersonation vs Trusted Subsystem Hi MBS, I am leaning toward recommending Trusted Subsystem myself based on my (admittedly) brief research. Essentially, I read the Patterns and Practices doc from MSDN. However our DBAs are pushing for Impersonation/Delegation because it appears to offer more granular auditing. We have a big "too many cooks in the kitchen" problem here and they would like to be able to audit across all tiers of the system and provide very granular access control. In the Trusted Subsystem model, my understanding is that you would have to pass the identity back at the application level for effective auditing. Otherwise I can't see a serious downside to using Trusted Subsystem aside from the possibility of middle-tier compromise. As far as I know these applications will be for internal use only, which doesn't completely mitigate the concern, but it does help. I'm trying to maintain a neutral stance, but the DBAs work for me...so I have a little built-in bias to overcome. Jim From: Michael B. Smith [mailto:[email protected]] Sent: Friday, June 18, 2010 10:04 AM To: NT System Admin Issues Subject: RE: SQL Impersonation vs Trusted Subsystem I don't understand why you wouldn't want to use Trusted Subsystem. Could you speak to that? Regards, Michael B. Smith Consultant and Exchange MVP http://TheEssentialExchange.com From: Jim Holmgren [mailto:[email protected]] Sent: Friday, June 18, 2010 10:00 AM To: NT System Admin Issues Subject: SQL Impersonation vs Trusted Subsystem Gooood Morning, Is anyone on the list familiar with implementing these two methods of connecting to SQL server via ASP.Net in the real world? I'm trying to understand better a vague reference in the MSDN docs to "large numbers of users". i.e. The docs state that one of the disadvantages to Impersonation is that it "significantly limits the application's ability to scale to large numbers of users". I understand what the limitation is, connection pooling, but I don't know the scale. I'd really like to know what 'large numbers of users' means - 100? 10,000? 1,000,000? All could be 'large numbers of users' in different contexts. Our developers are asking us which method we would like them to use for some new projects they are about to start working on. Don't get me started on why the developers we hired have no experience in this arena. All that I'll say is, we get what we pay for around here. -Jim Jim Holmgren Manager of Server Engineering XLHealth Corporation The Warehouse at Camden Yards 351 West Camden Street, Suite 100 Baltimore, MD 21201 410.625.2200 (main) 443.524.8573 (direct) 443-506.2400 (cell) www.xlhealth.com<http://www.xlhealth.com> CONFIDENTIALITY NOTICE: This email, including attachments, is for the sole use of the intended recipient(s) and may contain confidential and/or protected health information. Under the Federal Law (HIPAA), the intended recipient is obligated to keep this information secure and confidential. Any disclosure to third parties without authorization from the member of as permitted by law is prohibited and punishable under Federal Law. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. NOTA DE CONFIDENCIALIDAD: Este mensaje incluyendo cualquier anejo es para uso exclusivo del (los) destinatario (s) y puede incluir informaci?n confidencial y/o informaci?n de salud protegida. La Ley Federal (HIPAA) establece que el destinatario est? obligado a mantener la informaci?n confidencial y sequra. HIPAA proh?be y castiga cualquier divulgaci?n a terceras personas sin autorizaci?n del afiliado o permitido por ley. Si usted no es el destinatario, redirija esta mensaje al remitente, y destruye cualquier copia existente del mensaje original. CONFIDENTIALITY NOTICE: This email, including attachments, is for the sole use of the intended recipient(s) and may contain confidential and/or protected health information. Under the Federal Law (HIPAA), the intended recipient is obligated to keep this information secure and confidential. Any disclosure to third parties without authorization from the member of as permitted by law is prohibited and punishable under Federal Law. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. NOTA DE CONFIDENCIALIDAD: Este mensaje incluyendo cualquier anejo es para uso exclusivo del (los) destinatario (s) y puede incluir informaci?n confidencial y/o informaci?n de salud protegida. La Ley Federal (HIPAA) establece que el destinatario est? obligado a mantener la informaci?n confidencial y sequra. HIPAA proh?be y castiga cualquier divulgaci?n a terceras personas sin autorizaci?n del afiliado o permitido por ley. Si usted no es el destinatario, redirija esta mensaje al remitente, y destruye cualquier copia existente del mensaje original. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
