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
**************
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

rayluvsAuthor Commented:
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
Jai STech ArchCommented:
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
brathCommented:
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
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!

Jai STech ArchCommented:
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
rayluvsAuthor Commented:
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
rayluvsAuthor Commented:
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
rayluvsAuthor Commented:
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
Jai STech ArchCommented:
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
rayluvsAuthor Commented:
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
Jai STech ArchCommented:
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
Jai STech ArchCommented:
i tried with a different server ofcourse the "localhost\CAAST" was just an example ,,, :-)
0
rayluvsAuthor Commented:
ok...sound intersting...i'll try it...
0
rayluvsAuthor Commented:
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
Jai STech ArchCommented:
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
rayluvsAuthor Commented:
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
Jai STech ArchCommented:
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
Jai STech ArchCommented:
check this link for more details
http://support.microsoft.com/kb/195566

let me know whether you are successful !!!
0

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
rayluvsAuthor Commented:
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
rayluvsAuthor Commented:
I found the file....copied it, but still no work.....
0
rayluvsAuthor Commented:
Any expert out there can help with this problem....???
0
dennisrturnerCommented:
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
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.