• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

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.

Thanks
0
PMH4514
Asked:
PMH4514
  • 5
  • 2
1 Solution
 
exclamationsoftCommented:
Are you checking and receiving an error when you try and connnect, through your ASP code, to SQL Server?
IF IT IS A CONNECTION PROBLEM:
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.
IF YOU CAN CONNECT, BUT RESULTS ARE NULL
Are you handling/catching an exception?  Is an error returned?
0
 
PMH4514Author Commented:
Hello

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 '192.168.1.5'

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 '192.168.1.5' 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.
0
 
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: 192.168.1.5]
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? 192.168.1.5 is the client computer that is unable to connect.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

0
 
exclamationsoftCommented:
In your connection string, you are specifying two types of SQL Server Authentication.  Try taking out:
;Trusted_Connection=yes
Then, the connection will use the username and password you specify.  Let me know how that goes.
0
 
PMH4514Author Commented:
Interesting...

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
 
oConn.Close
Set oConn = Nothing
 
if (oRS is nothing) then
	response.write("nothing")
else
	response.write("something")
end if
 
%>

Open in new window

0
 
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.

SUCCESS!

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now