Connecting to SQL Server from ASP

[nzanella]

Hello,

I have MS SQL Server running on a server computer. I am trying
to connect to it via ASP from a .asp file created in a virtual
directory. I am using IIS as the web server. I am connecting
from a Windows 2000 computer.

When I go to Start -> Settings -> Control Panel
-> Administrative Tools -> ODBC Data Sources

I see a data source which uses SQL Server. I click on it,
click Configure..., then Next, and notice that if I place
the password I can connect fine from the "ODBC Data Source
Administrator".

However, when i try the following from ASP I get an error:

<%

cncString = "DSN=X;" _
+ "Database=Y;" _
+ "UID=foo;" _
+ "PWD=bar"

Response.Write(cncString)

cnc.Open cncString

%>

The server error I get is the following:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
/neil/Default.asp, line 30

This seems strange to me given that I can connect from the
ODBC Data Source Administrator without much trouble.

The only reason for this error which I can think of is that
the ODBC data sources may not be running on the web server
itself, but on yet a third machine, and I do not have access
to the web server itself right now, so that may be the problem.

Please let me know about any other probems which may arise
once I can get my hands on the web server itself,

Thank you so much for your suggestions and help,

Best Regards,

Neil Zanella

[Madhivanan]

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
/neil/Default.asp, line 30

Make sure you created DSN with the name X

Madhivanan

[Serdar Yegulalp]

> <%

cncString = "DSN=X;" _
+ "Database=Y;" _
+ "UID=foo;" _
+ "PWD=bar"

Use & to concatenate strings in ASP, not +.

[Damien]

Okay, the two ways I have used to connect from plain old ASP are:

1) Save a System (not User) DSN, and then set the connection string
just as:

cncString = "DSN=SqlConnect"

(if SqlConnect is the name of the DSN)

or,

2) Construct the whole connection within the ASP (dont touch ODBC at
all) such as:

cncString = "Provider=sqloledb;Data Source=" & myServer & ";Initial
Catalog=" & myDatabase & ";"

then add on either:

cncString = cncString & "Integrated Security=SSPI;"

or

cncString = cncString & "uid=" & myUser & ";pwd=" & myPassword & ";"

depending upon whether I'm using windows authentication or sql
authentication.

-------------------------------

May as well piggy back my own query on here, since it may turn out to
be relevant to OP:

We've recently been setting up an intranet site where we wanted to ID
the users from the browser using Windows Auth and then connect to the
DB as them using Windows Auth. Is there *still* no way of achieving
this when IIS/SQL are on different boxes? Any patches we may be
missing? We've switched to doing the browser authentication using basic
digest, but that isn't ideal. Windows/SQL are both 2000 with latest
stable SPs.