Configuring SQLXML IIS

I have configured a virtual directory with my local SQL
Server and local IIS. I am able to execute my templates
without any problems. Pretty cool stuff!
Now I would like to do the same on our production SQL
Server and web server. The SQL Server and web server are
on two different machines. I have read that it is
possible to do this but I am confused as to which
computer I need to connect to through the IIS Virtual
Directory Management console. I am assuming it is the
web server. I tried both the SQL and web server and got
the same error - "An error occured connecting to
<server>. This server does not exist or is unavailable."
1. Can the web server reside on another machine?
2. Which server needs to be configured?
3. Why would I receive the error that I am getting?
Thank you for any assistance that you can provide.
Jason
Yes, the Web Server can be separate from the SQL Server. It's the Web server
you need to configure with the IIS Virtual Directory Management tool, but it
will need to have SQLXML installed. You'll also need to think carefully
about how you configure authentication in your SQLISAPI Virtual Directory.
As for the error, at face-value it looks like a networking issue - I assume
you've tried the usual troubleshooting approaches (i.e. pinging the server
by name or by IP address)?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:19bc001c42247$c41fd760$a601280a@.phx.gbl...
> I have configured a virtual directory with my local SQL
> Server and local IIS. I am able to execute my templates
> without any problems. Pretty cool stuff!
> Now I would like to do the same on our production SQL
> Server and web server. The SQL Server and web server are
> on two different machines. I have read that it is
> possible to do this but I am confused as to which
> computer I need to connect to through the IIS Virtual
> Directory Management console. I am assuming it is the
> web server. I tried both the SQL and web server and got
> the same error - "An error occured connecting to
> <server>. This server does not exist or is unavailable."
> 1. Can the web server reside on another machine?
> 2. Which server needs to be configured?
> 3. Why would I receive the error that I am getting?
> Thank you for any assistance that you can provide.
> Jason
>
|||Thanks Graeme, what do you mean about configuring
authentication? Are you referring to a user's ability to
access the templates? That was going to be my next
question!
Say I have these templates in my virtual directory that
can be called via HTTP. Anyone can get at them. Is
there a way to pass user credentials to the template in
order to authenticate the user who is trying to access
the template?
Jason

>--Original Message--
>Yes, the Web Server can be separate from the SQL Server.
It's the Web server
>you need to configure with the IIS Virtual Directory
Management tool, but it
>will need to have SQLXML installed. You'll also need to
think carefully
>about how you configure authentication in your SQLISAPI
Virtual Directory.
>As for the error, at face-value it looks like a
networking issue - I assume
>you've tried the usual troubleshooting approaches (i.e.
pinging the server
>by name or by IP address)?
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>"Jason" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:19bc001c42247$c41fd760$a601280a@.phx.gbl...
templates[vbcol=seagreen]
are[vbcol=seagreen]
unavailable."
>
>.
>
|||There are two things that require authentication here.
1. The virtual directory (and the physical template file). You'll need to
decide if your going to allow anonymous access to the virtual directory, in
which case the IUSR_computername accunt will need NTFS file permissions on
the virtual directory folder and the folder containing the templates (if
different), or whether you'll use Windows authentication (in which case the
user's domain account needs the appropriate NTFS permissions. This is just
like any other IIS Web site, and you can use the standard IIS admin tool to
configure site authentication.
2. SQL Server. You've got 4 choices here: (a) Have IIS connect to SQL Server
on the user's behalf, using Windows authentication (select "Always log on
as" and specify the Windows credentials in the virtual directory properties
Security tab - by default it will use the IUSR_computername account and
allow you to automatically synchronize the password. Alternatively, you can
specify any Windows account that you want to and specify the password), (b)
Have IIS connect to SQL Server on the user's behalf, using SQL Server
authentication (select "Always log on as" and specify the SQL Server login
credentials in the virtual directory properties Security tab - you might
also need to enable SQL Server authentication on the SQL Server since by
default it only uses Windows authentication), (c) Have IIS impersonate the
user's Windows credentials when accessing SQL Server (Select "Use Windows
Integrated Authentication" in the virtual directory properties Security
tab), or (d) Have IIS prompt for the user's SQL Server credentials and pass
them to SQL Server using clear text (Select "Use Basic Authentication (Clear
Text) to SQL Server account" in the virtual directory properties Security
tab).
In more general terms (a) and (b) are a form of the "Trusted Server" model
(in which the Web Server application is trusted to use its own credentials
to access the database on the user's behalf), while (c) and (d) are forms of
the "Impersonation/Delegation" model, in which the Web Server accesses the
database using the user's credentials.
In most cases, I'd tend to recommend (a), because it will allow IIS to use
connection pooling (and therefore make the application more scalable).
However, it does mean that everyone who accesses the database through the
Web site uses the same login to SQL Server. If your application is on an
Intranet and you need to force users to use their own account when accessing
SQl Server, then I'd go for (c), in which case Internet Explorer will
automatically send their domain credentials (assuming they're logged into a
domain) or prompt them for their Windows login. The main thing is that the
credentials are encrypted on the network. If you were to use (d) you'd have
to configure the virtual directory to use SSL in order to pass the login
details securely.
Hope that helps,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:1d43801c422ec$75616580$a101280a@.phx.gbl...[vbcol=seagreen]
> Thanks Graeme, what do you mean about configuring
> authentication? Are you referring to a user's ability to
> access the templates? That was going to be my next
> question!
> Say I have these templates in my virtual directory that
> can be called via HTTP. Anyone can get at them. Is
> there a way to pass user credentials to the template in
> order to authenticate the user who is trying to access
> the template?
> Jason
>
> It's the Web server
> Management tool, but it
> think carefully
> Virtual Directory.
> networking issue - I assume
> pinging the server
> message
> templates
> are
> unavailable."