SQL Server is started but didn’t listen on any port

http://dba.stackexchange.com/questions/54767/sql-server-is-started-but-didnt-listen-on-any-port

Question:

I newly installed SQL Server 2012. No database created yet. And I can open the Management Studio with sa/password. The Binn\sqlservr.exe is started as a service and I can see it started in the service list. However, it didn’t listening the 1433 port, or even, not listening any port by default, as I checked as below:

tasklist|find /I "sql"

I got:

sqlservr.exe                  5668 Services                   0     40,112 K

in which 5668 I think is the PID. And then to get PID = “5668” is listening to which port:

netstat -ano | find /I "5668"

but I got nothing except blank. On the other hand, after searching posts in this site, I double-checked my SQL Server’s configuration: start->All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager-> SQL Server SQL Native Client 11.0 Configuration -> Client Protocols -> TCP/IP ->Default Port 1433. There’re 3 services, SQL Server(MyInstanceName), SQL Server Agent(MyInstanceName) and SQL Server Browser. The last two is stopped.

SQL Server Configuration Manager-> SQL Server Network Configuration->Protocols for MyInstanceName -> TCP/IP is disabled by default, after I enabled it and restart the service and repeat the netstat -ano | find /I “newPIDNum” command I found the

TCP    0.0.0.0:52395          0.0.0.0:0              LISTENING  5668(newPIDNum)   

Why 52395 instead of 1433? And I failed to create the DSN using Windows ODBC Data Source administrator which returns me an error: specified SQL server not found. Any help for me? Thanks.

 

Answer 1:

Named instances listen on dynamic ports. Is the job of the Sql Server Browser Service to inform the clients of the actual port. The Sql Browser listens on UDP 1434 and answers questions of the form ‘what is the listenning port of instance “foo”?’. Sql Server Browser service is required for both TCP and named pipes protocols. Clients use the SQL Server Browser transparently, no need for special configuration. By simply specifying an instance name in the connection string (or the ODBC DSN), the client library know it has to contact the SQL Server Browser service first.

There’re 3 services, SQL Server(MyInstanceName), SQL Server Agent(MyInstanceName) and SQL Server Browser. The last two is stopped

Obviously for SQL Server Browser to do its job, it needs to be started. Start it, and change the start up type to automatic.

As a side note, an often used alternative configuration is to use static ports for the named instance and specify the port in the connection string (tcp:<hostname>:<port>). It has the advantage of taking SQL Server Browser out of the equation, but it requires careful maintenance of clients. Changing the listening port requires orchestrated changes at all clients, all machines, all locations, usually a big pain. I recommend taking the dependency on SQL Server Browser instead.

 

 

Answer 2:

 

What made my SQL server listen for TCP connections on port 1433 was the following…

  1. Configuration Tools / SQL Server Configuration Manager
  2. click the instance name in SQL Server Network Configuration
  3. enable TCP communication
  4. Right-click the TCP communication entry, choose Properties
  5. Click the IP addresses tab
  6. Enable all non-VMnet IP addresses and enter 1433 in the Port entry for “IPAll”.

Please note: I enabled TCP and disabled Named Pipes for clients in the same Manager. To test, I ranosql -E and netstat -an,

$ netstat -an | grep 1433
TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
TCP    10.201.130.153:8705    10.201.130.153:1433    TIME_WAIT
TCP    [::]:1433              [::]:0                 LISTENING