none
Why can't I enable a static TCP port on SQL Server Express 2012?

    Question

  • I have installed SQL Server 2012 Express on Windows 7 Home Premium 64 bit as a named instance. I have used the Configuration Manager to specify TCP port 1433 and disable dynamic ports (field set to a blank string). However, SQL Server always listens on dynamic port 50355 (as reported by TCPView).

    Even after restarting the service, Configuration Manager indicates that I should be using static port 1433.

    I have installed SQL Server 2008 on WinXP in the past in exactly the same way with no issues. Is there something different with 2012?

    Monday, August 06, 2012 12:55 PM

Answers

  • Hi Alberto,

    Yes - I have solved the problem but my previous explanation might not have been clear.

    The linked MSDN article described a procedure which WORKS with MSSQL 2008 but NOT with my installation of MSSQL 2012, that is :-

    The following settings do not work with MSSQL2008, but WORK with MSSQL 2012. Note that the individual IP address settings are ignored. SQL Server now listens on ALL IP addresses on port 1433.


    • Edited by MikeJ-UK Tuesday, August 07, 2012 12:42 PM
    • Marked as answer by MikeJ-UK Tuesday, August 07, 2012 12:43 PM
    Tuesday, August 07, 2012 12:42 PM

All replies

  • Hello,

    Please review the steps to set a specific TCP port.

    http://msdn.microsoft.com/en-us/library/ms177440.aspx

    Make sure Windows Firewall (or any other firewall) is configured to allow SQL Server access.

    http://msdn.microsoft.com/en-us/library/cc646023.aspx

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Monday, August 06, 2012 2:12 PM
  • Thanks for the reply Alberto. Unfortunately I have gone through all the usual steps to do this the usual way with no luck. The firewall is not the issue - I can connect to the dynamic port locally with no problem, however this won't help remotely as I can't enable a variable dynamic port through the firewall!
    Monday, August 06, 2012 2:21 PM
  • What is the exact error message you are getting wehn trying to connect remotely ?

    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    My Blog
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Monday, August 06, 2012 7:58 PM
  • I am not getting any error message and at this stage I am still trying to connect locally. I have installed SQL Server 2012 Express on Win7/64 as a named instance. I have used Management Studio to allow remote connections. I have used Configuration Manager to enable TCP protocol, enabled my network and loopback IP addresses and set their ports to 1433 and cleared the dynamic ports field. After restarting the server, I cannot connect locally on port 1433.

    TCPView reports that SQL Server is using a dynamic TCP port (currently 50355) and I have confirmed this by connecting to this dynamic port using Management Studio locally. The only error message I get is when I try to connect to the default port (1433) using Management Studio which (unsurprisingly) reports that the server cannot be found.

    I have just reproduced the same problem on a second machine with the same setup.

    Monday, August 06, 2012 9:04 PM
  • I have now solved the problem. The MSDN documentation Albert refers to in his answer states that in Configuration Manager, under Network Configuration|Protocols|TCP/IP|IP Addresses, you should change the properties for each required IP address separately. These individual IPn settings seem to be completely ignored. Instead, it is the IPAll settings alone that determine the server operation.

    Specifying a static TCP port for any or all of the individual addresses has no effect. The required port must be configured under IPAll and dynamic ports disabled there also.

    Edit ...

    My point is that  the MSDN article about enabling TCP ports is OK for SQL Server 2008 but not for SQL Server 2012

    • Edited by MikeJ-UK Tuesday, August 07, 2012 7:41 AM
    Monday, August 06, 2012 10:48 PM
  • Hello,

    I am glad for you. I think that article covers all possible issues. I had no idea how to help you further.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Monday, August 06, 2012 10:56 PM
  • Thanks for replying Alberto, but I'm sorry I had to unmark your answer! The linked article does not work for SQL Server 2012 Express.
    Tuesday, August 07, 2012 8:40 AM
  • Hello,

    No problem.

    Any progress?

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, August 07, 2012 11:14 AM
  • Hi Alberto,

    Yes - I have solved the problem but my previous explanation might not have been clear.

    The linked MSDN article described a procedure which WORKS with MSSQL 2008 but NOT with my installation of MSSQL 2012, that is :-

    The following settings do not work with MSSQL2008, but WORK with MSSQL 2012. Note that the individual IP address settings are ignored. SQL Server now listens on ALL IP addresses on port 1433.


    • Edited by MikeJ-UK Tuesday, August 07, 2012 12:42 PM
    • Marked as answer by MikeJ-UK Tuesday, August 07, 2012 12:43 PM
    Tuesday, August 07, 2012 12:42 PM