Command and ODBC connection question
[1/3] from: swhite:ci:bloomington:mn:us at: 29-Oct-2009 10:58
I assume this problem is related to a userID and password that I must put somewhere,
but I am not sure where to look and I wonder if anyone can point me.
I want to write a CGI program for REBOL/Command which I have purchased, to look up a
record in a table in a database maintained by Microsoft SQL Server, through ODBC (which
I assume is the only way). I have isolated the problem with the following script:
--------------
#!c:/rebol-cmd/bin/rebcmd.exe -cs
REBOL [
]
print "content-type: text/html"
print ""
print ""
print [<HTML><BODY><PRE>]
COG-CON: open odbc://cogsdale
COG-CMD: first COG-CON
print ["Database is open"]
close COG-CMD
print ["Database is closed"]
print [</PRE></BODY></HTML>]
----------------
When I run this script at a REBOL/Command prompt, that is, not through a browser, it
works just fine, as shown by the following result:
----------------------
>> do %odbctest.r
Script: "Untitled" (none)
content-type: text/html
<HTML> <BODY> <PRE>
Database is open
Database is closed
</PRE> </BODY> </HTML>
>>
---------------------
But when I run it as a CGI program, through a little test harness that looks like this...
-----------------------
<HTML>
<HEAD>
<TITLE>Program runner</TITLE>
</HEAD>
<BODY>
<A HREF="http://10.66.250.149/cgi-bin/odbctest.r">Run ( 'http://10.66.250.149/cgi-bin/odbctest.r"
) the program</A>
</BODY>
</HTML>
----------------------
...I get the following result:
-----------------------
** Script Error: ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user 'COB-DOMAIN\IS-SWHITE$'.
** Near: COG-CON: open odbc://cogsdale
COG-CMD: first
------------------------
The script I run is not the actual same script in both cases because it must exist in
the cgi-bin directory and the REBOL command directory, but it IS the same, as in an exact
copy. I copied it before I ran it, and I went back and verified that they were the same
after I had run them. I am very aware, from sad experience, that this kind of situation
is a source of errors.
I know that I can put a user ID and password on this line:
COG-CON: open odbc://cogsdale
to make it like this:
COG-CON: open odbc://userid:password-cogsdale
and I tried it with the password that I believe is my Windows password. The ODBC connection
is set up to use Windows authentication. I also tried to change the ODBC connection
to NOT use Windows authentication, and the userID and password I entered in the required
boxes was rejected. (Maybe that is the key.)
So I am scratching my head a bit here and wondering if anyone can offer guidance.
Thank you.
Steven White
City of Bloomington
1800 W Old Shakopee Rd
Bloomington MN 55431-3096
USA
952-563-4882 (voice)
952-563-4672 (fax)
steven.white-ci.bloomington.mn.us
[2/3] from: louisgosselin:accesrail at: 29-Oct-2009 13:02
Steven,
No a direct answer to your problem but maybe will be of some help:
You have to be able to test successfully your Data Source (DSN) with the
authentication method / login / password you will be using from the
Windows configuration panel before trying from Rebol.
I remember that not all combination of drivers and network library will
work with the selected authentication method. For example Named Pipes
library vs SQL server authentication. But I don't remember which
combinations are problematic.
Currently we use SQL Native client (for which you can't specify the
network library) and SQL server authentication without problems on SQL
Server 2003.
Good luck. Louis.
Steven White wrote:
[3/3] from: swhite:ci:bloomington:mn:us at: 30-Oct-2009 12:22
I don't know if this original message made it through, but in case it did, I will report
that I managed to find the solution.
It seems that the ODBC connection I was using used Windows authentication, and the web
server (apache) must have been passing some other authentication to REBOL which was passing
it to the SQL Server, or something like that. I don't really know how any of this stuff
works.
Anyway, the solution was to make a new user ID in SQL Server and set it up so that it
does NOT accept Windows authentication, but instead requires a user ID and password.
Then I made new ODBC data source that does not accept Windows authentication, but instead
uses a user ID and password, and made it the same user ID and password that matches those
I set up in SQL Server. Then I supplied that new ODBC data source along with its userID
and password in the REBOL line that defines the command port to the database.
Now that I have connected to the database, I am struggling along with trying to get something
out of the database, but that's another story.
Thank you.
>>> "Steven White" <swhite-ci.bloomington.mn.us> 10/29/2009 10:58 AM >>>
I assume this problem is related to a userID and password that I must put somewhere,
but I am not sure where to look and I wonder if anyone can point me.
I want to write a CGI program for REBOL/Command which I have purchased, to look up a
record in a table in a database maintained by Microsoft SQL Server, through ODBC (which
I assume is the only way). I have isolated the problem with the following script:
--------------
#!c:/rebol-cmd/bin/rebcmd.exe -cs
REBOL [
]
print "content-type: text/html"
print ""
print ""
print [<HTML><BODY><PRE>]
COG-CON: open odbc://cogsdale
COG-CMD: first COG-CON
print ["Database is open"]
close COG-CMD
print ["Database is closed"]
print [</PRE></BODY></HTML>]
----------------
When I run this script at a REBOL/Command prompt, that is, not through a browser, it
works just fine, as shown by the following result:
----------------------
>> do %odbctest.r
Script: "Untitled" (none)
content-type: text/html
<HTML> <BODY> <PRE>
Database is open
Database is closed
</PRE> </BODY> </HTML>
>>
---------------------
But when I run it as a CGI program, through a little test harness that looks like this...
-----------------------
<HTML>
<HEAD>
<TITLE>Program runner</TITLE>
</HEAD>
<BODY>
<A HREF="http://10.66.250.149/cgi-bin/odbctest.r">Run ( 'http://10.66.250.149/cgi-bin/odbctest.r"
) the program</A>
</BODY>
</HTML>
----------------------
...I get the following result:
-----------------------
** Script Error: ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user 'COB-DOMAIN\IS-SWHITE$'.
** Near: COG-CON: open odbc://cogsdale
COG-CMD: first
------------------------
The script I run is not the actual same script in both cases because it must exist in
the cgi-bin directory and the REBOL command directory, but it IS the same, as in an exact
copy. I copied it before I ran it, and I went back and verified that they were the same
after I had run them. I am very aware, from sad experience, that this kind of situation
is a source of errors.
I know that I can put a user ID and password on this line:
COG-CON: open odbc://cogsdale
to make it like this:
COG-CON: open odbc://userid:password-cogsdale
and I tried it with the password that I believe is my Windows password. The ODBC connection
is set up to use Windows authentication. I also tried to change the ODBC connection
to NOT use Windows authentication, and the userID and password I entered in the required
boxes was rejected. (Maybe that is the key.)
So I am scratching my head a bit here and wondering if anyone can offer guidance.
Thank you.
Steven White
City of Bloomington
1800 W Old Shakopee Rd
Bloomington MN 55431-3096
USA
952-563-4882 (voice)
952-563-4672 (fax)
steven.white-ci.bloomington.mn.us