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.

This question is in regards with SQL server 2005 and 2012.

I have a database server with two instances of SQL Server. Through SQL server management studio I was able to connect to both instances by providing "IP-Address"\"InstanceName" and user ID/password. But, I was not able to telnet the server on port 1433 from command prompt of the client PC.

Finally, when I specified TCP port 1433 through "SQL server configuration manager" on the server and restart SQL server services I was able to telnet to server on port 1433.

I want to know how "SQL server management studio" was able to make connection with SQL Server even if port 1433 was not active on SQL server? And, the sequence of activities that takes place in background when management studio initiates connection with SQL server?

Regards, Deepak Bisht

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Please do not change Sql Server's TCP port unless you know what you are doing. This needs to be done only in specific scenarios like manual SPN registrations with failover cluster setups or restricted environments.

Sql Server installations contain a special service called Sql Browser Service. When a client tries to connect to a named instance, it will ask connection details from the browser service. As per the documentation:

Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use --

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

share|improve this answer
    
I changed TCP port to 1433 in order to setup port monitoring from a tool. Thanks for the information... this is exactly the information I was looking for. –  Deepak Bisht Oct 25 '13 at 7:12

The port 1433 is reserved for the default instance (MSSQLSERVER). When you have a named instance, where you can connect via "IP-Address"\"InstanceName", the port number is different. You can see the actual port number when you look into your SQL Server Logfile.

share|improve this answer
    
,the other way is using sql server configuration manager where you can see the port used for TCP/IP protocol under sql server network configuraion –  Biju jose Oct 24 '13 at 15:58

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.