ASP => Access => SQL Server

[Sc0uSe]

Hi,

I have a manager who's rewritten the Access database that my ASP queries (successfully) so that it now runs queries against a SQL Server database.

The problem I'm getting is when I try to do the recordset open, it's giving me an ODBC &quot;connection to <dsn> failed&quot;.

When running the query thru access, it asks for the uis & pw - I think this is where the ASP is falling down. It open the access connection ok, but can't pass security info on thru SQL Server. Would this be fixed by setting the Access=>SQL Server linked tables as Trusted Connections? What entry would I need to put in the Trusted Connections group to allow the ASP to get thru?

Bit of unexplored territory here for me ...

Sc0uSe

 

[Karl Blessing]

if it's saying DSN failed, you need to make sure it's DSN exist on the server that the ASP is running off of. and that it's configured to point to a SQL server DB, and not a Access DB anylonger.

 

[Sc0uSe]

The ASP is still accessing the Access DB, but the access DB now runs qeries against the SS db, and to run those queries, it needs to log on to the SS db ...

 

[Karl Blessing]

by pointing the DSN to the SS Db.

 

[Sc0uSe]

but I'm not trying to access the SS directly - I'm running a query on the access DB which returns a resultset ... but the access DB links to the SS db.

If I run the query from access, it asks me to log on to SS and then runs.

If I run the query from ASP, it fails ...

 

[palbano]

> but I'm not trying to access the SS directly - I'm running a query
> on the access DB which returns a resultset ... but the access DB
> links to the SS db.


That is an absolutly awful design. Change the design as Karl states.

-pete

 

[Sc0uSe]

The design may be awful, but that's what I have to live with here.

No direct access to the SS is allowed, and the to$$er who owns the Access DB refuses to change it. No, he's not a programmer - he's a 'manager'.

 

[palbano]

Have you tried the MS Access forums here at TT. You might find a solution in there. This is really an MS Access issue.

> The design may be awful, but that's what I have to live with here.

I don't even know how to respond to that. If you are working with/for individuals that will not act responsibly and professionally or for some reason are incapable of making reasonable decisions, then perhaps that is the problem you should be trying to solve.

&quot;But, that's just my opinion... I could be wrong&quot;.
-pete

 

[nalbiso]

I am little confused by your question, but when you set up your ODBC,
did you make sure to put the user name and password for the SQL login
during the config?

I currently run an Access database which updates a SQL table that feeds an
ASP and when I did it, I just did a regular link ODBC tables and it worked.