Solved

ASP connection to SQL 2008

Posted on 2010-09-16
7
842 Views
Last Modified: 2012-05-10
We recently completed an upgrade to our database from MS SQL2005 to MS SQL 2008.  The upgrade is finished & our custom database application connects to the new db successfully.  I also have an ASP (classic) web page which connected to the 2005 database to pull down some info from one of the tables as reports.  It worked fine under the SQL2005 server, but stopped working after the upgrade.  I get the error: Microsoft OLE DB Provider for SQL Server error '80004005'   [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I've tweaked with the the connection string dozens of different ways with no success.  I was able to create a .udl file with a successful connection, but that connection string didn't work either.  I've tried both Windows Integrated Authentication & SQL authentication (the SQL server is set up to use both) with no results.  Users & Roles are set up in the new 2008 database exactly how it was on the 2005 database (like I said, our db application works fine).  

The odd thing is that when I go into IIS Manager on our web server (it is an isolated intranet), I can to into directory security & if I change the authentication method to "Enable anonymous access" the page starts working.  I don't want to leave this enabled because of other reasons.  The thing is, I never had to have this enabled before the upgrade, everything worked fine then.  I just don't know why allowing anonymous access to the website would start the page working again.  The user account used by anonymous access doesn't even have rights in SQL.
0
Comment
Question by:BHR
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 33696083
One thing that could help is to go to the web server and make sure that you have installed SQL Native Client and client connectivity which will update the connectivity for this box.

The other thing is to be aware that if there is a named instance that you need to have the SQL Browser service running for the application to find the SQL Server without using a port number.

Can you ping the SQL Server from your IIS box?  Is the SQL Server a default instance or a named instance?  Did you go into the SQL Server and open the Configuration Manager and look at the Protocols for the SQL 2008 instance and make sure that you have Named Pipes and TCPIP enabled?
0
 

Author Comment

by:BHR
ID: 33696386
In the SQL Server Configuration Manager, both TCP/IP & Named Pipes are enabled.

I can ping the SQL server from the IIS box.  Actually I can even connect to it using a UDL on that box & have even loaded our database app on that box just to try & it also connected without problem.

The SQL server is a named instance.  The SQL Server Browser service was disabled.  I set it to automatic, then started it but that did not help.

I had previously been using the Microsoft OLE DB SQL Provider for a connection, but I downloaded & installed the SQL 2008 native client onto the IIS server (running Server 2003, IIS6), then tried using the SQL 2008 server native client Provider in the connection string but that also failed.

Under this method, when I try TCP connection I get this error:   Microsoft SQL Server Native Client 10.0 error '80004005'   TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.    When I try named pipes I get this error:  Microsoft SQL Server Native Client 10.0 error '80004005'  Named Pipes Provider: Could not open a connection to SQL Server [5].
0
 
LVL 3

Expert Comment

by:xiong8086
ID: 33697897
You may try the following:

Provider=sqloledb;Data Source=servername;Initial Catalog=database;User ID=uid;Password=xxxi;Network Library=DBMSSOCN

add the network library part to your connection string.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 3

Expert Comment

by:xiong8086
ID: 33697905
you may also need to make sure that the uid has proper rights to the database
0
 

Author Comment

by:BHR
ID: 33700987
Have already tried the suggested connection string along with many other variations without success.

The uid has the correct rights to the db.  I am positive about that because our custom db application connects without problem, it is only this ASP page that is having problems.
0
 
LVL 3

Expert Comment

by:xiong8086
ID: 33759325
maybe you need help from this website as reference.

http://www.connectionstrings.com/

0
 

Accepted Solution

by:
BHR earned 0 total points
ID: 33823159
I ended up chaning the permissions of that specific folder on the website to only use anonymous authenication only for that folder only & that works.  The original connection string that I had used works this way.  The real problem has got to be a permissions issue, but I couldn't figure it out.  It isn't my ideal solution because it really doesn't explain what is going on, but at least it gets things working.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

839 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