Re: How can I avoid using SQL Authentication with the Office Web Parts?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: DarrylR (darrylr_at_nospam.com)
Date: 01/30/05 

Date: Sun, 30 Jan 2005 15:34:51 -0500

Ken,

Thanks for the reply and references to suggested reading. Everything that
I've read suggests that using Basic authentication should have resolved the
"Double Hop" issue (as you pointed out). That's the reason that I chose to
use Basic authentication for extranet users. It obviously isn't working,
though.

One possible explanation for this is that the Office Web Parts ignore the
credentials supplied by the user when integrated security is specified in
the connection string, and use the current Windows user account instead. I
read some documentation (for Project Server 2003, which uses some Office Web
Components and SQL Server Analysis Services) that suggested that if you want
to use Basic authentication to implement pass-through security, you must
also enable Basic authentication for the Remote Data Services ISAPI Library
(Msadcs.dll). However, I also read that creating an MSADC virtual directory
is frowned upon in Windows Server 2003/IIS 6.0 because it creates a security
risk. So let's put this aside for now...

Another thing that leads me to believe that the Office Web Parts ignore
supplied credentials and use the current Windows user account is the fact
that the site works for internal users, who hit it from a virtual directory
that uses Integrated Windows authentication. I'm surprised that it works
because according to the NTAuthenticationProviders metabase key (returned by
adsutil.vbs), Kerberos is not enabled for that virtual directory; the key
value is "NTLM", not "Negotiate,NTLM". And even if Kerberos is enabled by
default when Integrated Windows authentication is used in IIS 6.0 (suggested
by David Wang in a separate post), I haven't specifically enabled any user
accounts or computers for delegation or created any Service Principal Names.
Therefore, I'm assuming that Kerberos is only partially implemented, and a
true double-hop should still fail. Yet the Office Web Parts retrieve data
for internal users.

So when I get in tomorrow, I plan to test my theory by logging into my
machine using one domain user account and then logging into the portal using
a different account. Just to be clear, I'll be logging in from our intranet,
so I'll be hitting the virtual directory that uses Integrated Windows
authentication. I'll use SQL Profiler to determine which credentials are
used to access the database. My guess is that it will be the credentials
that I use to log onto my machine. This would suggest that the Office Web
Parts ignore impersonation.

I'll let you know what I find out.

Regards,
Darryl R.

"Ken Schaefer" <kenREMOVE@THISadopenstatic.com> wrote in message
news:uvNBY2fBFHA.936@TK2MSFTNGP12.phx.gbl...
> I'm not familiar with Office Web Parts, so this may not be specific enough
> to your situation. But in general:
>
> If you are using NTLM authentication you will run into a double-hop
> authentication problem if you have a:
>
> Browser -> IIS Server -> SQL Server
>
> setup. If you use Basic Authentication, or Kerberos Authentication (with
> appropriate delegation enabled) you can get around this.
>
> Some suggested reading:
> http://www.adopenstatic.com/resources/books/293_CYA_IIS6_05.pdf
> (from this book:
> http://www.amazon.com/exec/obidos/ASIN/1931836256/adopenstati0f-20)
>
> -and-
>
>
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> Troubleshooting Kerberos Errors
>
>
> A tool that may be able to help you diagnose what's happening with your
> entire end-to-end authentication:
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=e90fe777-4a21-4066-bd22-b931f7572e9a&DisplayLang=en
> AuthDiag v1.0
>
> Cheers
> Ken

Relevant Pages