ASP connection to SQL 2008

Posted on 2010-09-16
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.
Question by:BHR
  • 3
  • 3
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?

Author Comment

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].

Expert Comment

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.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Expert Comment

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

Author Comment

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.

Expert Comment

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


Accepted Solution

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.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

12 Experts available now in Live!

Get 1:1 Help Now