Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Connecting to SQL Server from Classic ASP

Posted on 2004-10-21
31
Medium Priority
?
548 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
Comment
Question by:dzmvhl
  • 9
  • 9
  • 8
  • +2
31 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 12369907
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
ID: 12369962
Use localhost as machine name... does that work?
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12369994
localhost or (local)
0
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.

 
LVL 33

Expert Comment

by:hongjun
ID: 12370009
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
ID: 12370055

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

Author Comment

by:dzmvhl
ID: 12370151

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

Expert Comment

by:thefritterfatboy
ID: 12370263
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
ID: 12370291
This line
conn =createobject("ADODB.connection")
change it ot
conn =Server.Createobject("ADODB.connection")

0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 300 total points
ID: 12370316
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
ID: 12370389
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
ID: 12370441
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
ID: 12370512

* 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
ID: 12370519
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
ID: 12370531
Windows Server 2003 comes with MDAC 2.8 already.
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 12370581
0
 
LVL 10

Assisted Solution

by:thefritterfatboy
thefritterfatboy earned 300 total points
ID: 12370594
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 12370667
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
ID: 12371072

* 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
hongjun earned 300 total points
ID: 12371160
or you try to create a system dsn see how?
0
 
LVL 20

Expert Comment

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

Also did you see my last post ?
0
 

Author Comment

by:dzmvhl
ID: 12371525

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

Thanks to everybody for your help.
0
 
LVL 20

Expert Comment

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

Author Comment

by:dzmvhl
ID: 12371812

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
ID: 12372360
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:
jitganguly earned 600 total points
ID: 12372390
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
ID: 12373514

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
ID: 12381559

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
ID: 12381581
That was not a solution :-)
0
 

Author Comment

by:dzmvhl
ID: 12381685

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

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

Have a nice weekend from NY
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 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