Can I impersonate a client authenticated with forms auth and establish a trusted connection to SQL Server?

Question

Here is what I've been trying to do

Build an ASP.NET MVC 3 application with forms authentication and active directory membership. The web server and database are different physical servers hence a double hop.

I thought the answer was this older article on constrained delegation and protocol transition? So far, I have not been able to get the technique to work.

I'm testing this from my DEV machine (Windows 7, IIS7) for the web server before deploying to windows 2008 (IIS7) in the production setup. Would windows 2008 make a difference?

What works and what fails

I'm able to login with forms auth and the AD membership. This seem to be working fine. When I try to make a database call using this code:

public void AsUser(Action action)
    {
        using (var id = new WindowsIdentity(User.Identity.Name + @"@example.com"))
        {
            WindowsImpersonationContext context = null;
            try
            {
                context = id.Impersonate();
                action.Invoke();
            }
            catch (Exception ex)
            {
                // ex.Message is The type initializer for System.Data.SqlClient.SqlConnection threw an exception
                // buried inner exeption is Requested registry access is not allowed
            }
            finally
            {
                if (context != null)
                {
                    context.Undo();
                }
            }
        }
    }

It fails with an exception leading me to believe I have setup issues on my local DEV server. The inner exception is Requested registry access is not allowed.

If I set a breakpoint and inspect the WindowsIdentity after the Impersonate() call I see that the ImpersonationLevel is set to Identification. This seems like a clue that it is not setup correctly. Can anyone confirm?

Am I on the right track and is this even possible to setup? Any pointers would be appreciated.

Answer 1

I think you are on the right track. You just need more troubleshooting work on your protocol transition setup.

I assume you configured your Active Directory membership provider correctly so that you can successfully logon your web page using the active directory user name and password. If that's not the case, please ignore the rest of my answer :)

From what I saw in your question, you got your user's token using S4USelf by WindowsIdentity. Then, you are using S4UProxy to pass the impersonated token to SQL server. Since you said you got ImpersonationLevel.Identification only, it means you failed to do protocol transition.

You need to understand that allowing one machine to do protocol transition in a domain is very high privilege. Granting a server to do protocol transition almost means that you trust that server to be almost like a domain controller. You need to consciously make this decision in AD to turn a server to have this ability and you have to be a domian administrator to make this change. If you haven't done this, you probably didn't setup your thing properly.

There are couple things to check.

First, make sure you selected "Trust this computer for delegation to specified services only" and then you picked "select Use any authentication protocol" on your service account. You may like to create a domain account. Here is a link on how to create a service account for ASP.NET. Remember, you need a domain account. After you created a domain service account, make sure you go to the delegation tab on that account and selected the correct options.

Second, you need to make sure SPNs are set properly. I realize that the link that you posted only mention the SPN of your ASP.NET service account. Actually, you also need to make sure the service account on your SQL server also set properly. Otheriwse, Windows won't use Kerberos authentication at all. It will fall back to use NTLM. There are a lot of details to setup a SPN correctly on SQL server. You can check here first and see if you have any luck. From my experience, most of the DBA don't know how to set them up properly. They don't even aware of it because most applications work fine with NTLM. You need to pay attention to the SQL server service account and the port number that it's using.

Third, you need to make sure there is nothing disabling your Kerberos delegation. Some sensitive AD accounts are by default not allowed to be delegated. For example, the built-in administrator account. So, you better use some other normal user accounts for testing purpose.

UPDATE

I just found another article teaching you how to setup the protocol transition for ASP.NET. It mentioned that you need to grant TCB right to the IIS service account in order to make sure it can create a Impersonation type WindowsIdentity. You can give it a shot.