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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

696 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