Solved

SQL Login error

Posted on 2014-04-17
21
457 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
ID: 40007701
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
ID: 40007715
Where do I run these commands from?
0
 
LVL 23

Expert Comment

by:Thomas Grassi
ID: 40007735
From a command prompt

What OS are the workstations?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:SBSWIZARD
ID: 40008111
Windows 7.

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

Expert Comment

by:Thomas Grassi
ID: 40008114
You need to install the sql tools on that machine

Use the sql 2008 dvd
0
 

Author Comment

by:SBSWIZARD
ID: 40008117
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
ID: 40008118
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
ID: 40008123
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
ID: 40008130
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
ID: 40008145
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
 
LVL 23

Expert Comment

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

Author Comment

by:SBSWIZARD
ID: 40008158
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
ID: 40008159
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
ID: 40008162
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
ID: 40008179
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
ID: 40008187
One machine can not have two different host names

when was the last time these computers were restarted?
0
 

Author Comment

by:SBSWIZARD
ID: 40008190
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
ID: 40008193
post ipconfig /all from all computers involved here
0
 

Author Comment

by:SBSWIZARD
ID: 40008201
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
ID: 40008204
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
ID: 40008221
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing Administrative privilege 4 95
What is CMR CACHE? 7 34
ms sql + get number in list out of total 7 29
hp envy 4500 driver installation problem 5 25
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…
While working, an annoying popup showing below will come and we cannot cancel or close it form the screen. The error message will come again and again.
This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.

832 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