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

From: Ken Schaefer (kenREMOVE_at_THISadopenstatic.com)
Date: 01/29/05 

Date: Sat, 29 Jan 2005 23:25:30 +1100

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

"DarrylR" <darrylr@nospam.com> wrote in message
news:%23p3Iw6YBFHA.1452@TK2MSFTNGP11.phx.gbl...
> We have a machine running Windows 2003 Server, IIS 6, and Windows
> SharePoint
> Services. The machine resides in our DMZ (outside the firewall), and is on
> a
> separate domain that has a one-way trust relationship with our intranet
> domain (separate forest; the extranet domain trusts accounts from our
> intranet domain).
>
> We've created 2 separate virtual directories for a particular WSS site.
> The
> virtual directory used internally uses Integrated Windows authentication.
> The one used by extranet users uses Basic authentication over SSL. Neither
> virtual directory has Kerberos Authentication enabled. The web.config
> files
> in both virtual directories contain the <authentication mode="Windows" />
> and <identity impersonate="true" /> elements.
>
> We are using Office Web Parts (specifically the PivotView) on several Web
> Part pages to display data retrieved from a SQL Server (SQL 2000 SP3
> running
> under Windows 2000 Server SP4 on a different machine located behind our
> firewall, member of the intranet domain). The pages with Office Web Parts
> on
> them work correctly when accessed via our intranet (only from machines
> that
> belong to the intranet domain); users are logged on seamlessly using their
> domain credentials, and the PivotViews retrieve data correctly. When we
> access these pages via our extranet (or from a machine that is currently
> on
> the intranet but isn't a member of the intranet domain), even if the user
> supplies valid domain credentials when challenged by the WSS site, the
> Office Web Parts fail to retrieve data. According to SQL Profiler, this is
> due to a SQL login failure ("Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection.").
>
> Incidentally, I get the same error when I try to access the same SQL
> Server
> box using Windows Authentication from within Query Analyzer if I'm not
> logged in using domain credentials. Could it be that the Office Web Parts
> use your current identity despite what you supply when challenged by IIS?
> If
> so, how can I get them to use the credentials that I supply during the
> NTLM/Basic challenge?
>
> We've opened the generally accepted ports on the firewall to support SQL
> Server (at least we know that WSS is able to access the SQL Server box to
> deliver all other portal content). The Office Web Parts fail to login
> (from
> the extranet) when we use connection strings similar to the following:
>
> Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=ourDB;Data Source=ourSQLserver;
> Provider=SQLOLEDB.1;Trusted_Connection=Yes;Persist Security
> Info=False;Initial Catalog=ourDB;Data Source=ourSQLserver;
>
> So far, the only way that we've been able to get this to work from the
> extranet has been to use SQL Authentication and a connection string
> similar
> to the following:
>
> Provider=SQLOLEDB.1;Persist Security Info=True;User
> Id=ourUserId;Password=ourPwd;Initial Catalog=ourDB;Data
> Source=ourSQLserver;
>
> Obviously, we'd rather not use SQL Authentication, since the Office Web
> Parts write the connection string to the page, where it is readable in the
> browser using the View/Source command. I researched the problem and
> discovered the following article (among others) which looked promising:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskaccessingsqlserverusingmappedwindowsdomainuser.asp
>
> However, the steps outlined in the article didn't correct the problem.
> I've
> also seen references to Kerberos authentication that suggest that it could
> solve the problem. However, rather than plow blindly ahead, I thought I'd
> seek input.
>
> Any suggestions?
>
> Thanks,
> Darryl R.
>
>

Relevant Pages