wydopn
asked on
Management studio connection failing on certain computers
I am encountering a somewhat hard to trace problem with Management studio on my local network.
I have a named instance of SQL server 2005 installed on my local development machine. I have verified that under connections for the instance i have Checked "Allow remote connections to this server"
I have also verified that under surface area configuration that local and remote connections is selected, with "Using both TCP/IP and named pipes" selected.
I can connect from one of the other computers on the network using the sa account and password, however i get the error 26 code when trying to connect from any of our other computers with management studio installed. Error is as follows:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)"
However on all the computers that fail, when you browse for servers, and select network servers the named instance shows up as a selectable option.
I'm at a loss, and any information or guidance would be greatly appreciated.
I have a named instance of SQL server 2005 installed on my local development machine. I have verified that under connections for the instance i have Checked "Allow remote connections to this server"
I have also verified that under surface area configuration that local and remote connections is selected, with "Using both TCP/IP and named pipes" selected.
I can connect from one of the other computers on the network using the sa account and password, however i get the error 26 code when trying to connect from any of our other computers with management studio installed. Error is as follows:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)"
However on all the computers that fail, when you browse for servers, and select network servers the named instance shows up as a selectable option.
I'm at a loss, and any information or guidance would be greatly appreciated.
BrandonGalderisi
Is there by chance a firewall separating the client and the server that might be doing port blocking?
ASKER
Thank you for the speedy reply.
There is no firewall operating on the client that is trying to connect, and all the computers are plugged into the same switch inside our network firewall.
I can only assume that the problem is not in the server itself as one other computer can connect to it so it can't be blocking ports (or could it?)
Another thing that may be of use, the client that can connect is on a different domain than the server, and the other clients that cannot connect as well as the server are on one domain together.
There is no firewall operating on the client that is trying to connect, and all the computers are plugged into the same switch inside our network firewall.
I can only assume that the problem is not in the server itself as one other computer can connect to it so it can't be blocking ports (or could it?)
Another thing that may be of use, the client that can connect is on a different domain than the server, and the other clients that cannot connect as well as the server are on one domain together.
Have you checked that you have SMO is installed correctly on these machines?
Have a look at the following website:-
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
You will need to download the folowing pack in order to install SMO:-
Microsoft SQL Server 2005 Management Objects Collection
Hope this helps
Have a look at the following website:-
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
You will need to download the folowing pack in order to install SMO:-
Microsoft SQL Server 2005 Management Objects Collection
Hope this helps
Also , to check the firewall, from the affected PC(s) can you telnet to the box?
I.e. Telnet <servername> 1433
It should just flick in the top left.....
I.e. Telnet <servername> 1433
It should just flick in the top left.....
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
named instance is running port 1047, from the one client i can connect on, it will connect with telnet
from all others, it will not
from all others, it will not
I would bet it's a firewall issue. Are they on the same subnet (IP Range)?
ASKER
All are running under the same subnet
local firewall software?
ASKER
windows firewall is shut off on all computers, disabled by a group policy, no other software firewall appears to be present
And you can telnet from one windows computer to your SQL Server instance running port 1047 but not from another sitting right next to it (virtually next to it in the same subnet). You aren't using IP filtering on your server are you?
Save this VBS and try running it from one machine that works then one machine that doesn't.
Save this VBS and try running it from one machine that works then one machine that doesn't.
Option Explicit
Const ServerInstance = "YourServerName\YourInstanceName"
Dim dbConnStr
dbConnStr = "Provider=sqloledb;Data Source=" & ServerInstance & ";Initial Catalog=TFS_History;Trusted_Connection=Yes"
Dim dbConn
Set dbConn = CreateObject("adodb.connection")
dbconn.connectiontimeout=5
dbconn.connectionstring = dbConnStr
dbconn.open
If dbconn.state=1 Then
wscript.echo "OPEN"
Else
wscript.echo "CAN NOT OPEN"
End If
Set dbconn = Nothing
ASKER
converting the script to use sql server authentication
it reads OPEN on the server locally, and the one that can access it remotely and gives a
"SQL Server does not exist or access denied" error on others
it reads OPEN on the server locally, and the one that can access it remotely and gives a
"SQL Server does not exist or access denied" error on others
ASKER
It appears to be a problem with the other computers resolving the name of the server.
If i ping the server name it takes ~90ms to respond, where as a direct ip address is <1ms
if i try connecting from the non-working computers with ipaddress\instancename it works perfect and connects no problem.
Tried a flushdns and didn't help. Any ideas on this one? now really is a different topic completely...
If i ping the server name it takes ~90ms to respond, where as a direct ip address is <1ms
if i try connecting from the non-working computers with ipaddress\instancename it works perfect and connects no problem.
Tried a flushdns and didn't help. Any ideas on this one? now really is a different topic completely...
Are the two machines using the same DNS server? Verify that you don't (for some strange reason) have an invalid host entry.
Try running "nbtstat-RR" to flush the netbios name cache on the "problem" machines.
Try running "nbtstat-RR" to flush the netbios name cache on the "problem" machines.
ASKER
nbtstat -RR did not work either
how long does it take if you use nslookup and type in the servername from that machine. Perhaps it may be taking too long to get a response from the DNS server. It may be that the primary DNS server is full and the requests are getting bounced to the secondary which is slower (on the problem machines).
ASKER
speedy, but returning an incorrect ip address....
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
It would appear that you are correct, this isn't a management studio problem, but still you were able to help me track down the cause.
Thank you very much
Thank you very much