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

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
MustangzAuthor Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hmm ok,     I assume that youve checked that the SQL login has access to the intial catalog ?
MustangzAuthor Commented:
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.
MustangzAuthor Commented:
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  
MustangzAuthor Commented:
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.

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

MustangzAuthor Commented:
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;
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.
MustangzAuthor Commented:
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.

MustangzAuthor Commented:
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 ?
MustangzAuthor Commented:
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
MustangzAuthor Commented:
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
MustangzAuthor Commented:
I just used the export wizard to copy the DB to the production server.

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
MustangzAuthor Commented:
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.

MustangzAuthor Commented:
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?

            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.



      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.

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.
MustangzAuthor Commented:
I agree with you suggestion, and thanks for the quick response.

Shogun and MTillet:
Thanks again for the help.
your welcome
PAQ + Points Refund
PAQed, with points refunded (250)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.