none
tcp port not opened

    Question

  • I have a database setup and it works fine locally. I can connect management studio to it (using the same computer).

    The problem is connecting another computer to it. I've enabled TCP/IP protocol, and I'm running SQL Server Browsing, I also disabled the private firewall (Both of my computers are inside my house [private firewall is disable in both of them]). I'm running Ms SQL 2012 express on Windows 7. The machine that I'm trying to connect would generated a network-related error. I tried to telnet into it (i.e. telnet <machineName> 1433), and it seems that it's closed. What am I missing here? I tried to look for a command that will tell me what ports are open in my database machine, but could not find it. I was wondering if you guys would help me resolve this problem.

    Thanks.

    I'm attaching some configuration pictures, so you would see the setup...

    Saturday, February 02, 2013 3:04 AM

Answers

All replies

  • And the main window picture

    Saturday, February 02, 2013 3:05 AM
  • i just ran netstat command and indeed port 1433 is not in "listening" status

    Saturday, February 02, 2013 3:27 AM
  • i just ran netstat command and indeed port 1433 is not in "listening" status

    Hello,

    Port 1433 is the common port for SQL Server; but that doesn't mean, that port 1433 is always the used port. In "Protocols for SQL Server" do a right-mouse click on TCP/IP => "Properties" to see, if really 1433 or a different port is used for SQL Server.


    Olaf Helper

    Blog Xing

    Saturday, February 02, 2013 3:36 AM
  • Well I don't see default values there, I see values for "Enabled". "Keep Alive", "Listen All"....then in IP Addresses tab, I see IP1, IP2 ... IP7. and IPAll, their dynamic ports are 0 in all of them except IPAll, IPAll has a value of 49156, none of them have TCP Port value...

    Now if I check the properties of TCP Protocol under "Client Protocols", then yes I see the default port value which is 1433

    Saturday, February 02, 2013 3:47 AM
  • IPAll has a value of 49156


    Then the used port is 49156, not 1433.

    Olaf Helper

    Blog Xing

    Saturday, February 02, 2013 3:51 AM
  • Whats the difference of dynamic port and regular port? I made that change, restarted the service and I still can't telnet into that port. Although when I run de netstat , I see 1433 in listening status. But if I tried to telnet the port above 1433 (from the netstat list), I'm able to telnet into it....
    Saturday, February 02, 2013 4:01 AM
  • Dynamic ports can change with each SQL Server service restart. The "SQL Server Browser" is reliable for resolving the instance name like SQLEXPRESS to the used ip port; so sure this services is running.

    When you open SQL Server logs you should find a log entry like

    Server is listening on [ 'any' <ipv4> 49503].

    which stats, that SQL Server uses port 49503 (in this case)

    Olaf Helper

    Blog Xing

    Saturday, February 02, 2013 4:13 AM
  • Yes the SQL browser is running and the log says

    2013-02-02 06:43:24.92 spid12s     Server is listening on [ 'any' <ipv6> 1433].
    2013-02-02 06:43:24.92 spid12s     Server is listening on [ 'any' <ipv4> 1433].

    So let me get this straight, if we are talking to dynamic port, then that means it's going to change, and if it does, SQL Server Browser will handle it.... So what's the point in changing the port? At first I wanted to change it because I thought Management Studio from the other computer was trying to use that port. Anyways, it seems to be listening on port 1433. but I still can't connect to it, nor can't telnet to that port. Again, the private fire wall is turned off, so I don't think firewall is an issue here.

    Saturday, February 02, 2013 12:48 PM
  • Am i out of options?
    Saturday, February 02, 2013 8:00 PM
  • I tried changing the port back and forth, disabling all firewalls, and nothing works. It's interesting, one time I had MySQL setup in a linux box, and I never had this problem. Is there something else I need to take care extra besides firewall?
    Sunday, February 03, 2013 3:54 PM
  • When exactly do you get an error? What's the exact error message? Which "server name" are you using to connect to? You you are work local on the same machine you can use ".\SQLEXPRESS" as server name.

    Olaf Helper

    Blog Xing

    Sunday, February 03, 2013 6:08 PM
  • Detailed troubleshooting steps at How to Troubleshoot Connecting to the SQL Server Database Engine

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by raac130 Wednesday, February 06, 2013 12:43 AM
    Monday, February 04, 2013 4:40 PM
  • These steps are awesome. I isolated the problem, I'm able to access the database using the ip address. But not the name. I'm able to ping the computer name, but check this out. The computer where I have the database is called "MachineA", the client is called "Laptop" (Verified from the router configuration page). When I do "ping MachineA" on the client, it returns

    "Reply from 127.0.01 ...." (loop-back), It thinks "MachineA" the local machine, I've got no idea why it thinks that.

    I did a ipconfig /flushdns, but that didn't fix the problem.

    Any ideas?

    Tuesday, February 05, 2013 12:17 AM
  • Odd. A couple things to check. Open Windows Explorer. In the left pane, right-click Computer (or My Computer), and then click Properties. The Computer Name is listed there, too. Same name (Laptop)? Any chance both computers have the same name?

    Or, you might be having an LMHost file issue. This isn't really my area of expertise, as it is networking, not SQL Server. Lots of help for this on the web though. Maybe start with http://support.microsoft.com/kb/101927


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, February 05, 2013 9:46 PM
  • Yes it is the same...."Laptop". You're right that's a different area, has nothing to do with the database....

    Thank so much!!

    Wednesday, February 06, 2013 12:43 AM