Problem
I am trying
to understand how SQL Server communicates on the network, because I'm having
to tell my networking team what ports
to open up on the firewall for an edge web server
to communicate
back to the SQL Server on the inside. What do I need
to know?
Solution
In order
to understand what needs
to be opened where, let's first talk briefly about the two main protocols that are in common use today:
TCP - Transmission Control Protocol
UDP - User Datagram Protocol
Both are part of the TCP/IP suite of protocols. We'll start with TCP.
TCP
TCP is the main protocol by which clients communicate with SQL Server. Actually, it is more correct
to say that clients and SQL Server use Tabular Data Stream (TDS), but TDS actually sits on top of TCP and when we're talking about Windows and firewalls and other networking devices, that's the protocol that rules and controls are built around. So we'll just speak in terms of TCP.
TCP is a connection-oriented protocol. What that means is that the two systems negotiate the connection and both agree
to it. Think of it like a phone call. While one person initiates the phone call, the other person has
to agree
to take it and both people can end the phone call at any time. TCP is the same way. Both systems have
to agree
to the communications, but either side can end it at any time. In addition, there is functionality built into TCP
to ensure that all communications can be disassembled and reassembled as necessary so it can pass over various network devices and be put together again properly in the right order. It also has mechanisms
to handle and retransmit lost communications.
Because of this functionality, TCP is the protocol used by many different network applications. The way the applications all can share is through the use of ports. When a service, like SQL Server, comes up on a system, it must listen on a port. For a default SQL Server instance, the default port is 1433. Clients connect
to the port via the TCP protocol, the connection is negotiated and agreed
to, and then the two sides can transfer information as needed until either side decides
to end the communication. In actuality, both sides will have a port
to use for the communications, but since the client's port is typically determined semi-randomly, when we're talking about firewalls and the like, typically we're interested in the port the server or service is using.
UDP
UDP, unlike TCP, is not connection oriented. A "client" can send a UDP communications
to anyone it wants. There's nothing in place
to negotiate a communications connection, there's nothing in the protocol itself
to coordinate order of communications or anything like that. If that's needed, it's got
to be handled by the application or by a protocol built on top of UDP being used by the application. If you think of TCP as a phone call, think of UDP as a postcard. I can put a postcard in the mail
to anyone I want, and so long as it is addressed properly and has a stamp on it, the postal service will pick it up. Now, what happens it afterwards is not guaranteed. There's no mechanism for retransmission of lost communications. It's great for short communications that doesn't necessarily need an acknowledgement. Because multiple network applications could be communicating via UDP, it uses ports, just like TCP. The SQL Browser or the SQL Server Listener Service uses UDP.
Network Communications - Talking
to SQL Server
When an instance of SQL Server is set up, what TCP port it listens on depends. A default instance will be set up
to listen on port 1433. A named instance will be set
to a random port chosen during installation. In addition, a named instance will be configured
to allow it
to change that port dynamically. What this means is that when a named instance starts up, if it finds something already using the port it normally uses, it'll pick a new port. If you have a named instance, and you have connections coming across a firewall, you're going
to want
to use SQL Server Configuration Manager
to set a static port. This will allow the networking and security folks
to configure their devices for maximum protection. While you can change the network port for a default instance of SQL Server, most people don't.
Network Communications - Finding a SQL Server
When just the name is specified for a client
to connect
to SQL Server, for instance, MySQLServer, this is an attempt
to connect
to the default instance. In this case the client will automatically attempt
to communicate
to port 1433 on MySQLServer. If you've switched the port for the default instance, you'll need
to tell the client the proper port, usually by specifying the following syntax in the connection string: <server>,<port>. For instance, if you moved SQL Server
to listen on 14330, you'd use MySQLServer,14330 instead of just MySQLServer.
However, because a named instance sets up its port dynamically by default, the client never knows at the outset what the port is it should talk
to. That's what the SQL Browser or the SQL Server Listener Service (SQL Server 2000) is for. In this case, the client sends a communication via the UDP protocol
to port 1434. It asks, "Where is the named instance?" So if I was running a named instance called SQL2008R2, it would be asking the SQL Browser, "Hey, how do I talk
to MySQLServer\SQL2008R2?" The SQL Browser would then send
back a communications from UDP port 1434
back to the client telling the client how
to talk
to the named instance. Of course, you can skip all of this of you set that named instance's port statically. Then you can use the <server>,<port> mechanism
to connect and the client won't try
to talk
to the SQL Browser service. It'll simply try
to make the connection. So, for instance, is the SQL2008R2 instance was listening on port 20080, specifying MySQLServer,20080 would attempt a connection
to the named instance.
Network Communications - Named Pipes
Named pipes is an older network library communications mechanism and it's generally not used any longer. It shouldn't be used across a firewall. However, if for some reason you need
to connect
to SQL Server with it, this protocol also sits on top of TCP. Named Pipes is actually used by the operating system and it has its own mechanism within the protocol
to determine where
to route communications. As far as network communications is concerned, it listens on TCP port 445. This is true whether we're talking about a default or named instance of SQL Server.
The Summary Table
To put all this together, here is what you need
to know:
Type of Communication
Protocol Used
Default Port
Finding a SQL Server or SQL Server Named Instance
UDP
1434
Communicating with a default instance of SQL Server
TCP
1433
Communicating with a named instance of SQL Server
TCP
* Determined dynamically at start up
Communicating with SQL Server via Named Pipes
TCP
445
