Link to home
Start Free TrialLog in
Avatar of jana
janaFlag for United States of America

asked on

MICROSOFT SQL SERVER LOGIN - SQLSTATE '01000' & '08001' - SQL SERVER ERROR 14 - ODBC

I have a Windows SBS 2003 Server, Service Pack 2, 2gb ram and 200gb disk.

I have installed MS SQL Server 2000 with its latest Service Pack 4 and left in tact its standard "MSSQL Desktop Engine (SBSMonitoriing)" and "MSSSQL Desktop Engine (Sharepoint)" installation that came with SBS 2003 Server install.

I have search the .experts-exchange and found a question ("SQL Server Login error when attempting ODBC connection") my exact error message:

Connection failed:
SQLState: '01000'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Invalid Instance()).
Connection failed:
SQLState: '08001'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Invalid connection.

I have defined an Alias as it recommended but, still no conection.

I have created the ODBC from within the Server and works fine.  I can connect to my SQL.

I've tried the following at the workstation:

1. Ping ServerName, tested ok
2. Ping server IP address, tested ok
3. In ODBC, in the "Create a New Data Source..", I place the server IP address, and still no connect
4. Tested command-line "Telnet ServerName 1433", and I get a blank screen,
     where I type anything and is also blank (dont know what that means, but thats the result)
5. Verified that the server name and the machine name are the same; tested ok
6. Disable all firewall in the server and the workstation, still no connect
7. Disable all antivirus & antispyware, still no connect
8. Uninstalled SQL 2000, rebooted server, deleted all folders related to SQL2000 and reinstall

**************
Moved to MS SQL Server / Database Zones
250 points added
jason1178 Community Advisor
**************
Avatar of jana
jana
Flag of United States of America image

ASKER

After reinstall, I also tried changing to "Name-Pipes" instead of "TCP/IP" in the "SQL Server Client Network Utility", "Alias" Tab.  When doing this and trying to connect, still no connection; the following error occurred:

Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or access denied.


Please advice
did you try connecting from the enterprise manager with the proper server credentials ? if you are not able to connect from EM then possibly your server name / instance name / alias name you are using is wrong...
Avatar of brath
brath

1. Open Enterprise Manager, right click on the sql server group.
2. Select "New SQL Server Registration"
3. A new screen will show up as where u can select the server.. either select the server u want to register or give the ip address of the system where SQL server is residing on.
4. It wil ask for authentication mode. Select which ever is the authentication mode... Normally it will be Sql Server Authentication
5. If SQL Authetication, provide login name n password..
6. Added it to the SQL group(either new or any existing one) .. Its not going to matter...
7. Finish

8. If it is successful, then try connecting using query analyser....
9. If it fails means theres is some problem with the authentication only.. Can be either u have provided with wrong server name(IP address) or username or password....

just a noet: It is not necessary to register the server to use it in QUERY ANALYSER...if you are to use ENTERPRISE MANAGER you need to do the mentioned steps...

if you wonder what QUERY ANALYSER  / ENTERPRISE MANAGER - they are two separate(though integrated) tools provided by SQL Server for managing you SQL Server, QA is mostly CUI (where in you have manually write down your queries / DML/DCL statements to manipulate what you want)......EM - is GUI based...
Avatar of jana

ASKER

hi jaiganeshsrinivasan,

first my problem is connecting to the SQL instance from a workstation thru ODBC connection.  Also, I tried setting up the ODBC connection with the actual SQL instanca name and also with the server IP; non work.

I also tried doing the ODBC connection in it three (3) ways:

1. Making an ODBC connection within the same server; it worked perfectly (unfortunately, I need to connect from a workstation).
2. Adding a new computer to the domain with admin rights (thru the Network Identification Wizard); connected to
    server successful, access all recourses, but I cant connect thru ODBC.
3. Finally, tried to connect without adding the computer to the domain.

The ODBC connections works when created in the same server, but from a workstation is unsuccessfull
Avatar of jana

ASKER

Hi brath.

The steps you are recommended worked perfect in the server!  unfortunately, my problem is not running EM or QA.  I can't connect to the server from a workstation thru ODBC.

Avatar of jana

ASKER

Sorry Experts...I just re-read my initial question and it seems that it can be interpreted as that my problem is when connecting to the SQL instance within the Server.  Far from it, creating and connecting to the SQL instance using an ODBC connection from within the same Server works perfectly.

My problem is connecting to the server from a workstation using an ODBC connections.  Hope this helps when answering.

Thanx
i hope you are using system DSN to create a ODBC data source...can you try using User DSN instead !!!

i assume taht you have a Created a DSN thru Ctrl panl -> Administrative tools -> odbc data sources.
there is an optuion to TEST connection there...is it working for you ? or is it where you are getting teh error...
Avatar of jana

ASKER

jaiganeshsrinivasan...I tried to include as many detail as possible in my question (i forgot this one)...yes, i created thru system DSN and also tried it thru user DSN (just in case)...unfortunately, I didn't get to the TEST since it doesn't pass "How should SQL Server verify the authenticity of the login ID?" section with the error indicated in my question...also I have done all this with admin rights and full credentials.

i just checked this out
try giving your server name like this [servername]\[instance name] (eg localhost\CAAST)
i got the error you have mentioned if i failed to specify the instance name in the first screen where it asks you to enter the server name (MIND THE SLASH it is "\" and not "/")
i tried with a different server ofcourse the "localhost\CAAST" was just an example ,,, :-)
Avatar of jana

ASKER

ok...sound intersting...i'll try it...
Avatar of jana

ASKER

I tried it....no success...as matter of fact, the server-machine name is the same as the instance name; thus SERVER\SERVER was used.

Anyways, I received this error:

---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

tht was weird !!!...can you recheck the instance name again, i persume it should not be the same...may be other experts can comment on this...
or can you just try with ONLY the servername. !!!!!!!!!
Avatar of jana

ASKER

Not only I tried both, I also tried with the IP address:

SERVER, SERVER\SERVER and 192.168.0.20 in the OBDC connection setup

The machine name is SERVER and the SQL instance name is SERVER

i wont harass you after this...pardon me but i want the problem to be fixed...not just for points but for knowledge sharing :-)
By default, SQL Server will listen for incoming connections made by Named Pipes clients. Named Pipes is the default IPC mechanism for clients to connect to a SQL Server version 4.2, 6.0, and 6.5 server. The Named Pipes DLL is Dbnmpntw.dll, and it should be located in the Windows\System or Winnt\System32 directory. The TCP/IP sockets netlib DLL is Dbmssocn.dll, and also should be in the Windows\System or Winnt\System32 directory.The most common resolution to this problem is to configure the client computer to connect using Named Pipes.
ASKER CERTIFIED SOLUTION
Avatar of Jai S
Jai S
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Yes...this was my first test and it did work.  However, all my stations were using TCP/IP prior installing SBS2003 Server.  When I had the W2000 server running, all ok in all workstations.  

I checked the Windows\System or Winnt\System32 directory and didn find "Dbmssocn.dll"...

If this is the problem, where can I find this file?

Also, how come in my previous server the workstations connected?

Avatar of jana

ASKER

I found the file....copied it, but still no work.....
Avatar of jana

ASKER

Any expert out there can help with this problem....???
If anyone has this problem, try using the Data Sources (ODBC) admin snapin.  Under Client Configuration, select TCPIP and uncheck "Dynamically Determine port" This has worked for me on several occasions on different networks.