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
