Problem with ASP connection to SQL Server ODBC source, works local, fails with remote DB
Posted on 2008-11-03
I have a SQL Server 2005 Standard edition instance running on one computer, called SW-05, and the SQL Server instance name is DBSERVER
I have an IIS website running (ASP, not ASP.NET) on a seperate computer, called SW-04. A single COM+ object "BrokerWEB.DLL" interfaces the active server pages with the database.
I can configure an ODBC connection on SW-04 to point to DBSERVER on SW-05 with the SQL server login/password (not a windows account) and it tests successfully.
I have a small C++ executable that must interface with the remote database by way of the defined ODBC setting. This is working just fine, so I believe my ODBC settings are correct.
However, my web application fails to load when it points to a remote database.
When the SQL Server and the ASP code are running on the same computer however, everything works just fine. The web app loads and can interface with the database. It's only when I point the ASP code to a database on a remote computer that it fails.
The only thing I can "see" at this point is a call that returns a recordset to the ASP page is returning NULL. Again, all else the same, same code, same database contents, if the database is on the same computer as the web server, the call succeeds and the ASP code gets the recordset. Remote database, I'm not getting a recordset.
Where might I look to make changes to resolve this? I'll provide more details on request only because I'm not sure what is relevant and what is not, to this question.