July 13, 2010 at 11:15 am
Problem:
How can I access SQL server on WAN????????????????????????????????
-----------------------------------------------------------
Error:
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
------------------------------------------------------------------------
Information:
Windows Sever 2008 (64bit OS) Service Pack 2
Microsoft SQL 2008 Server Management Studio (10.0)(64bit)
Microsoft .Net Frame work 3.5 SP1
------------------------------------------------------------------------------
Additional Information:
IIS 7 installed
In TCP/IP properties TCP Dynamic ports : 49157
All services are running except SQL Server Agent(instance name), and their start mode is automatic.
There is not any Alies configure that have problem .
------------------------------------------------------------------
What I already done:
1) SQL SERVER is up and the instance connect is running.
2) All required ports on Firewall is open/ also try to disable Firewall but no success.
3) TCP/IP protocol is ENABLE. SQL SERVER port is by Default 1433.
4) Also try this:
the probelm is the Instance name from the client is not resolved, to do that follow the below shown steps.
1. navigate to C:\Windows\System32\drivers\etc
2. Open hosts file in notepad
3. add the entry at the end of line, as i did for example
213.165.131.65 KSSQLSRVR
213.165.131.65 KSSQLSRVR\SPDEV01
those were my instances i am tried to connect remotely
5) In Server Name I tried IP address of Server (also with port number like 213.165.131.65,1433 )and also server name and instance name.
I m pulling out my hairs becoz of ANNOYING error. KINDLY HELP ME OUT OF THIS.
MNasir.
July 13, 2010 at 11:20 am
Did you test with telnet?
http://weblogs.sqlteam.com/tarad/archive/2008/05/23/60609.aspx
Your error indicates it's using named pipes and not TCP/IP. When you used IPAddress,PortNumber did you get a different error?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 13, 2010 at 12:34 pm
Thank you so much for ur response.
Yes i got below different error msg when i try with Port no
""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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)""
Could you please let me know how can you say its using Name pipes not TCP/IP???
Looking forward for your help
:sick:
July 13, 2010 at 1:41 pm
It is likely that the port is being blocked or that you aren't actually using 1433.
You need to test with telnet. On the client machine, open a cmd window and type "telnet serverName portNumber" without the double quotes and replacing serverName and portNumber with your actual data. If you get an error, then you've got some kind of communication problem (such as wrong port or firewall blocking you). If you get a blank screen, which I think is unlikely given your error, then it means it should work.
Let us know how the telnet test goes.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 13, 2010 at 4:17 pm
Once again thanks for your quick response.
I did before:
I telnet 1433 and 1434 both ports on client but connection failed. i tried on 8443 (Plesk port) on same server it working fine. Also i try this to open ports(http://support.microsoft.com/kb/968872/) for SQL server 2008 in windows server 2008.
I also disable the firewall on server but it did not work. waiting for you response.
July 13, 2010 at 6:20 pm
We need to verify what port SQL Server is using. You can do this by viewing the SQL Server Error Log as it gets mentioned at startup, or we can use SQL Server Configuration Manager.
Open up SQL Server Configuration Manager, expand SQL Server Network Configuration, locate your instance (if you have multiples), click on it, and then double-click on TCP/IP. Go to the IP Addresses tab and then scroll to the bottom. In the IPAll section, you will find the port. In my environments, we use a static port so that we can hard code it in our firewall holes as well as in our config files. If we were to use a dynamic port and if the port ever changed on us, then we'd be in for a nightmare of a problem!
For the SQL Server Error Log route, open up the newest one in SSMS and native to the bottom. You will see a bunch of messages related to SQL Server starting up. Locate one similar to this: Server is listening on 127.0.0.1
I have a feeling that you aren't using port 1433 and will instead see a different 4-digit port.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 13, 2010 at 8:35 pm
I am having a very similar problem.
I am able to connect from any machine in the same lan, but not able to connect from another network (friend's house)
Any suggestions will be greatly appreciated
July 13, 2010 at 8:49 pm
VPilli, please see the link that I posted. It covers how to troubleshoot the issue including the stuff that I've posted in this topic.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 15, 2010 at 12:48 pm
I checked it, its 1433 everywhere in TCP/IP properties.
It is default Instance.
TRIED:
servername,4711
RESULT:
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: TCP Provider, error: 0 - No such host is
known.) (Microsoft SQL Server, Error: 11001)
--------------------------------------------------------------
TRIED:
ServerName\SQLEXPRESS
RESULT:
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)
-----------------------------------------------------------------------
TRIED:
Server IP address only 213.343.43.32
RESULT:
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: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
-----------------------------------------------------
TRIED:
213.343.43.32,1433
RESULT:
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: TCP Provider, error: 0 - A connection
attempt failed because the connected party did not properly respond after a
period of time, or established connection failed because connected host has
failed to respond.) (Microsoft SQL Server, Error: 10060)
---------------------------------------------------------------------------
This is whole story, i dont know what going on.
AND
MsDtsSrvr.exe file is also missing from DTS/BINN folder?????????
Nasir Hussain.
July 15, 2010 at 12:52 pm
Since telnet failed and since 1433 is in fact the port, you are going to need to contact your network administrator for assistance. It is likely that 1433 is blocked on the firewall (I don't mean the Windows firewall but rather the network one).
At my company, we block port 1433 and instead use a custom port and then add a firewall hole for that custom port. 1433 is too commonly known to hackers, so a custom port decreases our vulnerability a tiny bit. We have loads of other things that practically eliminate our vulernabilities, so not using 1433 is just one of them.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 15, 2010 at 6:50 pm
I have been trying to solve this problem since 3 days now...
Finally, I found that COX (my ISP) is blockng the port 1433 🙁
I have changed the default port and boom it worked like a charm 🙂
Thanks guys for the help ...
July 16, 2010 at 6:38 am
Congrats VPilli,
what i find is:
MsDtsSrvr.exe file is missing in 100/DTS/Binn folder and i think i hav to reinstall the SQL Server 2008:crying:
I already tried with custom port but it does not work for me.
Dear Tara any of ur suggestion will b highly appreciated and thank you so much for your help:-)
NasirH
July 16, 2010 at 9:27 am
I don't see how MsDtsSrvr.exe relates to the issue you are encountering.
I don't have any other ideas except that you need to contact your network administrator for assistance.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 16, 2010 at 11:09 am
On server its SQL server Express edition 2008 is install and i installed SQL Enterprise edition 2008 on another machine windows server 2008 i try to access that in LAN it is connecting without any problem, Do you think it because of different SQL editions.
Could you please let me know how to change it to the custom port??
NasirH
July 16, 2010 at 11:26 am
It is definitely not because of the edition. It is almost certainly because of the firewall blocking you. Even if you use a custom port, you'd still need to have the network admin punch a hole in the firewall if the firewall is configured right that is.
To change to a custom port, you make the change in the SQL Server Configuration Manager tool. You go to the TCP/IP settings for the instance, and then at the very bottom you remove the dynamic port and enter a static port into the field below it.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply