Solved

Connecting to SQL Server from Classic ASP

Posted on 2004-10-21
537 Views
Last Modified: 2010-08-05
I have a problem trying to establish a connection to a SQL server database from an ASP classic,  the web server (windows 2003) and the database server (SQL server 2000 SP3) are in the same machine, the code I use to establish the connection is something like this:

     conn =createobject("ADODB.connection")
     conn.Open “Provider=SQLOLEDB.1;Password=userpassword;Persist Security Info=true;User ID=userid;Initial Catalog=databasename;Data Source=servername”

When I run the application appears the following error message
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/testsite/testpage.asp, line 14

I  found the following Microsoft KB article http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B328306 , but nothing there helps.

I think the error is related to IUSR_MACHINENAME or IIS permissions, because the following.
•     When I change the connection to another database server a similar error occurs.
•     I have tried changing the connection string to use ODBC and the error remains.
•     I wrote a sample page with the same code in a client-side script, and run it in the server console and it works ok.
•     The error occurs only in that web server, if I change the application to another web server it works fine, even if it uses the same database server.
•     The ASP .NET sites in the same server are OK.

Any idea?
0
Question by:dzmvhl
    31 Comments
     
    LVL 33

    Expert Comment

    by:hongjun
    have you started your sql server?
    are you using trusted connection?

    try this to make it trusted connection
    Enterprise Manager -> Right click your server name and then properties -> Security -> at Authentication, make sure "SQL Server and Windows" is checked



    hongjun
    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    Use localhost as machine name... does that work?
    0
     
    LVL 33

    Expert Comment

    by:hongjun
    localhost or (local)
    0
     
    LVL 33

    Expert Comment

    by:hongjun
    Conn.open "Provider=sqloledb;Data Source=(local);Initial Catalog=databasename;User Id=userid;Password=userpassword;"


    or


    Conn.open "Provider=sqloledb;Data Source=localhost;Initial Catalog=databasename;User Id=userid;Password=userpassword;"
    0
     

    Author Comment

    by:dzmvhl

    Yes hongjun, SQL server is started and it's using SQL and Windows authentication, as i told before, if I copy the application to another web server it works ok, even if I use the same database server.  And the same code in a client side script in the problem server console works ok. I cannot connect to any SQL server from ASP pages in that web server.

    Thanks.

    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    Try the localhost connection and tell us what happens.
    0
     

    Author Comment

    by:dzmvhl

    I also tried using localhost and did't work.
    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    This problem sometimes arises when both sql server and Visual Studio .net are installed on the same PC

    If you add an alias with the SQL Server Client Network Utility, vs.net sees it as a remote server and hence fails to connect.
    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    This line
    conn =createobject("ADODB.connection")
    change it ot
    conn =Server.Createobject("ADODB.connection")

    0
     
    LVL 26

    Assisted Solution

    by:Hilaire
    ASP.NET brings its own middleware to connect to the DB.
    ASP doesn't
    To connect using ASP and ADO, you need to install the latest MDAC on the server
    http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en

    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    Hilaire - if MDAC wasn't installed - would such an error be returned?

    In my understanding, the ASP would fail on this line:
    conn =createobject("ADODB.connection")
    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    Or may be he(she) is supplying wrong userid/password or could be even server

    I would checke th followings first before dig into mdac

    1.As mentioned in my previous post
    Change This line
    conn =createobject("ADODB.connection")
    change it ot
    conn =Server.Createobject("ADODB.connection")

    2.Check the correct userid, password

    3. Check server name, if required use IP address instead of server name
    0
     

    Author Comment

    by:dzmvhl

    * thefritterfatboy - Visual Studio is not installed on the server.

    * jitganguly - I have tried that and didn't work, same error message.

    * Hilaire - I didn't install MDAC in the server but, Do you think is necessary to install it even if there are no errors creating the ADO object and the script works fine in a client-side script?

    Another weird thing, if I use an invalid user or password in the connection string the error message changes to "Login failed for user 'username'."

    Thanks to everybody.

    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    conn =createobject("ADODB.connection") should function the same as conn =Server.Createobject("ADODB.connection")

    Check the correct userid, password >> "The error occurs only in that web server, if I change the application to another web server it works fine, even if it uses the same database server."


    The problem lies in how the server is referencing itself. Classic ASP has this problem in MANY areas. (ServerXMLHTTP users will have come across similar problems) You could try using the IP address as jitganguly suggests, but I doubt it will make a difference.

    Can you confirm if this server has Visual Studio .NET installed? I believe the error lies in this.
    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    Windows Server 2003 comes with MDAC 2.8 already.
    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    0
     
    LVL 10

    Assisted Solution

    by:thefritterfatboy
    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    Just for the sake of testing

    Can you use same user id, password to connect to the same db from Query Analyzer ?

    go to SQL Enterprise Manager, R Click on your server, Security tab make sure Authentication radio button has SQLSerevr and windows set and NOT windows only

    0
     

    Author Comment

    by:dzmvhl

    * thefritterfatboy - I tried changing the connection to ODBC and didn't work (http://www.mcse.ms/message747245-2.html),  also I tried using the IP address, the problem is not rectricted to the local database server, I cannot connect the application (in that server) to any SQL server machine.

    * jitganguly - Yes I am able to establish a connection with the same user from the query analizer in the server console and from my workstation.

    I did a lot of research in the web, and I found a similar case http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21023249.html, but it was closed with no solution.

    Thanks to everybody.


    0
     
    LVL 33

    Assisted Solution

    by:hongjun
    or you try to create a system dsn see how?
    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    Can I see your latest connections tring and the error message pls.

    Also did you see my last post ?
    0
     

    Author Comment

    by:dzmvhl

    Well, I tried everything you tell me and nothing work.

    Thanks to everybody for your help.
    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    Can I see your latest connections tring and the error message pls.
    0
     

    Author Comment

    by:dzmvhl

    conn =Server.createobject("ADODB.connection")

         conn.Open “Provider=SQLOLEDB.1;Password=userpassword;Persist Security Info=true;User ID=userid;Initial Catalog=databasename;Data Source=servername”


    Microsoft OLE DB Provider for SQL Server error '80004005'

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    /testsite/testpage.asp, line 14


    I have tried using the IP in the server name, also making a System DSN  (it works ok) and changing the connection string to ODBC

          conn.Open "DRIVER={SQL Server};SERVER=DSN or ServerName;Database=databasename;UID=username;PWD=userpassword"


    Same error.

    Thanks jitganguly.


    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    Can you delete this entry and try
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo\DSQUERY

    http://www.dbforums.com/t976301.html
    0
     
    LVL 20

    Accepted Solution

    by:
    See if this helps

    We had this exact problem.  The solution is to change the web site to run of
    f a domain user account instead of the default IUSR_MACHINENAME account.  Th
    e problem seems to be with SQL Server 2000 running on Windows 2003.  

    I don't know why, but even if you are using SQL authentication (user id/pass
    word) windows 2003 requires successfull authentication to allow you to acces
    s the sql server.  The website connects fine to any of our SQL 2000 servers
    running on Windows 2000 server, but will only connect to the one on Windows
    2003 server using a domain account.  This could also explain why it works wh
    en the site and SQL are on the same server (the website already has access t
    o that machine).

    Anyway, I would love to hear if this works for you.  Good luck.

    http://www.mcse.ms/message747245-2.html
    0
     

    Author Comment

    by:dzmvhl

    jitganguly  

    In this case the application isn't able to connect to any SQL server, not just the local server, also  I tried changing the IUSR_MACHINENAME to a local administrator account and didn't work.  I will try deleting the registry key as you told me.

    Thanks

    0
     

    Author Comment

    by:dzmvhl

    Well, finally and after almost two days with the problem, a server reboot was the solution, we didn't try that before because the problem was in a producction server.

    Thanks to everybody for your help.

    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    That was not a solution :-)
    0
     

    Author Comment

    by:dzmvhl

    jitganguly

    I agree with you, but it resolves the problem (for now), I will do more research to try to find what was wrong with the server.  

    Do you have a suggestion?
    0
     
    LVL 20

    Expert Comment

    by:jitganguly
    >>Do you have a suggestion?

    Reboot your machine whenever you install a new product :-)

    Have a nice weekend from NY
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    This video discusses moving either the default database or any database to a new volume.

    860 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

    15 Experts available now in Live!

    Get 1:1 Help Now