How to configure SharePoint when SQL listens on a different TCP port

It’s well known that when SQL Server is installed, the default instance is setup to listen on TCP port 1433. A virus program or a hacker can access the server on port 1433. So, to prevent unwanted hits on port 1433, most of us prefer to run the SQL server on a non standard TCP port.

Here we are discussing how to configure Microsoft SQL server to listen client requests on a non-standard port and how to configure Microsoft Sharepoint server accordingly. To start with, we will discuss some basic concepts of installing Microsoft SQL server and configuring it to listen requests on non standard port.

When the Microsoft SQL server is installed with the default instance, it always listens on the fixed port 1433. This is the default behavior and port number can be set to a custom one. However for the named instance the port on which the SQL server listens is random and is dynamically selected when the named instance of the SQL server starts. This is again the default behavior when the ports are not defined for the named instance manually.

In our scenario, we need to set a non standard port for default instance or a named instance. This can be achieved using SQL server configuration manager.

I use Microsoft SQL server 2005 Standard edition installed with default instance on server named “SQLServer” and Microsoft Office Sharepoint server 2007 on server “MOSSServer” to describe the steps.

Microsoft SQL server 2005:

Start->Program Files-> Microsoft sql server 2005->Configuration Tools-> SQL server configuration manager.

In configuration manager console, on the left hand panel, expand the SQL server 2005 Network Configuration. Click on the”Protocols for <Instance_Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.

Click on the IP Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.

After performing these activities we need to restart the SQL server services for the changes to take affects. After restarting the service you can confirm that whether changes has taken affect by checking the following registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name> \MSSQLServer\SuperSocketNetLib\Tcp

This key should contain the port no. defined by you.

Confirm all necessary clients (in our case Sharepoint servers) can connect to SQL server using the non-standard port with the help of telnet and a UDL file. Make sure they connect successfully.

Microsoft Office Sharepoint server 2007:

Now our task is to make configuration wizard understand that SQL listens requests on a different port (non-standard port) and the configuration wizard to connect with SQL server using the proper port to create/modify configuration database. Now follow the steps

1. Click on Start -> Run -> type “Cliconfg”->hit enter

2. Click on Alias

3. Click on Add

4. Provide the alias name as the server name

5. Click on TCP/IP under “Network libraries”

6. Provide the server name under “connection parameters” server name or server name\instance name depending up on the SQL instance you want to connect with

7. Unselect “Dynamically determine port”

8. Provide the new port number on which the SQL server is listening

9. Click Ok

10. Click Apply and Ok

11. Runt he MOSS configuration wizard

12. When asked for the SQL server name; provide the alias created at step 4

Comments

• Anonymous
  April 16, 2013
  thanks a lot for sharing the information