Directory Programming .NET

·         SQL server, can we pass the authentication information through to SQL server?

 

Instead of using a generic app login, can the ADFS login be used as a windows login to the SQL server to perform the operations, that way you know which user was doing what?

 

So the app would use the user's login credentials to connect to SQL server instead of a generic app login.

You could implement a standard Kerberos delegation scenario if you use the NT token agent.  SQL won't accept a SAML token for login directly though.  It needs to either be windows auth or SQL auth.

I've never setup a delegation scenario, but I will give it a try.  I have a tall order to fill, but it would be cool to have all these things fall into place.

Do you mean that I would need to setup the website to be a token based app?  Or the SQL server itself?

If you want to flow the authentication from the web app to SQL such that SQL is authenticating the web site authenticated user instead of a fixed user, then you definitely need to use the token agent for ADFS.  This is because SQL only understands SQL auth and Windows auth and the ADFS token agent is the only component that gives you Windows auth integration with IIS.  Note that in a federated scenario, you'll need to have a strategy for mapping shadow accounts from the external auth realm to the resource forest.  It can get tricky.

Other than that, configuring delegation for ADFS isn't really any different than configuring a normal delegation scenario using protocol transition and constrained delegation.  That said, people often find this task quite daunting and very difficult to debug.  I'd suggest doing some reading on the 40+ pp docs on TechNet that explain how to do it and troubleshoot it.  :)

Ok, so if my Sql box is joined to my external domain, and it trusts the other domains (forest trust) then I shouldn't need the shadow accounts, I'll just need to setup the delegation.

Could you point me in the direction of the docs you are talking about?

Thanks Joe

according to this table, it sounds like I will be losing some functionality that I might need in the app.

http://technet2.microsoft.com/windowsserver/en/library/a6635040-3121-47ce-a819-f73c89dafc571033.mspx?mfr=true

Windows NT token–based application, cannot handle claims within the application code

Windows NT token–based application, cannot use Authorization Manager for access control

If it cannot handle claims within the code does that mean I no longer get the users groups as roles in the code to use for authorization?

Token apps have the same security features as a typical ASP.NET app that uses IIS authentication in that you get a Windows token for the user (a WindowsIdentity/WindowsPrincipal), so you can still do authorization against Windows groups.

The main thing you lose is the access to the custom claims.  You also lose some flexibility since you need some sort of mapping to identities in the resource forest whereas that isn't required with claims apps.

Basically, you use the token agent when you need Windows authentication for some requirement of the application.  Delegation to SQL server would be one reason to do this.  If you had a non-.NET 2.0+ app in IIS you needed to integrate, that would be another reason.

http://www.adopenstatic.com/cs/blogs/ken/archive/2007/01/28/1282.aspx

I was reading the above articles, it sounds like I could use any type of authentication to the web server and setup the delegation between the web and sql servers.

Or if that article is assuming you're using IWA then I'd need the NT-token app.

using the NT token isn't so bad, unless that forces the requirement for shadow accounts for anyone outside the org.  of course anyone "outside" the org in this instance will have an external domain account.  it's just the "federated" users in another org who will need shadow accounts/groups, right?

NT token apps can do delegation to SQL as long as there are shadow accounts provisioned for the external FS users.  Users from the forest of the resource FS will already have accounts.

I'm also pretty sure this requires 2003 AD in 2003 FFL because you need constrained delegation and protocol transition.

As to whether you really want to build an app like this is up to you.  You can also use the trusted subsystem design if that works for the way you want to use SQL Server.

just need to get this to work in the lab to show it, but it might not be the best option, I personally like to use trusted subsystem myself

I was using that bit of code you wrote Joe just as a quick test.

Unable to cast object of type 'System.Web.Security.SingleSignOn.SingleSignOnIdentity' to type 'System.Security.Principal.WindowsIdentity'

I'm getting this error.  I may be over complicating this (I thought i was making it easier :D) but I have my extweb.com site which is a claims app, there is a vdir that is listed as a separate app extweb.com/sqlapp that i have setup, the code is giving me this error

I assume this is happening because the NT Token app is contained within the claims app and it is getting the User.Identity crossed

any ideas?

Never EVER have different ADFS apps in the same web path hierarchy.  You will have overlapping cookies and chaos can ensue.  For example, don't define two apps like this:

https://server.com/app1/

https://server.com/app1/app2/

The cookie path for /app1 also applies to /app1/app2, so app2 will get app1's cookies.  You'll get ADFS errors from this.

Always make the apps pure siblings or just avoid having more than one app per host name (note that in terms of how cookies work, the port number does NOT make the host name distinct, so you can't vary by port number to accomplish this).

That said, you need a pure token app to do this.  You can't really use a claims app for this(although there is a hybrid combo where you enable both, but I suggest you avoid this).

Once you have a token app, you need to enable Kerberos delegation with protocol transition for the web app pool identity in AD and ensure that you have constrained delegation to the SQL server enabled.  You also need a proper SQL SPN set for the SQL server box.  There may have been one configured when you installed SQL.  It depends on whether you have SQL running as network service or system or if you are using a fixed account.

You also don't get a valid test here unless SQL is on a different machine.  You can't cross forest boundaries with constrained delegation, so make sure the SQL box and web box are in the same domain (or at least the same forest).

yeah, not sure why i didn't remember the cookie thing, i've created a separate app now on a new IP/DNS so i'm good there

found this: http://blogs.technet.com/adfs/archive/2008/05/13/using-adfs-with-constrained-delegation.aspx

jimsim wrote this so i'll go through this and see if i can get past my errors

I still get timeouts, anonymous user, not trusted sql account messages.

my SPNs should be set, I have the delegation setup, the windows group has access to sql resources i'm trying to hit, impersonation is true in web config (i've must have something screwed up in all this though)

web server and sql server are two separate machines (virtuals) and they are both on the same domain, the account i'm using is also a user in this domain

I'm not sure what I'm doing wrong, but even following Jim's guide I keep seeing IUSR_machine as the user that is hitting the page.

Shouldn't I be seeing the ADFS logged in user?  Which is a user on the domain of the web and sql servers.

When I was trying to hit the db server with my token app/impersonate=true/integrated security=sspi it kept saying I wasn't authorized or I wasn't a trusted login

I'm assuming that this is all because I'm still not really delegating.  IUSR_ keeps showing up as the account.

what monitoring tools would help running down the issues?

Context.User should be a WindowsPrincipal representing the Windows security principal of the ADFS user.  If you have impersonation enabled, WindowsIdentity.GetCurrent() should give you the same thing as Context.User.Identity.

If that isn't happening, your token app isn't working.  I'm not sure why.