none
SS 2017 Developer cannot set port number RRS feed

  • Question

  • I cannot get SQL Server on a named instance to open a port.

    What am I doing wrong?Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)  

    Filtering SQL Server logs on 'listening on':

    07/23/2019 07:18:04,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 55299].
    07/23/2019 07:18:04,Server,Unknown,Server is listening on [ ::1 <ipv6> 55299].

    From registry:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER01\MSSQLServer\SuperSocketNetLib\Tcp\IP1]
    "Enabled"=dword:00000001
    "Active"=dword:00000001
    "TcpPort"="1435"
    "TcpDynamicPorts"=""
    "DisplayName"="SQL Server"
    "IpAddress"="192.168.10.19"

    [Forum will not allow me to attach images]

    From Sql Server configuration Manager, TCP/IP Properties:

    IP1

    Active Yes

    Enabled Yes

    IP Address 192.168.10.19

    TCP Dynamic Ports

    TCP Port 1435

    Tuesday, July 23, 2019 3:12 PM

Answers

All replies

  • Wednesday, July 24, 2019 4:07 AM
    Moderator
  • Hi larlap0,

    >> SS 2017 Developer cannot set port number

    Please follow below steps to configure a Server to Listen on a Specific TCP Port:

    1.using SSCM enable TCP\IP protocol open TCP\IP properties like below screenshot.

    2.delete the 0 (the value means Database Engine is listening on dynamic ports)
    3.type the port that you want to listen in IPALL like above screenshot, then click OK
    4.restart SQL server service
    5.check the port that SQL server listening in SQL server error log


    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 24, 2019 7:56 AM
  • All IP settings have a space in 'TCP Dynamic Ports' and a space in 'TCP Port', except for two IP definitions. One of these, 1433, is for the default instance and 1435 is for SS 2017. The output of the SQL Server log represents those settings.

    When I first installed SQL Server 2017, I attempted to set the TCP ports via SSCM. I made the changes but when I attempted to 'Apply' those changes, it threw an error saying something like 'WRITE ERROR'. 

    I then used Registry Editor to change settings in key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER01\MSSQLServer\SuperSocketNetLib\Tcp".

    Now SSCM does allow using the 'Apply' button when changing TCP settings without an error.

    Wednesday, July 24, 2019 1:34 PM
  • So , do you finally manage to solve the problem? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 25, 2019 4:34 AM
    Moderator
  • Sadly, no.
    Thursday, July 25, 2019 1:24 PM
  • All IP settings have a space in 'TCP Dynamic Ports' and a space in 'TCP Port', except for two IP definitions. One of these, 1433, is for the default instance and 1435 is for SS 2017. The output of the SQL Server log represents those settings.

    When I first installed SQL Server 2017, I attempted to set the TCP ports via SSCM. I made the changes but when I attempted to 'Apply' those changes, it threw an error saying something like 'WRITE ERROR'. 

    I then used Registry Editor to change settings in key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER01\MSSQLServer\SuperSocketNetLib\Tcp".

    Now SSCM does allow using the 'Apply' button when changing TCP settings without an error.

    Hi larlap0,

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 26, 2019 8:17 AM
  • I have not resolved this issue!
    Friday, July 26, 2019 7:02 PM
  • I have not resolved this issue!

    Did you go through all the steps from Uri and Cathy?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 8, 2019 8:49 PM
    Owner