Solved

SQL Login error

Posted on 2014-04-17
21
449 Views
Last Modified: 2014-04-17
Have a workstation1 with MSSQL Server 2008 installed running a piece of software.

There is another workstation2 that connects to the database stored on the WS1. This has been working for months.

Now when WS2 tried to launch the software that connects to the DB on WS1, we get the following SQL error:

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

The firewall on WS1 has been turned off. Ports 1433 have been opened on WS2. I have checked the priority on the SQL Configuration Manager for the protocols and TCP/IP is set higher. Have even tried disabling Named Pipes as well.

Still cannot get WS2 to start the program and connect ot the DB on WS1
0
Comment
Question by:SBSWIZARD
  • 11
  • 10
21 Comments
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
try this

Please try
   osql -E
for trusted connection, or
   osql -U<login id> -P<password> -S<instance name>

you can skip -S parameter if you have installed a default instance.

In addition, use
   osql -U<login id> -P<password> -S<instance name> -i<tsql script file name>
to run scripts.  

This can test the connection
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Where do I run these commands from?
0
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
From a command prompt

What OS are the workstations?
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Windows 7.

When I try that I get osql is not recognized as an internal command
0
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
You need to install the sql tools on that machine

Use the sql 2008 dvd
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Ok. When I try from WS1 that hosts the DB I get connection.

Just from WS2 it doesnt see that command.

I am doing all of this remotely so I dont have the CD available.
0
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
yes you will need to get the tools installed so you can test

Doing things remotely can be hard sometimes
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Ok. Tools installed. Tried commands

Named Pipes Provider: could not open a connection to SQL server [53].
Login timeout expired
A Network-related or instance specific error has occurred while establsigh a connection...
0
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
try connecting from a client using the application's connection string, e.g. "sqlcmd -S np:\\myhost\pipe\mssql$myinstance\sql\query -U myuser".

Try connecting on the database server using the connection string as well as a connection string without the computername, e.g. "sqlcmd -S np:\\.\pipe\mssql$myinstance\sql\query -U myuser".

If you can connect on the sql server host with . (dot) as the computername, it implies that the computername is not resolving from the remote host. Possibly the IP address, hostname, or FQDN has changed in some way.


Sounds like named pipes is not enabled on the SQL Server side
0
 

Author Comment

by:SBSWIZARD
Comment Utility
I tried the command on the client machine and received the same error as above

I tried on the server machine and received the following:

error locating server/instance specified
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
Check your SQL server network settings make sure tcpip and named pipes are enabled and configured properly
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Ok. I rebooted the server machine just to make sure all services were started.

Tried the command for the server machine again and it connected.

Tried the command on the client machine and it returned the same error: Could not open a connection to SQL Server.

Checked TCP/IP and Named Pipes and they are both configured and enabled.
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Just ran a ping -a from the client machine for the IP address that the server machine is supposed to be set as and it returned a different hostname.

Checked our servers DHCP and that IP is supposed to be reserved for the server machine.

Should I try changing the IP on the server machine? I cannot locate the machine that is returning the same IP on the network.
0
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
If the ip address is responding with  different host name then check DNS
Make sure that the ip address on the server machine is static

Some other machine has that ip address I would check the MAC ID's in DCHP status and use
Ipconfig /all on the server machine check what it is.

What machine does your DHCP and DNS?
0
 

Author Comment

by:SBSWIZARD
Comment Utility
A completely separate server manages DNS/DHCP.

Now this is stange. Im a little confused now. Remoted into the DHCP/DNS server and checked SBS console to see if I could remote in. I was able to establish a remote connection and said there was another user logged in. I issued a command to prompt for logoff and a window popped up on the SQL server machine.

Not only that, but SBS console thinks its a XP machine, not a windwos 7 machine.

How does one machine report two different hostnames?
0
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
One machine can not have two different host names

when was the last time these computers were restarted?
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Today.

Not sure why SBS Console is reporting it as WS06 when it is named WS-01. Dont even see a machine named WS-01 in SBS or AD
0
 
LVL 23

Expert Comment

by:Thomas Grassi
Comment Utility
post ipconfig /all from all computers involved here
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Text files for both cleint and server machine.

Establishing a remote connection from SBS console to SGBI06 just connects to the Server Mahcine which has a hostname of SGBIWS-01
servermachine.txt
cleintmachine.txt
0
 
LVL 23

Accepted Solution

by:
Thomas Grassi earned 500 total points
Comment Utility
First disable all IPV6  in your network setup.

Next why does the clientmachine have two default gateways
192.168.2.1
10.10.0.1

What is 192.168.2.1 ?

Does this machine have two NICS?

having two nics in a computer is not good practice having two default gateways wil casue you many network issues.

Remove 192.168.2.1 from the client because it does not know how to route back to the 10.10.0.x network. where the server is on.
0
 

Author Comment

by:SBSWIZARD
Comment Utility
Not sure how 192.168.x.x got in there. Disabeld IPv6.

Client machine has correct configuration. Only one NIC

Server machine is set to static with correct configuration.

Both machines have been restarted.

Machine has connected.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This Micro Tutorial will teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now