Internet and would like to know if someone can tell me if it is possible to
do the following:
- IIS 6 machine serving ASP pages, integrated authentication, machine
trusted for delegation in the domain
- SQLServer 2000 machine with mixed authentication (same domain as IIS 6
machine)
- I want the ASP pages to access the SQLServer machine using the
authentication credentials provided to IIS as part of integrated
authentication
As far as I can determine, it should be possible as covered in this article
http://support.microsoft.com/kb/319723/en-us. However, I'm currently
getting the error:
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
My problem is that I've tried to make this work in our environment in the
past without success. A couple of months ago, it suddenly started working,
and then recently (a day ago) stopped again. The system administrators say
no changes were made that would cause it to stop working.
When researching this issue again on the Internet I did not find many people
trying to do what I wanted to do. Understandably, most people are
interested in solutions where the users accessing the web pages are not
domain users that have access to the SQLServer database - but in my
situation that's what I have. The only solutions I saw offered to this
solution were to turn on Basic Authentication (I know this will work, but do
not want to do this as it negatively impacts the user experience and do not
want passwords crossing the network in the clear) or to setup some other
domain account (I don't want to do this because I want to be using the user
accounts) that IIS would use to access SQLServer. No mention was made of
the kerberos/delegation solution that I identified above.
Is it possible to do what the article describes above? Is there any reason
it should not work? Has anyone had success with the above article?
Thanks, TylerHello Tyler,
I understand that you'd like to use IIS Intergration authentication in the
intranet, and ASP "impersonates" authencitaed users to access SQL Server on
a different machine in the same domain. However, you receive
'80004005'error. If I'm off-base, please let me know.
As you know, only kerberos authentication allows double-hops from clients
via IIS to SQL Server. Windows NT Challenge/Response does not support
double-hop impersonations (meaning that once passed to the IIS server, the
same credentials cannot be passed to a back-end server for authentication,
for example, when IIS uses Windows NT Challenge/Response, it cannot then
authenticate the user against a SQL Server database on another computer by
using SQL Integrated security). Please the following article for details:
How IIS authenticates browser clients
http://support.microsoft.com/defaul...kb;en-us;264921
Per your question, it is possible that IIS uses kerberos authentication and
impersontes the domain user to access backend SQL Server. This is also what
described in 319723. Though some old KBs mention that ASP/IIS must use
Basic Authentication if SQL Server is on a diferent machine, it is not true
any more.
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/defaul...kb;en-us;247931
PRB: "Client Unable to Establish Connection" Error Message When Connecting
from ASP to SQL Server
http://support.microsoft.com/defaul...kb;en-us;253500
You may want to start from 319723 which I think you have done. Also, you
must use TCPIP for Kerberos of SQL Server. Named pipes do not support
Kerberos delegation. Named pipes use NTLM only.
From the error, "Login failed for user '(null)'", it seems the your SQL
Server doesn't have an SPN registered properly. I suggest that you manually
add the SPN
setspn -A MSSQLSvc/<FQDN of SQL Server>:<port> <Sql Service Account>
setspn -A MSSQLSvc/<server hostname>:<port> <Sql Service Account>
For example:
setspn -A MSSQLSvc/sql1.test.com:1433 testdomain\sqlservice
setspn -A MSSQLSvc/sql1:1433 testdomain\sqlservice
You may want to use the following command to see the SPN registred to the
sql server startup account.
setspn -L <domain name>\<sql server startup account>
How to troubleshoot the "Cannot generate SSPI context" error message
http://support.microsoft.com/kb/811889
If the issue persists, you may want to enable security event logging and
Kerberos event logging on both SQL Server and IIS Server to see more
details:
How to troubleshoot Kerberos-related issues in IIS
http://support.microsoft.com/kb/326985/
262177 How to enable Kerberos event logging
http://support.microsoft.com/kb/262177
If the Internet Explorer client is set to use a proxy server, you must
click to select the Bypass Proxy Server for local addresses check box. To
verify that the Internet Explorer client is set to use a proxy server, this
follow these steps: a. Start Internet Explorer.
b. On the Tools menu, click Internet Options, and then click the
Connections tab.
c. Click LAN Settings. Under Proxy server, verify that the Bypass proxy
server for local address check box is checked.
Also, if Anonymous authentication is enabled, IIS will always try to
authenticate by using it first, even if other methods are enabled. If
Anonymous authentication, Integrated Windows authentication, and Basic
authentication are all selected, Integrated Windows authentication takes
precedence over Basic authentication, after Anonymous authentication.
More related information
Troubleshooting Kerberos Delegation
http://www.microsoft.com/downloads/...f94f-e28a-4726-
bffe-2f64ae2f59a2&displaylang=en
Download the Kerbtray and verify Kerberos tickets from any of the
associated computers that are being used.
http://www.microsoft.com/windows200...isting/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You have described exactly what we want to see work. I will let you know if
I'm still not able to get it working with all of the information you've
problem.
Thank you very much,
Tyler|||Hello Tyler,
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hello Tyler,
I'm still interested in this issue. Do you have any update of the test? If
you have any comments or questions, please feel free to let's know. We look
forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Peter,
The information and links you provided were quite helpful. I believe it was
an SPN problem, but unfortunately I cannot say for certain. I had to hand
the information and request off to another member of our IT Support team to
resolve because I am not a domain administrator and didn't have the
necessary access rights to perform all of the diagnosis. I am glad to
report though that the issue has been resolved and things are working as we
expect.
Thanks very much for your assistance with this issue - your help was
invaluable
Tyler
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:pUb3csfNHHA.2300@.TK2MSFTNGHUB02.phx.gbl...
> Hello Tyler,
> I'm still interested in this issue. Do you have any update of the test?
> If
> you have any comments or questions, please feel free to let's know. We
> look
> forward to hearing from you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hello Tyler,
My Pleasure. :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
No comments:
Post a Comment