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.

Answer 2

Here the class I use. Also, you'll want to check and see if the process that the AppPool is running under has enough permission to do the impersonation since it is such a privileged activity. I would give the user account that the app pool is running under temporary admin privileges (dev box only of course) and see if it works so you'll know if it is a permissions issue.

public class ImpersonationHelper : IDisposable
    {
        private const int LOGON32_LOGON_INTERACTIVE = 2;
        private const int LOGON32_PROVIDER_DEFAULT = 0;
        private WindowsImpersonationContext _impersonationContext;
        private string _userName;
        private string _domain;
        private string _password;

        [DllImport("advapi32.dll")]
        public static extern int LogonUserA(String lpszUserName,
            String lpszDomain,
            String lpszPassword,
            int dwLogonType,
            int dwLogonProvider,
            ref IntPtr phToken);
        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        public static extern int DuplicateToken(IntPtr hToken,
            int impersonationLevel,
            ref IntPtr hNewToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        public static extern bool RevertToSelf();

        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        public static extern bool CloseHandle(IntPtr handle);

        public ImpersonationHelper(string domain, string userName, string password)
        {
            _userName = userName;
            _domain = domain;
            _password = password;
        }

        public void Start()
        {
            WindowsIdentity tempWindowsIdentity;
            IntPtr token = IntPtr.Zero;
            IntPtr tokenDuplicate = IntPtr.Zero;

            if (RevertToSelf())
            {
                if (LogonUserA(_userName, _domain, _password, LOGON32_LOGON_INTERACTIVE,
                    LOGON32_PROVIDER_DEFAULT, ref token) != 0)
                {
                    if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
                    {
                        tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
                        _impersonationContext = tempWindowsIdentity.Impersonate();
                        if (_impersonationContext != null)
                        {
                            CloseHandle(token);
                            CloseHandle(tokenDuplicate);
                        }
                    }
                }
            }
            if (token != IntPtr.Zero)
                CloseHandle(token);
            if (tokenDuplicate != IntPtr.Zero)
                CloseHandle(tokenDuplicate);
        }

        #region IDisposable Members

        void IDisposable.Dispose()
        {
            if (_impersonationContext != null)
            {
                _impersonationContext.Undo();
            }
        }

        #endregion
    }

Answer 3

Have you enabled Impersonation on the Windows 7 or Windows 2008 machine? This article covers how to set it up. http://technet.microsoft.com/en-us/library/cc730708(WS.10).aspx. Also, are you running 32-bit or 64-bit?

Answer 4

You should also check with your AD administration to see if Impersonation is allowed. My companies AD policies won't allow impersonation.