August 24, 2011 at 11:02 am
Hello all,
i am sure this is probably been brought up on here a million times and i even google this issue but still not able to resolve this. So here is the situation...
i am able to connect to this instance fine, but my co-worker using the same username/pass(SQL Server authentication) cannot connect to it....they get the below error...
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
I even tried connection osql from cmd prompt using below...i can connect and they cannot connect...
osql -S xxx.xx.xx.xx\inst01 -U username
i have looked at the server...the instance is up and running fine(obviously thats why i can connect)...
i folllowed couple of diff blogs...and still no resolution....below is what i followed and made sure all is done
any help is appericiated...
August 24, 2011 at 11:13 am
is there a firewall between the 2 endpoints?
can the client machine resolve the server name or even ping it?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2011 at 11:26 am
yes the client can ping the host machine and we dis able the firewall on this local machine... and still get the same issue...
August 24, 2011 at 12:13 pm
can the client connect to it over port the sql server listening port? you can test this from the command prompt with "telnet <hostname> <port>". try this from your machine and his and compare results. A successful conenction will go to a black screen (you can type "quit <enter>" to exit")... an unsuccessful attempt will give an error message.
this is testing connectivity from the client to the SQL Server port. If you cant do this from the client, some firewall is blocking access over that port or the server is configured to allow connections only from certain hosts (ip filtering).
you can determine what port the server is listening on by lookuing at the error log... when it first starts up, the log will show "SQL Server is up and listening on XXXX" port.
August 24, 2011 at 3:54 pm
I typically see this in a client-server situation when one of the following is true:
1. The instance name is spelled wrong (probably not the case here)
2. UDP remote port 1434 is not set as an inbound rule on the client machine that is using Windows Firewall. This port needs to be open for the SQL Server Browser to work properly.
3. You're using aliases for your SQL Servers and the proper aliases are not set up on the client machine. If this is true in your case, you can verify what aliases are set up by running cliconfg.exe and looking under the 'Alias' tab.
August 25, 2011 at 7:01 am
hello, how would i go about following point 2(setting UDP inbound port)...that is
"2. UDP remote port 1434 is not set as an inbound rule on the client machine that is using Windows Firewall. This port needs to be open for the SQL Server Browser to work properly."
but what if the windows firewall has been turned off?? do you still need to set the UDP inbound port ?
August 25, 2011 at 7:19 am
abdul.irfan2 (8/25/2011)
hello, how would i go about following point 2(setting UDP inbound port)...that is"2. UDP remote port 1434 is not set as an inbound rule on the client machine that is using Windows Firewall. This port needs to be open for the SQL Server Browser to work properly."
Easiest way is through the windows firewall GUI, this link[/url] should help
abdul.irfan2 (8/25/2011)
but what if the windows firewall has been turned off?? do you still need to set the UDP inbound port ?
No!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2011 at 8:06 am
speculation is that another device is blocking access to the required port. by telneting to the port the instance is listening on you can determine whether or not there is a connectivity problem or some other problem.
Suggest you check sql server error log to see which port the instance is listening on. attempt to telnet to that port from the pc which is not connecting. If you can connect to that port via telnet, then all the talk of firewalls and network is no longer relevant... we need to look at why your client is not connecting to the sql service when you specify the name (i.e. server\instance).
If you cant connect via that port, then there is a networking issue. If you are certain that windows firewall is not running, then is there a phyisical device blocking connectivity over that port? is there a physical firewall?
So, test with telnet and let us know whether there is a networking problem preventing you from accessing that port, or there is a resolution problem where you are not resolving server\instance to the correct port.
August 25, 2011 at 9:35 am
client was able to connect via telnet to the named sql server instance with that port...
August 25, 2011 at 9:52 am
Is Windows Firewall disabled on both the client and the server ?(Not suggesting this, just curious as to what your current configuration is.) And you're sure you aren't using aliases? In otherwords, are you typing the full server_name\instance_name when you attempt to connect?
August 25, 2011 at 10:21 am
yes the firewall is off on client side, and server side it on...
as far as aliases, we are not using alias...
the same servername/inst_name that i used, is being used by the client too...
August 25, 2011 at 10:33 am
If:
-Client A can connect via servername\instance name
-Client B can not connect via servername\instance name
-Cleints A and B can both connect when specifying port
Then this is a SQL browser issue. Client B must be unable to connect to the server over port 1434- so the browser can tell the client "you are looking for a particular instance- find it on XX port". If the windows firewall is active on the server, it must be allowing connections from Client A to port 1434, but not from Client B to port 1434. Either you can reconfigure the firewall on the server, or you can specify the port when connecting... for example, in managment studio, enter the servername as: SERVERNAME,portnumber
obviously the better solution is to correct the firewall issue than to work around it by specifing the port.
August 26, 2011 at 10:42 am
That worked like a charm, giving the port number...
So now how do i go about fixing the browser ?
August 26, 2011 at 10:46 am
You need open a rule in Windows Firewall UDP Local port 1434 on your SQL Server - sounds like this should solve your issue.
August 26, 2011 at 11:20 am
I have to say- I find it surprising that one client it works without specifying the port number and another you must specify the port number. To me this means one client is allowed to access port 1434 while the other is not. Is this the case? the client which is able to connect without specifying the port number- is client actually running on the server, or this is another client PC just like the one with the problem?
If I have all the information correct, and one PC can access without specifying a port, while the other must specify the port... and everything about these two clients is the same (i.e., no alias is specifed in the working client) then there must be an existing firewall rule which is allowing access to port 1434 by some host and not allowing it from others.
what you want to ensure is that your SQL Server is allowing access to port 1434 from any host you wish to connect to sql server using the name... so you can either add the ip address of the new host to the list of allowed hosts, or you can do something like allow access to 1434 to ALL hosts, or all hosts on a certain subnet, etc.
http://technet.microsoft.com/en-us/library/cc947832(WS.10).aspx
this is for Server 2003/xp.... basically, you are looking to ALLOW access to UDP port 1434 from ANY or from the ip address of the clients you want to be able to access sql server...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply