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:
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 “,<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 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.
