Problem with ASP connection to SQL Server ODBC source, works local, fails with remote DB

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you checking and receiving an error when you try and connnect, through your ASP code, to SQL Server?
In the "SQL Server Configuration Manager" tool, there are settings for "SQL Server 2005 Network Configuration".  Under this section, select "Protocols for XXX".  
Ensure that tcp/ip is enabled.  On some/most networks, if this is not enabled, a remote "client" will not be able to connect to the server.
Also, through "Data Sources (ODBC)", on the client, you should test your connection to ensure it will connect.  If it passes this test, but fails thorugh the application, then the issue may be permissions.
Are you handling/catching an exception?  Is an error returned?
PMH4514Author Commented:

tcp/ip is enabled.  all firewalls are currently disabled.
sqlbrowser is also setup properly.

>>Also, through "Data Sources (ODBC)", on the client, you should test your connection to ensure it will >>connect.  If it passes this test, but fails thorugh the application, then the issue may be permissions.

Yup, the connection test in the ODBC dialog passes. As well, my small C++ application which is running on the client, and connects to the remote DB by way of the ODBC setting, works properly. It specifies the same user/password credentials as my ASP code is trying to access the database with.

It is only the website which fails when the database is remote. When it's local all code runs fine.

>>Are you handling/catching an exception?  Is an error returned?

Well, I have exception handling in place at the COM+ level  that uses a disconnected recordset to report errors from the COM+ layer to the ASP code.

ie. every call to the DLL from ASP will get a recordset back. Either containing the fields I'd expect, or if there was an error executing something, I create a disconnected recordset with fields to hold all the exception data. A flag is passed by reference from ASP to the DLL and is set as true if an error arose, thus, if error, the ASP code can instead call my HandleError(recordset) method to display the error conditions.

So for example, the first SQL call which happens when my COM+ object is initialized in global.asa,  is to "proc_local_config_select" which takes the IP address of the client and queries the database for some configuration settings. This happens as soon as the site loads.

I immediately see this as soon as I point my browser to the link:

Microsoft VBScript runtime error '800a01a8'
Object required: 'o_RS'
/BrokerWeb/include/functions.asp, line 5

functions.asp line 5 is my HandleError method where I write out the contents of the disconnected recordset. It is expecting a recordset, but it gets NULL, which means that my COM+ DLL both had an error running the SQL code, as well, could not create the error condition holding recordset.

Interestingly, if I then immediately hit refresh, I instead see this:

An Error Has Occured:
Number: 3705
Description: Operation is not allowed when the object is open.
Caller: system:InitLocalAEConfig()
SQL: proc_local_config_select ''

This type of block is is what my HandleError() method outputs on the ASP side. Each of those lines is a row in the disconnected recordset the exception handling mechanism in my COM+ object implements.

I can run that line: proc_local_config_selec '' from management studio on the DB server and get a valid result set.

I'm sure the problem here is a permissions or settings thing, as from a code perspective, everything works as expected as long as the SQL Server is on the same computer as the webserver..

I just have no idea what setting it might be.
PMH4514Author Commented:
Perhaps a clue.

In the SQL server logs, I see these entries:

11/03/2008 14:47:31,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT:]
11/03/2008 14:47:31,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

so even though, within my DLL, I pass a username and password to the connection, it seems the DLL as anonymous? is the client computer that is unable to connect.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PMH4514Author Commented:
Additionally, Here is how I connect within the DLL

Dim oConn As New ADODB.Connection
oConn.Open "Provider=SQLOLEDB;SERVER=DBSERVER;database=broker;uid=usernamehere;password=passwordhere;Trusted_Connection=yes;Network Library=DBMSSOCN")

Open in new window

In your connection string, you are specifying two types of SQL Server Authentication.  Try taking out:
Then, the connection will use the username and password you specify.  Let me know how that goes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PMH4514Author Commented:

To simplify debug I created a single ASP page as seen in the attached snippet.

Note the removal of Trusted_Connection as you had stated. With it in place, I received the same errors I was describing earlier.. With it removed,  I get this:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/Broker/test.asp, line 11

so I removed , adOpenStatic, adLockBatchOptimistic from the Open statement, and this time I have success.

It was another developer who is no longer in the picture who specified adOpenStatic and adLockBatchOptimistic, I admit I am not quite familiar with what purpose they were intended to server. What do they do?

It seems without the Trusted Connection and without the adOpenStatic and adLockBatchOptimistic, I can access the remote DB on the client webserver.

I am going to go recompile the COM+ DLL with those changes and see if the website works.

Dim oConn 
Dim oRS
set oConn = Server.CreateObject("ADODB.Connection")
set oRS = Server.CreateObject("ADODB.Recordset")
oConn.Open "Provider=SQLOLEDB;SERVER=DBSERVER;database=broker;uid=xxxx;password=xxxx;Network Library=DBMSSOCN"
oRS.Open "select * from users", oConn, adOpenStatic, adLockBatchOptimistic
Set oConn = Nothing
if (oRS is nothing) then
end if

Open in new window

PMH4514Author Commented:
I recompiled my DLL without the adOpenStatic or adLockBatchOptimistic, and remoted the TrustedConnection line from the connection string which was specified in global.asa, and now the website functions properly when the DB Server is on another computer.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.