[Zope] MSSQL7 ZSQL method works, can't use in DTML

14 Mar 2001

      Hi all-
	I'm trying to set up a dual-zope arrangement to allow use of my
MS-SQL Server 7.0 database data in linux-based web docs.  Perusal of the
Zope Book & the mailing list archives got me only so far -- specifically, I
can connect to the database, I can browse it from the ZODBC object, I can
create a ZSQL Method using it, and test it, and get the data I oughta get.
All of this had me thrilled, until I tried to use the ZSQL method in a dtml
document, as follows:
	<dtml-var standard_html_header>
	<H2>Here's SQL output:</H2>
	<UL>
	<dtml-in test>
	   <LI><dtml-var tour_name></LI>
	</dtml-in>
	   <!-- tour_name is the name of the field I was querying out of the
database, as in "SELECT tour_name FROM tour_info" -->
	   <!-- test is the name of the ZSQL Method -->
	</UL>   
	<dtml-var standard_html_footer>
This document, when viewed, starts prompting me for username & password, and
refuses to accept anything that oughta work.  I've created a user in MS-SQL
specifically for this purpose, and it doesn't work.  My own privileged login
name and password don't work.  I've tried creating variations on the basic
ODBC datasource, wherein the connection type is SQL-Server LID/PW instead of
trusted connection; TCP-IP connection instead of Named Pipe; I've even tried
using a dummy Access DB with linked tables (that work fine in Access) to
create an MS-Access ODBC datasource.  All of these fail in the exact same
way:
	Zope Error

	  Zope has encountered an error while publishing this resource. 

	  Unauthorized

	  Sorry, a Zope error occurred.

	  Traceback (innermost last):
	    File C:\Program
Files\ZopeXPlus\lib\python\ZPublisher\Publish.py, line 222, in
publish_module
	    File C:\Program
Files\ZopeXPlus\lib\python\ZPublisher\Publish.py, line 187, in publish
	    File C:\Program
Files\ZopeXPlus\lib\python\ZPublisher\Publish.py, line 171, in publish
	    File C:\Program Files\ZopeXPlus\lib\python\ZPublisher\mapply.py,
line 160, in mapply
	      (Object: SqlOutput)
	    File C:\Program
Files\ZopeXPlus\lib\python\ZPublisher\Publish.py, line 112, in call_object
	      (Object: SqlOutput)
	    File C:\Program Files\ZopeXPlus\lib\python\OFS\DTMLDocument.py,
line 170, in __call__
	      (Object: SqlOutput)
	    File C:\Program
Files\ZopeXPlus\lib\python\DocumentTemplate\DT_String.py, line 502, in
__call__
	      (Object: SqlOutput)
	    File C:\Program
Files\ZopeXPlus\lib\python\DocumentTemplate\DT_In.py, line 682, in renderwob
	      (Object: test)
	  Unauthorized: 0
(once again, SqlOutput is the name of the dtml doc in question.)

I have a feeling that http://www.zope.org/Members/roberth/ZMSSQLServer is a
vital clue, but I haven't found it very helpful yet.  This is mainly because
I don't see any failed login attempts in the SQL Server logs, and I am
without clue as to how one verifies/sets the user the main zope process runs
as in a Windows environment.

Oh, yeah.  Machine #1 (database): NT 4.0, SQL Server 7.0; Machine #2
(windows connection to DB): Win98, Zope 2.3; Machine #3 (eventual Linux
webserver): Redhat 6.2, Zope 2.3, etc.  

Any help greatly appreciated.
--Rich Young
rich at xplus dot com