IIS -> SQLServer on another machine

We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!Why don't you have your IIS and SQL Server on a domain and use domain
accounts?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:OGVqjoOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||What is meant exactly by having IIS and SQL server "on a domain"? I'm not t
he network administrator -- we don't really have one ;-( The guy with the
most experience with setting things up has said we're using ActiveDirectory
with Windows2003 server. Our SQL server 2000 is installed on Server1. IIS
is installed on server2. The DNS names of these servers are server1.{
ourdomain}.net and server2.{ourdomain}.net. Are these servers "on a do
main"?
BTW, we have tried to substitute a named domain user for the anonymous IUSR_
SERVER2 built-in user. Although I was able to grant that user login and dbac
cess and add it to a role, the IIS app didn't work. Does changing the machin
e user for IIS require a reboot of the IIS server?
Is there any way to see traffic between IIS server and the sql server box?
Thanks for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:%23IJPbwOEEHA.
1240@.TK2MSFTNGP10.phx.gbl...
Why don't you have your IIS and SQL Server on a domain and use domain accoun
ts?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message news:OGVqjoOEEHA.3472@.TK2MSFTNG
P09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||If you're using AD, then you're on a domain. Looks like you're almost there
by using a domain user for IUSR_SERVER2. You have granted that same user
access to SQL Server on Server1, as well as having granted the appropriate
access to databases and their objects. I don't have access to an IIS server
right now but I think you'd have to configure it to use the domain user
account. This would likely require stopping and starting the IIS service on
the server.
You could use Network Monitor to look at the traffic but I don't think you
need to get down to that level.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:O#CEd2QEEHA.712@.tk2msftngp13.phx.gbl...
What is meant exactly by having IIS and SQL server "on a domain"? I'm not
the network administrator -- we don't really have one ;-( The guy with the
most experience with setting things up has said we're using ActiveDirectory
with Windows2003 server. Our SQL server 2000 is installed on Server1. IIS
is installed on server2. The DNS names of these servers are
server1.{ourdomain}.net and server2.{ourdomain}.net. Are these se
rvers "on
a domain"?
BTW, we have tried to substitute a named domain user for the anonymous
IUSR_SERVER2 built-in user. Although I was able to grant that user login and
dbaccess and add it to a role, the IIS app didn't work. Does changing the
machine user for IIS require a reboot of the IIS server?
Is there any way to see traffic between IIS server and the sql server box?
Thanks for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23IJPbwOEEHA.1240@.TK2MSFTNGP10.phx.gbl...
Why don't you have your IIS and SQL Server on a domain and use domain
accounts?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:OGVqjoOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to
make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become
visible
to Server2 so it can be granted Login to SQLServer? Is this trusted
scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||316989 PRB: "Login Failed" Error Message When You Create a Trusted Data
http://support.microsoft.com/?id=316989
- Programmatically change the security context of the ASP.NET worker
process to a user who has the correct SQL Server permissions. -or-
- Change the default configuration of ASP.NET so that the ASP.NET
worker process starts and runs under the context of a user who has the
correct permissions in SQL Server. -or-
- Grant the correct permissions on SQL Server so that the aspnet_wp
account (or NetworkService account, for an application that runs on IIS
6.0) has the appropriate access to the required resources.
317012 INFO: Process and Request Identity in ASP.NET
http://support.microsoft.com/?id=317012
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We have taken option #2 from the post below (thank you Kevin). But what
should we be seeing in EventViewer/Security in the "user" column under those
circumstances? We're seeing SERVER2$ rather than the domain username we have
supplied. Is that expected behavior?
to recap: SQL Server 2000 running on Server1
IIS 6.0 running on Server2
Windows2003 / ActiveDirectory
We did this:
--edited the processModel section of machine.config setting userName to a
domain user with SQL logon and dbaccess rights, let's call it SQL_USER
-- supplied OURDOMAIN\SQL_USER as the "anonymous" user in the IIS dialog
-- edited machine.config: setting impersonate="true"
userName="OURDOMAIN\SQLUSER" password="pwd"
--Then we rebooted the machine on which IIS is installed
-- granted OURDOMAIN\SQL_USER read/write/modify rights to the ASPNET
temporary directory
With EventViewer->Security running on Server1 (the SQL Server), we look to
see who is trying to access the SQL machine from the IIS web pages.
Invariably the user appears as "SERVER2$" not as SQL_USER. What should we
see in EventViewer/Security on Server1? Is our reconfiguration of the
default ASP.NET user incomplete?
Thanks
Timo
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:lygZpHREEHA.3568@.cpmsftngxa06.phx.gbl...
>
> 316989 PRB: "Login Failed" Error Message When You Create a Trusted Data
> http://support.microsoft.com/?id=316989
> - Programmatically change the security context of the ASP.NET worker
> process to a user who has the correct SQL Server permissions. -or-
> - Change the default configuration of ASP.NET so that the ASP.NET
> worker process starts and runs under the context of a user who has the
> correct permissions in SQL Server. -or-
> - Grant the correct permissions on SQL Server so that the aspnet_wp
> account (or NetworkService account, for an application that runs on IIS
> 6.0) has the appropriate access to the required resources.
>
> 317012 INFO: Process and Request Identity in ASP.NET
> http://support.microsoft.com/?id=317012
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||It sounds like ASPNET process is running under localsystem. It is
attempting to connect using localsystem
credentials, and thus comes across as Server2$.
See if these resources help you :
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch08.asp
and
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch12.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.