Solved

SQL2000 server - error 80004005 Server does not exist or access denied (I've verified everything 20 times now)

Posted on 2004-09-23
41
322 Views
Last Modified: 2008-01-09
I have an asp/javascript application (written by someone else) that I've just thrown up on an IIS server. It uses a SQL2000 DB backend.

I've loaded the DB onto my local dev SQL2000 server and can connect to this DB just fine from the application. Now when I export this DB to the production server and attempt to connect the application to IT, I get the wonderful error above. I have checked over everything 20 times at LEAST. The user/login exists, and it has the proper permisisons/rights (ie. the same as I have on my dev server which is working).

Connection string for local machine which is working:
var MM_eDFMEAConnect_STRING = "Provider=SQLOLEDB;Data Source=ANNMIW40QZG31;User ID=eDFMEAuser;Password=password;Initial Catalog=PSCO_eDFMEA"

Connection string for remote production server (not working):
var MM_eDFMEAConnect_STRING = "Provider=SQLOLEDB;Data Source=marmissql01\auto_sql;User ID=eDFMEAuser;Password=password;Initial Catalog=PSCO_eDFMEA"

Can someone see something I am missing? or suggest something to try?

TIA
0
Comment
Question by:Mustangz
  • 17
  • 13
  • 5
  • +3
41 Comments
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
does the authentication mode on the production server permis sql logins or is it set to windows authentication only?
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
also i notice you are using a named instance.  on production.    are you sure there are no IP port conflicts eg default instance and named instance but listening on 1433
0
 

Author Comment

by:Mustangz
Comment Utility
I have many other DB's running on that same production server that I use SQL authentication so that isn't an issue.

I'm not sure how to go about checking for port conflicts. But, again I have many other DB's on this server, running off of the same named instance and am not having any other issues.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
hmm ok,     I assume that youve checked that the SQL login has access to the intial catalog ?
0
 

Author Comment

by:Mustangz
Comment Utility
the SQL login has Public, datareader and datawriter permits to this DB.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
have to you tried establishing a connection to the this database via QA using the same name & pwd?
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
and the database is not in DBO use only mode, etc.
0
 

Author Comment

by:Mustangz
Comment Utility
Just did that, and it connected, showing me the DB and all the tables no problem.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
this is probably a silly question so forgive me for asking it (but i want to eliminate all the opbious thinks first).     I dont suppose you have in your vb code forgotten to pass the connection string in  to the connection.open  
0
 

Author Comment

by:Mustangz
Comment Utility
The application works when connecting to my local dev sql server. All I'm changing is the Data Source to the production server name, none of the code is being touched.
0
 
LVL 2

Expert Comment

by:MTillett
Comment Utility
Mustangz,

Here's a snippet from BOL.  To find the full article enter 'Controlling Net-Libraries' in the search tab.

========== BOL ==================
Controlling Net-Libraries and Communications Addresses

When the SQL Server 2000 client Net-Libraries connect to an instance of SQL Server 2000, only the network name of the computer running the instance and the instance name are required. When an application requests a connection to a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the computer network name specified in the connection. All computers running an instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll connects to this port, the server returns a packet listing all the instances running on the server. For each instance, the packet reports the server Net-Libraries and network addresses the instance is listening on. After the Dbnetlib.dll on the application computer receives this packet, it chooses a Net-Library that is enabled on both the application computer and on the instance of SQL Server, and makes a connection to the address listed for that Net-Library in the packet. The connection attempt fails only if:

The requested instance of SQL Server 2000 is not running.


None of the Net-Libraries that the instance of SQL Server 2000 is listening on is active on the application computer.

========== end BOL ===================

As you have previously stated, other apps. are able to connect, that leaves no. 2  Have you check that there is a common net-lib between the client and the server?  I didn't read much further but you might find some other clue there.

MC Mikey T

0
 

Author Comment

by:Mustangz
Comment Utility
I'm not sure how to check on the net-lib's to verify between client and server. And by this you mean between IIS (client?) and the SQL server (server) correct?

Just to keep this in mind. I have many other applications running off of this same IIS server using the same SQL server, so is there something that would be in the application (again which I didn't write) that specifies another 'net-lib'? if so, what will it look like in the code?

Also, this app works when connecting to my local dev SQL server, which is just a plain/basic install, I've done nothing special and added/enabled nothing additional.

This is the exact error on the page:

Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/apps/eDFMEA/eDFMEAopener.asp, line 52


This is the CODE from the page showing LINE 52:

<%
var SQL="SELECT * From dbo.tblOwners ORDER BY RealName";

var WhoRecordSet = Server.CreateObject("ADODB.Recordset");
WhoRecordSet.ActiveConnection = MM_eDFMEAConnect_STRING;   <------- LINE 52
WhoRecordSet.Source = SQL;
WhoRecordSet.CursorType = 0;
WhoRecordSet.CursorLocation = 2;
WhoRecordSet.LockType = 1;
WhoRecordSet.Open();
%>
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
i dont think it's a library issue ( this was pretty much my initial thought) until you  said you have other apps using same instance running on same machine.   pretty much this eliminates protocol stuff.
0
 

Author Comment

by:Mustangz
Comment Utility
Any other suggestions or ideas would be greatly appreciated. Really need to have this app going by tomorrow afternoon.
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
have you tried to connect to sql server from the machine where you experience the error.
0
 
LVL 2

Expert Comment

by:MTillett
Comment Utility
If you're running ASP.NET maybe this will help.

http://support.microsoft.com/default.aspx?scid=kb;hu;315159
0
 

Author Comment

by:Mustangz
Comment Utility
miron: I've connected to the DB with QA from a machine that experiences the error in the web browser, but not from the IIS server.

MTillett: this app is asp 3.0/javascript, but I'll take a look at the article later this morning and see what I can find, thanks.
0
 
LVL 4

Expert Comment

by:philippe_leybaert
Comment Utility
Is there a firewall and/or router between the IIS machine and the SQL Server machine ?
0
 

Author Comment

by:Mustangz
Comment Utility
No.. and once again, there are many other applications running on the same IIS server that are using the SAME SQL server and named instance, so there is nothing between the IIS server and SQL server having any problems. It seems to me that it has to be related to the DB itself, but I don't know how.

I have the DB locallly on my SQL dev server and it connects to and works off of that just fine. I then exported this working DB to the production SQL server and verified users/logins/permissions and it just doesn't work, giving me the original error mentioned in my oringinal question.

Come on SQL experts! I'm a bit of a noob and must be missing something!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
"I then exported this working DB to the production SQL server and verified users/logins/permissions and it just doesn't work"

ok,  might not solve it but just to entertain a theory can you delete the login and recreate it and then try again
0
 

Author Comment

by:Mustangz
Comment Utility
I've done that about a dozen times.. created several new users, used a couple different existing ones.. and nothin...
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
ok.   its not a SID problem then :(
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
how did you export the db ?
0
 
LVL 2

Expert Comment

by:MTillett
Comment Utility
Given that you have connected to the DB w/ QA then that rules out security on the SQL Server login front.  To narrow it down as to whether it's 'access denied' or 'does not exist', turn on failed login auditing on the security tab of the propeties page for the SQL Server, you have to bounce SQL Server though.  You should see in the event log,  Window's and/or SQL Server's, the failed login details, if not then the login request is not reaching the SQL Server.  If you do see a failure, voila.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
good call
0
 

Author Comment

by:Mustangz
Comment Utility
ShogunWade:
I just used the export wizard to copy the DB to the production server.

MTillett:
Unfortunately I can't do that to the production server, but what could possibly cause it to fail? considering the username/password combo works with QA, and all the other apps running off the same IIS server are connecting and using this SQL server and named instance just fine?
Is there anything that could possibly be in the asp code that for some reason doesn't like a named instance DB?
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
Is there a filewall involved?
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
oops i mean firewall
0
 

Author Comment

by:Mustangz
Comment Utility
No there isn't.. all within the same intranet.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
hmmm.   im pretty much at a losss then im afraid
0
 
LVL 2

Expert Comment

by:MTillett
Comment Utility
Mustangz, hope you had a good weekend, didn't spend it thinking about this.

Could this possibly be an ADO timeout problem.  I think this error can be reported under this condition.  The ConnectionTimeout defaults to 15 secs. if yours is a busy environment, maybe that's it.  From what I've read of ADO (and I don't know a lot about this) the above property, ConnectionTimeout, can be adjusted on a Connection object but your ASP is not using an explicit one so you'd have to convert and add something like:

Dim sqlConn as ADODB.Connection

Set sqlConn = New ADODB.Connection

sqlConn.ConnectionTimeout = 60 (you can use 0 for infinite).

sqlConn.Open MM_eDFMEAConnect_STRING

I may have the syntax completely wrong, as I say, I'm no ADO newbie.

Mike
0
 

Author Comment

by:Mustangz
Comment Utility
WOW... I figured it out, and I can't believe it. I don't do JScript (which this app was written with), but decided to search on JScript connection strings and happened to notice one of them I stumbled upon had 2 backslashes \\ between the server name and the named instance for the data source. VOILA!

I thank both of you for your troubles and trying to help, but am not sure how to handle points and accepted answers... I think you both deserve some points for the worthy attempts, but the answer wasn't suggested. What should I do?
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
Mustangz,

            thanks for sharing solution, please indicate if you agree with the suggested question closure.

Site admin,
           please keep the question, return the points to the user.

Miron.
0
 
LVL 2

Expert Comment

by:MTillett
Comment Utility
Mustangz:

Phew,

      that's a relief, glad to hear it, sanity intact.  Solving problems is usually about thinking simple.  Far too often I come across people who start blaming a bug in the O/S or the fact that the Earth's magnetic field has shifted.  Sounds like you've got a cool head though and it turned out to be something simple.  Narrowing the problem down helps, which was basically the angle I was taking.  

As I started this question with no points (being my first question) I'm happy to leave it with no points, just glad it's sorted.

Mike
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
yes, thanks for that info,   I guess that Jscript must maybe use \ to indicate a control character thus \\ = \.   I learn something new every day.

I agree that you should keep your points, as self fix.   The reward for us is the knowledge of what to to suggest next time, and knowing that your problem is sorted.
0
 

Author Comment

by:Mustangz
Comment Utility
miron:
I agree with you suggestion, and thanks for the quick response.

Shogun and MTillet:
Thanks again for the help.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
your welcome
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
PAQ + Points Refund
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
Comment Utility
PAQed, with points refunded (250)

CetusMOD
Community Support Moderator
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

11 Experts available now in Live!

Get 1:1 Help Now