Using Non-Standard Port for SQL Server
July 20, 2007
Are you using the standard port number 1433 for communicating with SQL Server 2005? Have you considered setting up SQL Server to listen on a different port number than 1433? Well I did. In this article, I will discuss how I did it and the issues I came across when I setup up SQL Server 2005 to use a non-standard port number.
Issues with Using default Port Numbers
When you install SQL Server, the default instance is setup to listen on port 1433. This is the default port number and it is well known. Because of this, you commonly see unwanted attacks against port 1433 from hackers. So to minimize the potential threat of a hacker gaining access through this default port number you should consider using a non-standard port number other than 1433.
When a named instance is installed, by default it dynamically determines the port number to use when it starts up. Therefore, each time a named instance is started it has the potential to be listening on a different port number. This can be problematic, especially if you need traffic to come through a firewall. Since the port number might change dynamically, you cant restrict traffic to your SQL Server for a specific port number with your firewall rules. Therefore, in order to restrict traffic to your SQL Server box you will need to configure SQL Server to listen on specific ports.
How to Configure SQL Server to Listen on Specific Port
Since the default port number is vulnerable to hacker attaches and the named instances dynamically set the port number, you should consider specifying each of your instances of SQL Server to listen on a specific port. Having SQL Server using a specific port number provides a more secure environment. So how do you configure SQL Server 2005 to listen on a different port? To accomplish this, SQL Server provides the SQL Server Configuration Manager tool.
To start Configuration Manager left click on the Start button, navigate to All Programs, then to Microsoft SQL Server 2005, then to Configuration Tools, and then finally left click on the SQL Server Configuration Manager drop down. Once you have SQL Server Configuration Manager up and running expand the SQL Server 2005 Network Configuration folder. When you do this, you should see a screen similar to the one below:
Here you can see I have 4 different instances of SQL Server running. One is the default instance, named MSSQLSERVER, one is an Express edition named SQLEXPRESS and then I have two named instances, named SERVER1 and SERVER2.
To change the port assignment of one of these instances I would left click on the instance, which would then display the different available protocols. Below is a screen shot where I left clicked on instance SERVER2:
Here you can see I have two protocols enabled, Shared Memory and TCP/IP. To change the port assignment, right click on the TCP/IP protocol in the right pane, and select the Properties option. When you do this, the following screen shot should be displayed:
From this screen, you can see that the Listen All setting is Yes. This means that this instance will listen on all IP addresses. Listening on all IP addresses is the default when you install an instance of SQL Server. If you only want SQL Server to listen on a specific IP address then you would need to change the Listen All setting to No.
To identify a specific port that you want SQL Server to listen on you need to left click on the IP Addresses tab on the above screen. When you do this, a screen similar to below will be displayed:
As you can see IP1 and IP2 are disabled (Enabled set to No), and the IPALL TCP Dynamic Ports is set to 1317. Since SERVER2 is a named instance the IP address is set dynamically when the instance is started, port 1317 just happens to be the port number my named instances is currently using. If I was to stop this instance and restart it there is the potential that when this instance comes back up it might be using a different port, because the port number is set dynamically. When the TCP Dynamic Ports setting is set to 0 it indicates that the Database Engine is listening on dynamic ports.
To ensure that a named instances uses a consistence port number, or you want to change the default port number for the default instance all you need to do is identify a specific port number that your SQL Server instance should be listen on, like so:
Here I have specified that I want to use port 8484 as the port number for my SERVER2 named instance. I did this by placing this port number on the TCP Port option.
Connecting Clients When using Specific Part Assignments
Connecting a client to a SQL Server instance using a non-standard, depending on your installation setup, can be problematic, especially if you are not running the SQL Server Browser service. There are three different ways to connect a client to an instance that is using a specific non-standard port number.
The first method is to run the SQL Server Browser service. When this service is running, it communicates with the client informing the client what port number is being used for the instance in which a connection is being requested. This eliminates the client from having to know the port number when connecting to an instance of SQL Server. If you want a more secure environment then you will not run the SQL Server Browser service. By not running the SQL Server Browser service, the client will need to identify the correct port number in the connection string in order to connect to SQL Server.
The next way to connect a client is to create a SQL Server Alias on the client machine. An alias can be created on the client machine by running the SQL Server Configuration Manager tool. Under the SQL Server Native Client folder, right click on the Aliases item and select the New Aliases item. When doing this the Alias New dialog box will be displayed. In the new alias dialog box, you can create an alias where you can specify the instance name and the port number it is using. Below is a screen shot of how I would create an alias for my SERVER2 named instance to identify that I want to connect using port 8484.
Note I have identified an Alias Name of SERVER2, which is using a Port No of 8484, and a Server value of SERVER2. Building this alias associated the alias name SERVER2 with server SERVER2 and port number 8484. Once Ive specified this information I can either click on OK, or Apply and then OK to create my alias.
The last method is to programmatically specify the port number in the connection string. Im not sure of all the different methods to accomplish this since Im not a connection string guru, but one way is to specify the port number along with the server name in the connection string. This is done by placing ,<port number> immediately following the server name. So for my SERVER2 example above, I would have a connection string that looks something like so:
Data Source=SERVER2,8484;Initial Catalog=AdventureWorks
Note Ive added a ,8484 right after the name of my server in the Data Source parameter of the connection string.
Issues with Using Non-Standard Port
Ive found no real technology issues associated with using a Non-Standard Port number, or specifying a specific port number for a named instance. The biggest problem Ive encountered is the fact that programmers keep coming to me saying, I cant connect to SQL Server. Then when I work with them to identify the connectivity issue, we find that they have forgotten to specify the port number association along with the server name when they try to connect. Therefore, if you plan to use non-standard port numbers you need to make sure you clearly explain how the connection strings will need to have a port number in it to ensure a connection will be made successfully.
As you can see there isnt that much work to setup your SQL Server instances to use a specific port number. The biggest issue is making sure the connection strings are coded correctly. When setting a port number make sure you find a high port number that is not being used. To make your environment more secure I would suggest you turn off the SQL Server Browser services. By doing this all clients will be required to specify the port number when connecting to SQL Server.