SQL Server Port: Learn How Your Database Answers Queries

You’ve probably heard of computer ports. Computer ports allow you to plug in hardware such as a mouse or a keyboard. These are physical computer ports. In contrast, virtual ports are also present on your computer. Virtual ports give computers the ability to run applications and “listen” for connections from external computers. Virtual ports are used by SQL Server to run your database query and listen for application and external computer connections.

In charge of a SQL Server? Use this course to get some training.

What are Virtual Ports?

With a physical port, you’re only able to plug in one device into one port. The same goes for virtual ports. You can only run one application on one port at a time. If you try to run two applications on one virtual port, you run the risk of both or at least one of the applications crashing.

There are 65535 virtual ports available on a computer. Typically, low numbers are reserved for common applications.  For instance, port 80 is reserved for the HTTP protocol (websites), DNS uses port 53 and SMTP (email) uses port 25. These are just a few of the common applications that run on most computers. To ensure that an application doesn’t run on one of these ports, most developers create applications that run on high numbers such as port 22222 or port 65222.

What Port Does SQL Server Run On?

By default, Microsoft sets the SQL Server port to 1433. In other words, your database application listens on virtual port 1433. If you run any other application on this port, you run the risk of crashing your database’s listening process, so your database will no longer answer to requests for queries.

SQL Server has a graphical interface that allows you to edit this virtual port. You can also turn protocols off and on. In most networking applications, you want SQL Server to use the TCP/IP protocol. However, SQL Server also supports Named Pipes.

To open SQL Server’s configuration manager, log in to your database server. Click the Windows “Start” button and click the program group for your SQL Server version. In this example, SQL Server 2008 is used. Click “SQL Server Configuration Manager” in the configuration folder.

Get some SQL Server training at Udemy.com.

A window that looks like the following is displayed:

Click the “SQL Server Native Client” option to expand your options .Click “Client Protocols.” Notice in the right panel that the supported protocols are listed. For most networks, you want to configure TCP/IP for SQL Server. If this protocol is disabled, enable it.

Double-click the “TCP/IP” option to open the configuration window. If the “Enabled” option is “No,” flip it to “Yes.” This will allow your users and applications to reach the database server using TCP/IP.

The first option in the configuration window is the default port. Notice that it’s set to 1433. You can change this number from 0 to 65535. However, not every one of these virtual port numbers is available. For instance, port 7 is reserved for the ping command, so you can’t run your database on port 7 safely. The safest route for picking a port is to look up the reserved port numbers. Find a port number that is not on the list. Be aware that even if a port is free does not mean that it’s free on your database server. Another application might already use the port.

You can use the “netstat” utility to find listening ports. Open the Windows command line and type “netstat –an /I” and press “Enter.” This utility will show you all listening ports. Cross check listening ports with the list of reserved ports and find a port that isn’t used. This is the safest way to choose an alternative port for your SQL Server.

Take the SQL Server 101 course at Udemy.com

After you determine your port, type it into the “default port” configuration option in SQL Server Configuration Manager. When you’re finished, click “OK” to apply the changes.

One issue to note is that when you change the default SQL Server port, you must tell your end users and application developers. Your users and applications will need to specify the new alternative port number before being able to connect.

Using an alternative port is better for security. Hackers will try to attack your SQL Server on the default port. While this isn’t fool-proof, it does help defend against common scripts that run automatically and look for databases running on the default port.

Virtual Ports and Your Firewall

Another issue with changing the port is the firewall. By default, a router might block the new port you choose for your database. This will block users and applications from connecting.

You can run firewall software on a router, your server or both. If you use an external hosting company, you will have to ask the host to open a new port on the router if you want to allow external connections. This is rare with database applications, but you’ll need to open the port if you connect to your SQL Server from external applications that run on the Internet. You’ll also need the port open if you connect to the SQL Server from another location using SQL Server Management Studio.

If you run the Windows firewall application that comes with the operating system, you need to open the port in that software as well. The Windows firewall application can be found in the Control Panel. Open the Control Panel and navigate to “System and Security.” Click the “Windows Firewall” option to open the configurations window.  In the left navigation panel, click “Allow a program or feature through Windows Firewall.” In this configuration window, type the application and the port number you want to open.

SQL Server is just one application you will probably run on your database server. You have thousands of virtual ports to monitor and edit. However, if you want to run a SQL Server database, you need an open port to server any kind of queries and data sets.