Re: IIS / SQL Server impersonation

From: Joe Kaplan \(MVP - ADSI\) (joseph.e.kaplan_at_removethis.accenture.com)
Date: 05/25/05 

Date: Wed, 25 May 2005 11:41:28 -0500

You need Kerberos delegation to get this to work. I'd start with the big
Kerberos white paper:

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

HTH,

Joe K.

<matthew_glen_evans@hotmail.com> wrote in message
news:1117031888.339049.185630@g43g2000cwa.googlegroups.com...
> 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
>

Relevant Pages

  • Re: Saving WSS site prior to upgrading SQL Server 2005 Express to Standard/Enterprise
    ... Your backup is from a different version of Windows SharePoint Services and ... SQL Server 2005 is available. ... The advantages of the embedded version ("Windows Internal Database") over ...
    (microsoft.public.sharepoint.windowsservices)
  • IIS / SQL Server impersonation
    ... I am attempting to implement impersonation from a windows application ... I have configured the host virtual directory in IIS to require windows ... the database permits Windows Authentication. ... When I try and open a connection to SQL Server: ...
    (microsoft.public.dotnet.security)
  • Re: User setup problem.
    ... login permissions for SQL Server. ... have access to SQL Server and the database through their ... windows group role membership which has the permissions.. ...
    (microsoft.public.sqlserver.security)
  • Re: BizTalk 2004 MSDTC
    ... On my Windows XP, dev machine, I set ... and it still errors out when updating a remote SQL Server database running ... BizTalk 2004 Server, Visual Studio.Net, and local SQL Server DB ... The problem I am having is that with MSDTC ...
    (microsoft.public.biztalk.general)
  • Permissions problem on SBS 2003 R2 for SQL Server 2005 clients
    ... involving SQL Server Express 2005 on a Windows SBS 2003 R2 network. ... I've been testing a SQL Server 2005 Express database in a multiuser ... environment using Window Small Business Server 2003 R2 with Windows XP ... On a given client computer to which I may have logged on with Windows ...
    (microsoft.public.windows.server.sbs)