Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-09-09
21
Medium Priority
?
56,199 Views
Last Modified: 2012-06-27
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
**************
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
21 Comments
 

Author Comment

by:rayluvs
ID: 19858254
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
0
 
LVL 14

Expert Comment

by:Jai S
ID: 19858934
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...
0
 
LVL 1

Expert Comment

by:brath
ID: 19859510
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....

0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 14

Expert Comment

by:Jai S
ID: 19859561
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...
0
 

Author Comment

by:rayluvs
ID: 19859856
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
0
 

Author Comment

by:rayluvs
ID: 19859877
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.

0
 

Author Comment

by:rayluvs
ID: 19859895
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
0
 
LVL 14

Expert Comment

by:Jai S
ID: 19859919
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...
0
 

Author Comment

by:rayluvs
ID: 19859947
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.

0
 
LVL 14

Expert Comment

by:Jai S
ID: 19859981
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 "/")
0
 
LVL 14

Expert Comment

by:Jai S
ID: 19859986
i tried with a different server ofcourse the "localhost\CAAST" was just an example ,,, :-)
0
 

Author Comment

by:rayluvs
ID: 19860006
ok...sound intersting...i'll try it...
0
 

Author Comment

by:rayluvs
ID: 19860051
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.

0
 
LVL 14

Expert Comment

by:Jai S
ID: 19860147
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. !!!!!!!!!
0
 

Author Comment

by:rayluvs
ID: 19860205
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

0
 
LVL 14

Expert Comment

by:Jai S
ID: 19860234
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.
0
 
LVL 14

Accepted Solution

by:
Jai S earned 1000 total points
ID: 19860244
check this link for more details
http://support.microsoft.com/kb/195566

let me know whether you are successful !!!
0
 

Author Comment

by:rayluvs
ID: 19860423
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?

0
 

Author Comment

by:rayluvs
ID: 19879509
I found the file....copied it, but still no work.....
0
 

Author Comment

by:rayluvs
ID: 19888117
Any expert out there can help with this problem....???
0
 

Expert Comment

by:dennisrturner
ID: 21507437
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.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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