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 can’t 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:

Click for larger image

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 I’ve 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. I’m not sure of all the different methods to accomplish this since I’m 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 “,” 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 I’ve 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

I’ve 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 I’ve encountered is the fact that programmers keep coming to me saying, “I can’t 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.

Conclusion

As you can see there isn’t 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.