IIS / SQL Server impersonation

• Previous message: Steve B.: "Re: Appl. Security Problems"
• Next in thread: Joe Kaplan \(MVP - ADSI\): "Re: IIS / SQL Server impersonation"
• Reply: Joe Kaplan \(MVP - ADSI\): "Re: IIS / SQL Server impersonation"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: 25 May 2005 07:38:08 -0700

Hi

I am attempting to implement impersonation from a windows application
to a SQL Server database via a remoting middleware application(hosted
in IIS 6 on W2003 Server).

I have configured the host virtual directory in IIS to require windows
authentication, and anyonymous access is off.

Web.config specifies the following:

    <system.web>
                <authentication mode="Windows" />
                <identity impersonate="true"/>
    </system.web>

(In addition to the remoting configuration.)

On the clients app.config I have:

            <channel ref="http" useDefaultCredentials="true">
               <clientProviders>
                  <formatter
                     ref="binary"/>
               </clientProviders>
            </channel>

Finally, the database permits Windows Authentication.

When I try and open a connection to SQL Server (2000):

conn.ConnectionString = @"Trusted_Connection=Yes;Persist Security
Info=False;Initial Catalog=xxxxxxx;Data Source=xxxxxxx";
conn.Open();

I have tried variations on this which include the "Integrated Security
= SSPI"
property.

In all cases, I get the following:

Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.

(The SQL server machine is on a separate box, which may be relevant. I
have seen a number of posts around this)

I have tried a couple of other things including elevating the Trust
level of the assembly to Full. Adding the 'Act as Part of the Operating
System' privilege to the ASPNET account. These were pretty much
grabbing at straws.

I have seen some posts around creating mirrored user accounts with
appropriate privileges on both the web and db server, then getting IIS
to run under this account. But this kind of defeats the purpose, I
think.

Essentially I want to impersonate the authenticating Windows user
seamlessly to the database.

I am aware of the connection pooling inefficiency. This is not my
desired solution. The client requires it....

I have tried programmatically forcing the impersonation:

        public string ImpersonateProcess()
        {
                string stemp = "Initial Identity: " +
WindowsIdentity.GetCurrent().Name;
                System.Security.Principal.WindowsImpersonationContext
impersonationContext;
                impersonationContext =
((System.Security.Principal.WindowsIdentity)HttpContext.Current.User.Identity).Impersonate();
                stemp += " Now Impersonating " + WindowsIdentity.GetCurrent().Name;
                impersonationContext.Undo() ;
                return stemp ;
        }

this test method returns the following:
Initial Identity: xxx\xxxxxxxxx Now Impersonating xxx\xxxxxxxxx ( where
xxx\xxxxxxxxx is my domain\logon.

If i run the following method:

        public string GetServerString()
        {
                // Use the HttpContext to acquire what IIS thinks the client's
identity is.
                string temp = HttpContext.Current.User.Identity.Name;
                temp +=WindowsIdentity.GetCurrent().Name;
                temp +=" "+WindowsIdentity.GetCurrent().AuthenticationType;
                temp +=" "+WindowsIdentity.GetCurrent().IsAuthenticated;
                temp +=" "+WindowsIdentity.GetCurrent().IsAnonymous;
                temp +=" "+WindowsIdentity.GetCurrent().Token;
                temp +=" "+WindowsIdentity.GetCurrent().IsSystem;
                if (temp == null || temp.Equals(string.Empty))
                {
                        temp = "**unavailable**";
                }
                return "Hi there. You are being served by instance number: " +
InstanceHash.ToString() + ". Your alias is: " + temp ;
        }

I get this:

Hi there. You are being served by instance number: 55. Your alias is:
xxx\xxxxxxxxx xxx\xxxxxxxxx NTLM True False 1076 False

It seems clear to me that my security token is flowing to the remoting
application. It even looks like impersonation is occurring correctly
(since the Initial WindowsIdentity is mine.

However... no database access. If I run the remoting application
locally in IIS
(5, admittedly) I can access the database.

<sigh>

Any help gratefully received,

Thanks

Matthew

• Previous message: Steve B.: "Re: Appl. Security Problems"
• Next in thread: Joe Kaplan \(MVP - ADSI\): "Re: IIS / SQL Server impersonation"
• Reply: Joe Kaplan \(MVP - ADSI\): "Re: IIS / SQL Server impersonation"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]