SQLServer Port Not Showing

Running SQLServer 2014 Express on Windows 7.  When i run netstat -aon i do not see 1433 included in the list of ports.  When I try testing
from a laptop with telnet machine 1433 there is no connection, but if i telnet machine 3306 or some of the other ports listed as listening i get a 'connected' message.    Any ideas why 1433 does not show up on the list.
xoxomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
SQLExpress usually has a dynamic port, as any named instance of MSSQL. Only the default, unnamed instance uses port 1433 by default. If you want to find out the port after the fact, get the corresponding PID, and filter for that PID in netstat. E.g. if SQLExpress has PID 1234, try with netstat -ano | findstr "1234$".
You can also use the SQL Server Configuration Manager to set up a fixed port.
0
xoxomosAuthor Commented:
Even when i run the command on the Windows machine there is no connection for 1433, but 3306 connects fine.


C:\Users\ns8435>telnet localhost 1433
Connecting To localhost...Could not open connection to the host, on port 1433: C
onnect failed

C:\Users\ns8435>telnet 134.154.228.217 1433
Connecting To 134.154.228.217...Could not open connection to the host, on port 1
433: Connect failed
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did you read my comment? It doesn't make sense to telnet to a port not listening ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

xoxomosAuthor Commented:
Thanks.  How do i tell the PID for SQLServer.  Not even seeing it in Task Manager
0
xoxomosAuthor Commented:
Got it!
0
xoxomosAuthor Commented:
So is this saying 59726 is the port i should be trying to telnet to?

:\Users\ns8435> netstat -ano | findstr 2580
 TCP    0.0.0.0:59726          0.0.0.0:0              LISTENING       2580
 TCP    [::]:59726             [::]:0                 LISTENING       2580

:\Users\ns8435>
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Looks correct.
0
xoxomosAuthor Commented:
A206394:~ ctaylor$ telnet 134.154.228.217 5357
Trying 134.154.228.217...
Connected to Axum.---------------------->>>>>>>>>>>>>> makes connection
Escape character is '^]'.
Connection closed by foreign host.
A206394:~ ctaylor$ telnet 134.154.228.217 59726
Trying 134.154.228.217...


will keep 'trying' and eventually fail.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You are using a public IP for telnet. Are you going thru a firewall?
0
xoxomosAuthor Commented:
OK  that's been my guess for a while, but i must not be going through. :-(  I'm at a school and i know there are firewalls galore.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Let's step back, and ask: what is it you really want to do?
0
xoxomosAuthor Commented:
BIG THANKS for asking.  Most immediate goal is to get telnet to connect to sqlserver from OSX machine.  I believe that is the first step to getting to the point where i can issue a tsql command to connect to SQLServer.
Overall i want to connect from OSX to SQLServer.  Preferably I would be able to do it with pymssql.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You want to be able to connect from OS X to MSSQL via pymssql, which, as I read it, is a Python-based implementation to connect to MSSQL.
Part of protocol is to ask on 1434/udp (connected to the SQL Browser Service) about known MSSQL instances and the dynamic port used. That said, it should "just work" if you know the server (machine) name, and the name of the (Express) instance. You don't need to try with telnet, which is a last-resort test tool only in this case.
In pymssql.connect(server, user, password, database), you would set server to '134.154.228.217\SQLExpress', if the default instance name SQLExpress for SQL Express is used.
0
xoxomosAuthor Commented:
Python 2.7.10 (default, Jul 14 2015, 19:46:27)
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.39)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymssql
>>> sql_conn = pymssql.connect(server='Axum\SQLEXPRESS',user='sa',password='Imaster',database='master')
cursor = sqlTraceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pymssql.pyx", line 637, in pymssql.connect (pymssql.c:9508)
pymssql.OperationalError: (20009, 'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (Axum\\SQLEXPRESS)\n')


Trying using name.  Will try IP next
0
xoxomosAuthor Commented:
by IP

>>> sql_conn = pymssql.connect(server='134.154.228.217\SQLEXPRESS',user='sa',password='Imaster',database='master')

cursorTraceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pymssql.pyx", line 637, in pymssql.connect (pymssql.c:9508)
pymssql.OperationalError: (20009, 'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (134.154.228.217\\SQLEXPRESS)\n')
0
xoxomosAuthor Commented:
Thanks.  One step back and if pymssql is installed and you've having trouble next step back is try tsql:
A206394:~ ctaylor$ tsql -H Axum -U sa -P Imaster -p 1433
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
39

This should return a prompt where you should be able to run a query such as select count(*) from dingbats.  Instead this one starts at 1 goes up to 150 and bombs out.
As you stated at the point where you're tempted to abandon OSX  and go to Windows 10, you try the telnet, which is where I started you, to see if you can even connect to 1433.
0
xoxomosAuthor Commented:
No apologies accepted.  Obviously I don't know anything about pymssql either.:-)
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The connect more probably is
   tsql -H Axum\SQLExpress -U sa -P Imaster
but only if the instance name is SQLExpress. You should go and look into the MSSQL installation of that server to make sure
a) SQL Browser runs, if there is any port used different than 1433/tcp
b) SQLExpress is the instance name
c) which port it is using by looking into SQL Configuration Manager.

If you can't check any of those points above, you need the exact data from someone who knows them.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xoxomosAuthor Commented:
Thanks.  Browser is not running and i need to figure what is needed to get it going first thing.
0
xoxomosAuthor Commented:
Looks like browser is 1434 UDP
  UDP    192.168.56.1:1900      *:*                                    1636
  UDP    [::]:123               *:*                                    344
  UDP    [::]:1434              *:*                                    5524
  UDP    [::]:3702              *:*                                    1636
  UDP    [::]:3702              *:*                                    1636
  UDP    [::]:5355              *:*                                    1164
  UDP    [::]:49792             *:*                                    1636 ???
0
xoxomosAuthor Commented:
a) SQL Browser runs, if there is any port used different than 1433/tcp
    >>>>>   1434 UDP
b) SQLExpress is the instance name
    >>>>>> SQLEXPRESS
c) which port it is using by looking into SQL Configuration Manager.
   >>>>>> Under SQL Native Client 11.0 Configuration (32 bit)   1433  Default port
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There is a different setup for the server, something like SQL Server Network Configuration
0
xoxomosAuthor Commented:
CONFIGURATION SCREENSHOT
0
xoxomosAuthor Commented:
>>> import pymssql
>>> sql_conn=(server='134.154.228.217\SQLEXPRESS',user='sa',password='Imaster',database='master')
  File "<stdin>", line 1
    sql_conn=(server='134.154.228.217\SQLEXPRESS',user='sa',password='Imaster',database='master')
                    ^
SyntaxError: invalid syntax
>>> sql_conn=pymssql.connect(server='134.154.228.217\SQLEXPRESS',user='sa',password='Imaster',database='master')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pymssql.pyx", line 637, in pymssql.connect (pymssql.c:9508)
pymssql.OperationalError: (20009, 'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (134.154.228.217\\SQLEXPRESS)\n')
>>>
0
xoxomosAuthor Commented:
1433 DEFAULT PORT
SQLEXPRESS.png
SQLEXPRESS.png
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That still are the client parameters. The server's is just below those.
0
xoxomosAuthor Commented:
Protocols for SQLEXPRESS ??
SQLEXPRESS.png
0
xoxomosAuthor Commented:
Thanks.  Actually i believe getting the browser going is sufficent for this question although i'm still at a loss trying to connect using tsql.
Thanks again.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The last screenshot is correct, but you have to scroll down to the last setting ("IP All", IIRC). There you will find a dynamic or static port setting (different from 0). The dynamic port can change, of course, but if the static port is set up, this one is used - including a failure to start MSSQL if that port is in use otherwise.
But yes, getting SQL Browser Service running is the better option. A connection string for MSSQL can either use
   hostnameOrIP\instancename
or
  hostnameOrIP,portno
with the first asking SQL Browser for the port. The second does not require that service, but a known port.
0
xoxomosAuthor Commented:
Stumbled on handy tool :-)

Identify Open Ports on Your Internet Connection
your external address
134.154.228.217
open port finder
Remote Address  
134.154.228.217
 
  Use Current IP
Port Number
1433
 Check
Closed Port 1433 is closed on 134.154.228.217.
Is your router causing you massive grief? Try picking up a cheap Netgear N600 on Amazon or Newegg. Since I bought one last year, I've never had to reboot it. Port forwarding is a breeze to setup.
0
xoxomosAuthor Commented:
I needed to change that bottom  port number you pointed to from some dynamically assigned port to 1433.  After that stuff worked.  
Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.