Solved

ASP connection to SQL 2008

Posted on 2010-09-16
7
838 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 3

Expert Comment

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

Author Comment

by:BHR
Comment Utility
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
Comment Utility
maybe you need help from this website as reference.

http://www.connectionstrings.com/

0
 

Accepted Solution

by:
BHR earned 0 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Prologue It is often required to host multiple websites on a single instance of IIS, mostly in development environments instead of on production servers. I am sure it is not much a preferred solution on production servers but this is at least a pos…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

772 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

10 Experts available now in Live!

Get 1:1 Help Now