Dynamic SQL Port - Log into a Logfile

SQL Server Administration
1

I do run a sql server light 2014 version..
and after reboot somtimes dynamic port tcp is changing..
so i have to add that new port into firewall incoming rules to let sql server clients work again and
gain access to the server machine through the firewall.
my questions are:

  • is there any possibility to prevent this behaviour of changing daynmic ports in reboot?
  • is there any chance to log the current and old port into a logfile on desktop by scripting or so?
  • is there maybe a chance if happens.. to let the new port number directly let update firewall and
    give the new port number there without interactions?
    all clients are windows 10 clients and sql machine too.

thx for help, bernd

2

Why not just set it to a fixed port number?

3

Hello AndyC! First of all, thx a lot for help!

I would have assumed that a fixed port not set for the first time a data is set?
Why is the port changed after Reboot without any Action by other side? Any Idea?

How to set as a fixed port? Would be nice to explain it a bit. Would be another solution!

i just found out how to read the port using reg query...

REG QUERY 'HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\xxxxx\MSSQLServer\SuperSocketNetLib\Tcp'

output: TcpPort REG_SZ 11111

Would i be able to set that new given port then into firewall using command line like:

{FEA89B92-..........} found out by searching in registry for port number ...:>)):

REG ADD HKLM\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\FirewallRules /v {FEA89B92-..........} /t REG_SZ /v2.28|Action=Allow|Active=TRUE|Dir=In|Protocol=6|LPort=11112|Name=SQL-TCP|

problem is i want to overwrite the existing data cause there maybe still old value 11114 for ecample is still inserted...

ok, found info:

...community.spiceworks.com/how_to/124598-find-the-port-sql-server-is-using-and-change-a-dynamic-port-to-static

.. but maybe you can have still a look to my reg add question, or ist there something like reg change?

4

when the system is rebooted the dynamic port being used by sql is released and will be used by any other process looking for port. Making it static will reserve it for the sql server.

5

Thx a lot ahmeds08, think will change it to fixed sometimes after office hours.. cause i think there will a db disconnect a short one then..

what about my reg add question, have you got an idea?
have a nice day!

6

Playing with registry is something that is not recommended.
I would suggest you to just use static port.

7

As ahmeds08 says, please don't change the registry directly. You can use SQL Server Configuration Manager to change the Network Configuration of the server and assign it a static port. It will require a restart of the service.