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

[DarrylR]

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/d...singsqlserverusingmappedwindowsdomainuser.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.

 

[Ken Schaefer]

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/...77-4a21-4066-bd22-b931f7572e9a&DisplayLang=en
AuthDiag v1.0

Cheers
Ken

 

[David Wang [Msft]]

I've not directly dealt with your stated situation, but I'd like to offer
some viewpoints that can hopefully point you to the right direction.

I think your problem is caused by the fact that your extranet users
authenticate using Basic, yet you tell the web page (and web part) to
authenticate via another authentication protocol (Integrated) to the backend
SQL server. I'm not certain how IIS is supposed to translate between
different authentication protocols unless you use something like protocol
transition (see the URL below)

Although the following URL talks about IIS6 and UNC shares, the underlying
issues that it addresses is the same that you face with SQL. Namely, user
authenticates to IIS, which must authenticate to some remote server to
access a resource (be it a UNC share or SQL).

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/webapp/iis/remstorg.mspx


Your situation sounds like protocol transitioning is the solution.

FYI: using Integrated authentication with IIS6 in a domain will use Kerberos
by default. So, you already have half the puzzle all set up (as evidenced by
Intranet access working). Protocol transition allows IIS to take the basic
auth'd credential and get a kerberos ticket out of it, so that kerberos can
be used in Integrated authentication to access SQL.


--
//David
IIS
http://blogs.msdn.com/David.Wang
This posting is provided "AS IS" with no warranties, and confers no rights.
//
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/d...singsqlserverusingmappedwindowsdomainuser.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.

 

[DarrylR]

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.

 

[DarrylR]

David,

Thanks for the reply and references to suggested reading. I hadn't
considered the fact that I was mixing authentication methods for the
extranet users. I was trying to avoid a full Kerberos implementation by
using Basic authentication. However, I'm beginning to wonder if 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 say that because according to the NTAuthenticationProviders metabase key
(returned by adsutil.vbs), Kerberos is not enabled for the virtual directory
used by internal users (which uses Integrated Windows authentication); 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, I
haven't specifically enabled any user accounts or computers for delegation
or created any Service Principal Names. Therefore, I'm assuming that a true
double-hop should still fail, even from our intranet.

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.

I've not directly dealt with your stated situation, but I'd like to offer
some viewpoints that can hopefully point you to the right direction.

I think your problem is caused by the fact that your extranet users
authenticate using Basic, yet you tell the web page (and web part) to
authenticate via another authentication protocol (Integrated) to the backend
SQL server. I'm not certain how IIS is supposed to translate between
different authentication protocols unless you use something like protocol
transition (see the URL below)

Although the following URL talks about IIS6 and UNC shares, the underlying
issues that it addresses is the same that you face with SQL. Namely, user
authenticates to IIS, which must authenticate to some remote server to
access a resource (be it a UNC share or SQL).

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/webapp/iis/remstorg.mspx


Your situation sounds like protocol transitioning is the solution.

FYI: using Integrated authentication with IIS6 in a domain will use Kerberos
by default. So, you already have half the puzzle all set up (as evidenced by
Intranet access working). Protocol transition allows IIS to take the basic
auth'd credential and get a kerberos ticket out of it, so that kerberos can
be used in Integrated authentication to access SQL.


--
//David
IIS
http://blogs.msdn.com/David.Wang
This posting is provided "AS IS" with no warranties, and confers no

rights.

 

[DarrylR]

I couldn't wait to test it, so I tried it out today. Here's what I found:

If I log into my machine using one domain user account and then log into the
portal using a different account (by setting User Authentication/Logon for
the Trusted Sites zone in IE to "Prompt for user name and password"), the
Office Web Parts access the database using the credentials of the logged on
user, ignoring any impersonation. Windows SharePoint Services, on the other
hand, uses the credentials that I supply when logging into the portal (as it
should).

I'll keep you posted on my progress.

Regards,
Darryl R.

 

[DarrylR]

David,

I couldn't wait to test it, so I tried it out today. Here's what I found:

If I log into my machine using one domain user account and then log into the
portal using a different account (by setting User Authentication/Logon for
the Trusted Sites zone in IE to "Prompt for user name and password"), the
Office Web Parts access the database using the credentials of the logged on
user, ignoring any impersonation. This was using Integrated Windows
authentication.

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. Any thoughts on this?

With regards to Kerberos Constrained Delegation, the article that you
referred me to states that it will only work if the machines are members of
the same domain or trusted domains. Do you know whether delegation works
when the extranet domain has a one-way outgoing trust with the intranet
domain (extranet domain trusts users from the intranet domain)?

Regards,
Darryl R.

 

[David Wang [Msft]]

If I log into my machine using one domain user account and then log

into the portal using a different account (by setting User
Authentication/Logon for the Trusted Sites zone in IE to
"Prompt for user name and password"), the Office Web Parts
access the database using the credentials of the logged on
user, ignoring any impersonation. This was using Integrated
Windows authentication.

That does not sound like Office Web Parts ignoring impersonation. Rather,
it sounds like "Prompt for username and password" did not work and IE used
your logged on user credentials as authentication. The reason I say this is
because if impersonation was ignored on IIS by Office Web Parts, then the
only identity it has is the process identity, and you did not mention that
the Application Pool Identity was your user identity.

In other words, when you make a request to the web server and it runs Office
Web Parts, the web server's process identity is controlled by Application
Pool Identity, and the web server negotiates authentication with the web
browser for impersonation. If it succeeds, the Office Web Parts should run
as the impersonated identity, which is likely your logged on user identity
by default since that's what IE tries first; if Office Web Parts calls
RevertToSelf, it should get the Application Pool Identity. It is unlikely
for it to come up with a third credential unless you've specifically
configured it.

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. Any thoughts on this?

Exposing any functionality on a server creates a security risk. What is more
important is that you recognize the risk and take appropriate
caution/mitigation. A risk becomes a problem only if it is not
managed/mitigated and gets exploited. Life is full of risks, but it doesn't
mean we frown upon living. i.e. Driving is a risk on one's life, and
the two ways to think about it are either 1) don't drive, or 2) make sure to
learn how to drive safely and defensively.

In the case of MSADC, if it is only accessible to authenticated users (and
you make sure IUSR/anonymous user is not in Authenticated Users), it seems
like it mitigates an exploit to being an inside-job that likely gets
logged... Now, requiring Basic authentication is another risk to
mitigate...


I really do not have details on Kerberos implementation and usage. I would
imagine folks on microsoft.public.windows.server.security,
microsoft.public.windows.server.general or an Active Directory newsgroup to
have better info.

--
//David
IIS
http://blogs.msdn.com/David.Wang
This posting is provided "AS IS" with no warranties, and confers no rights.
//
David,

I couldn't wait to test it, so I tried it out today. Here's what I found:

If I log into my machine using one domain user account and then log into the
portal using a different account (by setting User Authentication/Logon for
the Trusted Sites zone in IE to "Prompt for user name and password"), the
Office Web Parts access the database using the credentials of the logged on
user, ignoring any impersonation. This was using Integrated Windows
authentication.

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. Any thoughts on this?

With regards to Kerberos Constrained Delegation, the article that you
referred me to states that it will only work if the machines are members of
the same domain or trusted domains. Do you know whether delegation works
when the extranet domain has a one-way outgoing trust with the intranet
domain (extranet domain trusts users from the intranet domain)?

Regards,
Darryl R.