You need 1 more thing in your web config
<identity impersonate="true">

This takes the credential of the web visitor and passes them to the SQL
Server
(Yes as long as every server involved is in the AD, and using NT
credentials it will work fine)

The web needs to have "anonymous" turned off forcing the client to send
auth credentials when connecting with IIS.

I run several apps like this in our intranet. NT auth works fine !

Dano

-----Original Message-----
From: Goodge Greg
[mailto:mssqldba-ezmlmshield-x96694745.[Email address protected]
Sent: Friday, February 25, 2005 10:30 AM
To: LazyDBA Discussion
Subject: RE: IIS And SQL Server
Importance: Low

Craig,

What you're trying to do is possible, although, in my experience, not
easy. The crux of the problem is that basically you're trying to make a
"double-hop" which is authenticating a user to a web server and then
trying to pass those credentials on to a database server. In order for
this to work, you need to use "Windows Authentication" only on the web
server and you need to configure delegation. Here is a Microsoft
article that talks about it:

http://support.microsoft.com/default.aspx?kbid=810572

You said that this is an Intranet site. That's good, because all the
servers involved need to be members of the same Active Directory domain.


Hope this helps,

Greg



-----Original Message-----
From: CBerko
[mailto:mssqldba-ezmlmshield-x82347552.[Email address protected]
Sent: Friday, February 25, 2005 9:47 AM
To: LazyDBA Discussion
Subject: IIS And SQL Server

What I am trying to do is have my web pages run on a dedicated IIS
server
and have them access SQL databases on separate SQL Servers. I want to
use
Windows Authentication to control access to the data since this is
strictly
an intranet site.

Here is what I have tried so far:

If I run IIS on the same box as SQL Server, I have no problems.

However, as soon as I try to run the WebPages from a separate IIS
server,
the problems begin. The only way that I have been able to get it to
work so
far is to do the following:

Change SQL Server so that it is running in Windows Authentication mode
only.
Set up IIS to use an anonymous login account with access to the
database.
This is a domain user account.
Check the "Use Anonymous Login" AND "Windows Authentication" checkbox on
the
IIS
If I set SQL Server to run in mixed mode, then I get a Login failed for
user
'NT AUTHORITY\ANONYMOUS LOGON'. error
If I leave SQL Server in Windows Authentication only mode and uncheck
the
Anonymous Login, I get a Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON' error

My web.config has the following line in the Authentication Section:

<authentication mode="Windows" />

What steps do I have to take to set up SQL Server and IIS on separate
boxes
when I can leave my SQL Server in Mixed Mode and have IIS connect with
Windows Authentication?


Thanks,

Craig Berko
SQL Server DBA / Software Engineer
The MHA Group
(469) 759-8551




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]


MS Sql Server LazyDBA home page