Last updated on August 2nd, 2022 at 10:16 am
As Microsoft Endpoint Configuration Manager (MEMCM / MECM / SCCM / ConfigMgr ) administrator we wear many hats. One of those hats is SQL Server administration. We use SQL for several components within ConfigMgr including ConfigMgr, WSUS and SQL Server Reporting Services (SSRS). But what if we wanted to setup Remote SQL Server with a Named Instance? In my last post I covered remote SQL servers with non-default ports. But, what if you have a remote SQL server with a named instance? Named instances have different requirements. In this case, you must use dynamic ports.
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?
Why use a non-default port?
There are many reason to this this, the first reason that you will always hear is for security. aka security by obscurity. if some trying to see if anything is responding to request on port 1433. Then they will know that SQL server is likely installed. This give them an attach vector to exploit.
Configurating Remote SQL Server with a Named Instance
The next step starts within 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 Dynamic Ports to the port you will be using.
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 dynamic port.
Summary
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.