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

Posted on 2008-11-03
Last Modified: 2008-11-06
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.

Question by:PMH4514
    LVL 2

    Expert Comment

    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?

    Author Comment


    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.

    Author Comment

    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.

    Author Comment

    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

    LVL 2

    Accepted Solution

    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.

    Author Comment


    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


    Author Comment

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now