SQLXML HTTP 401.3 Error

[Andrew888]

Trying to run template queries using Windows 2000 Server and SQL Server 2000. I have done this successfully off another machine using SQL Server 2K and Windows XP Pro, but for some reason, this Windows 2000 Server machine is giving me fits.

SQL Server is logging in using the SYSTEM account. I have setup a SQL user as a datareader in the appropriate database - and configured my virtual directory appropriately (using the SQL 2000 utility for configuring XML access)...again, this is all stuff I've done on another machine and it works fine -- but not this one machine...When I pass the URL to call the template query, I receive:

-----------------------------------------------------------
You are not authorized to view this page

You do not have permission to view this directory or page using the credentials you supplied.

Please try the following:

**Click the Refresh button to try again with different credentials.
**If you believe you should be able to view this directory or page, please contact the Web site administrator by using the e-mail address or phone number listed on the 10.3.1.38 home page.

HTTP 401.3 - Access denied by ACL on resource
Internet Information Services
-----------------------------------------------------------

Can anyone please recommend anything?

Thanks,
Andrew

 

[Hal10K]

Andrew,
You said it right in the second paragraph:
>>SQL Server is logging in using the SYSTEM account.

Since SYSTEM has no network rights, it won't be able to see the resource, that is unless IIS is on the same server (bad idea, not recommended, see BOL).

We set up a domain user account for SQL servers, and restrict it down to "least priviledge possible", but the NT account runs with system administrator priviledges in SQL. (You should also set the PW not to change, and restrict access to that account except for those that need to know). When we do this, it allows us to have servers talk to each other for replication, use mapi for email, use the SQL agent (should use the same domain account) and all other sorts of good things.

You can also use NTFS permissions on the IIS side to restrict what the domain user account can do on IIS.

Hal
(Enterprise DBA & Intranet Webmaster)