Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What port is my SQL2008 instance running on?

Tags:

sql-server

Check out this pic of my SQL 2008 Management Configuration screen for TCP: alt text

I need to know what port I am running on. Based on this previous post, I don't believe that it is 1433, which I guess is the default.

When I check my port number in SQL Config Mgr., it is blank. Does this suggest a default port? Note that I have multiple SQL Server instances on the db server. One 2000 instance and two 2008 instances. I want to know the port of the 2008b instance.

The first display is for TCP. I'm not sure what VIA is, but check it out:

alt text

Can you help?

Update

I restarted my sql2008b instance and checked the log to look for a msg that indicated the port that it was using. It showed this:

2010-05-22 20:06:29.27 Server      A self-generated certificate was successfully loaded for encryption.
2010-05-22 20:06:29.27 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2008B ].
2010-05-22 20:06:29.27 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2008B\sql\query ].
2010-05-22 20:06:29.32 Server      Server is listening on [ 127.0.0.1 <ipv4> 5786].
2010-05-22 20:06:29.32 Server      Dedicated admin connection support was established for listening locally on port 5786.

Yet, when I tried to log on using the following as the server name:

(local)\sql2008b,5786

I still was unable to connect, while logged onto the remote box (hence using "local".)

A suggstion was made below to set the port myself. This occurred to me to try but there are a couple of things that are botehring me: 1) Why can I connect to my SQL 2000 instance without monkeying with anything to get it to work (but not my 2008 instance)? 2) The IP addresses shown in TCP1 and 2 don't appear correct. The one I blacnked out, presumably theIP address of my router and the one needed to make my server visible on the Internet, was not correct. Also , the local 192.168.1.100 was not correct. The db server server's IP adress end in a different number. perhaps I sh

like image 430
Chad Avatar asked May 22 '10 20:05

Chad


People also ask

What port is my SQL instance using?

If enabled, the default instance of the SQL Server Database Engine listens on TCP port 1433.

How do I find my port number?

How to find your port number on Windows. Type “Cmd” in the search box. Open “Command Prompt”. Enter the netstat -a command to see your port numbers.

How can I tell if port 1433 is open?

On the local machine, click the Start button and enter “CMD” in the search programs and files field. If the port 1433 is closed, an error will be returned immediately. If the port 1433 is open, you will be able to connect to the MS-SQL server.

What is the port for MSSQLSERVER?

By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434.


2 Answers

TCP/IP is disabled in your screenshot so it isn't listening on any port. On start up when enabled it will write an entry to the SQL Server logs telling you what port it is listening to. (Accessible through Management Studio tree view Management -> Sql Server Logs nodes)

You might find this link useful How to configure an instance of SQL Server to listen on a specific TCP port or dynamic port

Also do you have the SQL Server Browser service running on that machine (it might help you connecting to the right instance)?

like image 177
Martin Smith Avatar answered Sep 19 '22 03:09

Martin Smith


If TCP/IP is disabled, the service can still listen via Named Pipes (for network connections) or Shared Memory (for local connections), so it will work with TCP/IP disabled if you choose to got that route.

On startup, there will be an event log entry to tell you what connection methods it's listening for, or you can do a "NETSTAT -A" from the command line to see which ports the server is listening on in general.

like image 44
SqlRyan Avatar answered Sep 20 '22 03:09

SqlRyan