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

does the authentication mode on the production server permis sql logins or is it set to windows authentication only?

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

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.

hmm ok,     I assume that youve checked that the SQL login has access to the intial catalog ?

the SQL login has Public, datareader and datawriter permits to this DB.

have to you tried establishing a connection to the this database via QA using the same name & pwd?

and the database is not in DBO use only mode, etc.

Just did that, and it connected, showing me the DB and all the tables no problem.

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  

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.

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

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();
%>

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.

Any other suggestions or ideas would be greatly appreciated. Really need to have this app going by tomorrow afternoon.

have you tried to connect to sql server from the machine where you experience the error.

If you're running ASP.NET maybe this will help.

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

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.

Is there a firewall and/or router between the IIS machine and the SQL Server machine ?

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!

"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

I've done that about a dozen times.. created several new users, used a couple different existing ones.. and nothin...

ok.   its not a SID problem then :(

how did you export the db ?

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.

good call

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?

Is there a filewall involved?

oops i mean firewall

No there isn't.. all within the same intranet.

hmmm.   im pretty much at a losss then im afraid

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

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?

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.

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

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.

miron:
I agree with you suggestion, and thanks for the quick response.

Shogun and MTillet:
Thanks again for the help.

your welcome

PAQ + Points Refund