[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

SQL Login error

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
SBSWIZARD
Asked:
SBSWIZARD
  • 11
  • 10
1 Solution
 
Thomas GrassiSystems AdministratorCommented:
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
 
SBSWIZARDAuthor Commented:
Where do I run these commands from?
0
 
Thomas GrassiSystems AdministratorCommented:
From a command prompt

What OS are the workstations?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
SBSWIZARDAuthor Commented:
Windows 7.

When I try that I get osql is not recognized as an internal command
0
 
Thomas GrassiSystems AdministratorCommented:
You need to install the sql tools on that machine

Use the sql 2008 dvd
0
 
SBSWIZARDAuthor Commented:
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
 
Thomas GrassiSystems AdministratorCommented:
yes you will need to get the tools installed so you can test

Doing things remotely can be hard sometimes
0
 
SBSWIZARDAuthor Commented:
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
 
Thomas GrassiSystems AdministratorCommented:
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
 
SBSWIZARDAuthor Commented:
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
 
Thomas GrassiSystems AdministratorCommented:
Check your SQL server network settings make sure tcpip and named pipes are enabled and configured properly
0
 
SBSWIZARDAuthor Commented:
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
 
SBSWIZARDAuthor Commented:
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
 
Thomas GrassiSystems AdministratorCommented:
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
 
SBSWIZARDAuthor Commented:
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
 
Thomas GrassiSystems AdministratorCommented:
One machine can not have two different host names

when was the last time these computers were restarted?
0
 
SBSWIZARDAuthor Commented:
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
 
Thomas GrassiSystems AdministratorCommented:
post ipconfig /all from all computers involved here
0
 
SBSWIZARDAuthor Commented:
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
 
Thomas GrassiSystems AdministratorCommented:
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
 
SBSWIZARDAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now