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:

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.

Thanks. A really good help for me. Any more comments about my DSN crating?
–
anaconda_wlyDec 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_UnbelieverDec 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_wlyDec 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_wlyDec 10 '13 at 9:02

1 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.

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.