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

• Previous message: Tim Frawley: "IIS Lockdown and WSH and Distributed Assembly excecute permission denied"
• Next in thread: Ken Schaefer: "Re: How can I avoid using SQL Authentication with the Office Web Parts?"
• Reply: Ken Schaefer: "Re: How can I avoid using SQL Authentication with the Office Web Parts?"
• Reply: David Wang [Msft]: "Re: How can I avoid using SQL Authentication with the Office Web Parts?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Fri, 28 Jan 2005 18:10:58 -0500

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.

• Previous message: Tim Frawley: "IIS Lockdown and WSH and Distributed Assembly excecute permission denied"
• Next in thread: Ken Schaefer: "Re: How can I avoid using SQL Authentication with the Office Web Parts?"
• Reply: Ken Schaefer: "Re: How can I avoid using SQL Authentication with the Office Web Parts?"
• Reply: David Wang [Msft]: "Re: How can I avoid using SQL Authentication with the Office Web Parts?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]