Office Web Parts / Office Web Components connecting to SQL Server from extranet

[DarrylR]

I am using Office Web Parts (specifically the PivotView) on several Web Part
pages. The IIS virtual directory serving the pages uses basic security over
SSL. The pages with Office Web Parts on them work correctly when accessed
via our intranet; users are logged on seamlessly using their domain
credentials, and the PivotView parts retrieve data from our SQL Server
database and display it in the page. However, when we access these pages via
our extranet, even if the user logs in using their domain credentials, the
Office Web Parts fail to retrieve data because of a SQL login failure
("Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."). All other pages on the portal are displayed correctly
from the extranet.
The IIS box (running WSS) is outside our firewall, and the SQL Server box is
behind our firewall. The machines are on different domains with a one-way
trust relationship (the extranet domain trust accounts from our intranet
domain). 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). Using connection strings
similar to the following, the Office Web Parts fail to login (when the pages
are accessed from the extranet):

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 connection
string is written 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/d...singsqlserverusingmappedwindowsdomainuser.asp

However, the steps outlined in the article didn't correct the problem. I
also read the following article which suggested that we may need to open
more outbound ports on the firewall to make this work:

http://support.microsoft.com/kb/287932

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?

 

[DarrylR]

Just to be clear, we're already using the <identity impersonate="true">
element in web.config.

And everything works
a) if we use SQL Authentication in the connection strings or
b) if you access the portal from within our network (from a machine behind
the firewall)

Thanks,
Darryl R.

 

[fatman]

most firewalls are going to strip out NTLM. I believe you are going to
have to modify your SQL authentication.

-Ryan

 

[DarrylR]

Ryan,

Thanks for your reply. We've actually opened up the correct ports to pass
Kerberos authentication, although we're not using it. Based on my research
over the weekend, I have concluded that the problem is actually the Office
Web Parts. Here's why:

As you may be aware, user impersonation/delegation can be accomplished in
one of two ways in IIS. The first is using Basic authentication over SSL.
The second is using Integrated Windows authentication with Kerberos. Both of
these methods are supposed to store enough information about the user to
overcome the "double-hop" dilemma presented by a Browser->IIS->SQL Server
architecture. The advantage of using Basic authentication to accomplish
impersonation is that you don't have to designate users or machines for
delegation, nor do you have to create Service Principal Names. That's why I
chose Basic authentication; I didn't want to incur the added overhead of a
full Kerberos implementation.

Basic authentication isn't working with the Office Web Parts, but I'm fairly
confident that a full Kerberos implementation won't work either. To test
this theory, I performed the following test from our intranet (accessing the
portal from the virtual directory secured using Integrated Windows
authentication):

I logged into a machine using one domain user account, launched Internet
Explorer, and accessed the portal using a different account. According to
SQL Profiler, the Office Web Parts accessed the database using the
credentials that I used to log onto the machine, not those used to access
the portal. This suggests that despite the existence of the <identity
impersonate="true"> element in Web.config, the Office Web Parts do not
impersonate portal users.

So, either connection strings that include the 'Integrated Security=SSPI'
parameter force the data connection to use the current Windows user's
credentials regardless of the credentials that they supply to IIS, or it's
an inherent limitation of the Office Web Parts. Another limitation that I've
noticed is the fact that server names supplied in the Office Web Part
connection string are resolved using the client's DNS, not on the server.
Therefore, any attempt to access data externally would require an externally
visible SQL Server (and opening the appropriate ports in the firewall).

I'm awaiting some feedback from Microsoft on these findings. I'll keep this
thread updated with the result.

Regards,
Darryl R.