Sometimes people want to make life difficult for themselves. SQL deployments in a Microsoft Endpoint Configuration Manager (MEMCM / MECM / SCCM /ConfigMgr ) environment come to mind. In one such example, deploying a remote SQL installation with a default instance name and using a non-default port. By default, SQL will use port 1433. But what to do if you choose a different port to use? This blog will cover how to setup SQL with Non Default Port.
As a ConfigMgr admin, we wear a lot of hats! This task is an example of how we need to know Firewall, SQL Server Administration and Security! This is way I say that ConfigMgr administrators are some of the best in the world. Why because we need to work with and know everything! What other positions, need to know SQL, Server OS, firewall, workstations OS, Application packaging, SSRS, PowerShell, Security, diplomacy, documentation writing, testing, etc.! None that I can think of!
Let get started! First you will need to open the new port in the firewall. I will not cover this step in details. But if you are looking for the basic steps, please see Why Can’t I Access My SSRS Site Remotely?
SQL with Non Default Port
Next, in SQL Server Configuration Manager, navigate to Protocols for MSSQLSERVER.
Open the TCP/IP properties page.
Clear all the TCP Ports. In the IPAll portion, set the TCP port to the port you wish to use.
Now that the port is set, configuring the Configuration Manager setup and later the Reporting Services point, will ensure that they will communicate with SQL on the desired port.