Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Default SQL Server Network Configuration (for 2008, can't find 2012 version but I don't think it's any different) –  Damien_The_Unbeliever Dec 10 '13 at 8:13
    
Thanks. A really good help for me. Any more comments about my DSN crating? –  anaconda_wly Dec 10 '13 at 8:48
    
It looks like you're using a named instance (only the default instance listens on 1433, so far as I'm aware). Are you including the instance name when trying to create the DSN? –  Damien_The_Unbeliever Dec 10 '13 at 8:50
    
Yes, when install, I entered a name for Instance. When creating DSN, I was asked to input a DSN name(I entered an arbitary name), SQL server to connect to(This is a drop-down selection box and I tried two choices here both fail, one is (local), another is <.MyInstanceName>), –  anaconda_wly Dec 10 '13 at 8:57
    
I didn't see any other place to include the MyInstanceName yet. And the next button direct me an authentication as well as a <Client Configuration> which I can set the <Server Alias>, <Server Name> and check <Dynamically determine port>. –  anaconda_wly Dec 10 '13 at 9:02
add comment

migrated from stackoverflow.com Dec 10 '13 at 9:11

This question came from our site for professional and enthusiast programmers.

1 Answer

Why 52395 instead of 1433

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.