Connecting to SQL Server from Classic ASP

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?
dzmvhlAsked:
Who is Participating?
 
jitgangulyConnect With a Mentor Commented:
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
 
hongjunCommented:
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
 
thefritterfatboyCommented:
Use localhost as machine name... does that work?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
hongjunCommented:
localhost or (local)
0
 
hongjunCommented:
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
 
dzmvhlAuthor Commented:

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
 
thefritterfatboyCommented:
Try the localhost connection and tell us what happens.
0
 
dzmvhlAuthor Commented:

I also tried using localhost and did't work.
0
 
thefritterfatboyCommented:
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
 
jitgangulyCommented:
This line
conn =createobject("ADODB.connection")
change it ot
conn =Server.Createobject("ADODB.connection")

0
 
HilaireConnect With a Mentor Commented:
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
 
thefritterfatboyCommented:
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
 
jitgangulyCommented:
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
 
dzmvhlAuthor Commented:

* 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
 
thefritterfatboyCommented:
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
 
thefritterfatboyCommented:
Windows Server 2003 comes with MDAC 2.8 already.
0
 
thefritterfatboyCommented:
0
 
jitgangulyCommented:
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
 
dzmvhlAuthor Commented:

* 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
 
hongjunConnect With a Mentor Commented:
or you try to create a system dsn see how?
0
 
jitgangulyCommented:
Can I see your latest connections tring and the error message pls.

Also did you see my last post ?
0
 
dzmvhlAuthor Commented:

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

Thanks to everybody for your help.
0
 
jitgangulyCommented:
Can I see your latest connections tring and the error message pls.
0
 
dzmvhlAuthor Commented:

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
 
jitgangulyCommented:
Can you delete this entry and try
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo\DSQUERY

http://www.dbforums.com/t976301.html
0
 
dzmvhlAuthor Commented:

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
 
dzmvhlAuthor Commented:

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
 
jitgangulyCommented:
That was not a solution :-)
0
 
dzmvhlAuthor Commented:

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
 
jitgangulyCommented:
>>Do you have a suggestion?

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

Have a nice weekend from NY
0
All Courses

From novice to tech pro — start learning today.