How to configure SQL Server static port with Powershell

20 April,2021 by Jack Vamvas

Question: I have an  SQL Server Instance - currently configured with a Dynamic Port. I'd like to change the setting from a Dynamic port configuration to a Static port configuration , using Powershell.

I want to change to a static port as we need to set up some Firewall rules , and using a static port will be much easier

How can this be done?

Answer: It is possible to complete this task using Powershell scripting. If a SQL Server instance is configured with a static port , SQL Server only listens on the specified static port, But it is important to delete all entries for dynamic ports otherwise SQL Server will listen on the static and dynamic ports.

Before we start , you'll notice in the SQL Server Configuration Manager the settings indicating the SQL Server Instance is set as dynamic port

Sql_dynamic_port

 

The aim is to adjust these settings to change from dynamic port to a static port setting.For testing purposes - I'll change the setting to be 50000 as a  static port 

Set-SqlNetworkConfiguration -ServerInstance MYINST -Protocol TCP -Port 50000 -ForceServiceRestart -Credential $cred

 

Note: For this Powershell cmdlet to work there is a  dependency on the Cloud Adapter Service, which if you consider from a security perspective exposes a potential attack vector to accessing the server. Full consideration should be given to weighing up ease of use versus potential drawbacks

If the SQL Cloud Adapter is not installed on the server - executing the above command will issue an error similar to the below

Set-SqlNetworkConfiguration : Could not load file or assembly 'Microsoft.SqlServer.Management.CloudAdapter.Data,
Version=15.0.0.0, Culture=neutral, PublicKeyToken=8098098098' or one of its dependencies. The system cannot find
the file specified.

 

Read more on SQL Server Ports

SQL Server – Find SQL Server tcp port with Powershell

How to get SQL port number using xp_readererrorlog


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to configure SQL Server static port with Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer