how to get a HttpModule to authenticate to SqlServer

Question

I need to add a database call to a HttpModule. I used the same connection string that we use for our WCF service which is hosted by IIS, however the HttpModule can not authenticate. (We use Windows Authentication and yes the WCF service does authenticate)

The error message I get back is Login failed for user domain\machinename$'.

What do I need to do to get the HttpModule to authenticate on Sql Server 2008?

Answer 1

Just in case someone else runs into the same issue I had, here is an answer that will work. Most of the code was taken from a MSDN site...it works. But essentially what you want to do is impersonate the user you need.

Add these to your usings,

using System.Web.Security;
using System.Runtime.InteropServices;
using System.Security.Principal;

Write following method:

private bool impersonateValidUser(String userName, String domain, String password)
{
    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);
                    return true;
                }
            }
        }
    }
    if (token != IntPtr.Zero)
        CloseHandle(token);
    if (tokenDuplicate != IntPtr.Zero)
        CloseHandle(tokenDuplicate);
    return false;
}

implement where needed

if (impersonateValidUser("username", "domain", "password"))
{
// code that needs to access Db
// make sure you undo when no longer needed
impersonationContext.Undo();
}