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
