[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


ASP connection to SQL 2008

Posted on 2010-09-16
Medium Priority
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 25

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.
Industry Leaders: 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

Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

656 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