none
Remote connections to SQL Server 2014 Standard default instance are only available using the default port

    Question

  • Hi experts,

    I have a situation where i need to configure a static port(1118) for TCP/IP by replacing the default port 1433. When i do so SQL Server is failing to connect remotely with the following error message. 

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to xyzserver.

    ------------------------------
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=5&LinkId=20476

    ------------------------------

    Access is denied

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    But if i change the port to the default 1433, I can connect remotely.

    I am using static ports on TCP/IP protocol and named pipes is disabled(I want the client connections to go thru TCP/IP). Also the firewall is disabled

    Am i missing something. This is very weird and never had such issues in the past

    UPDATE

    However if use the port number for remote connections along with the server name, It works. But i don't want to specify the port number every time i want to make connections remotely. Also the browser service is running

    Thanks

    Bhanu




    • Edited by bhanu_nz Monday, November 28, 2016 3:39 AM
    Monday, November 28, 2016 3:13 AM

All replies

  • Hi,

      You will need to configure SQL to listen on that given port. You will need to go to SQL configuration manager, go to SQL server network configuration, and protocols for MSSQLSERVER, right click on TCP/IP and go to property, on the IP addresses tab, go down to the bottom, there is a IPAll section, change the TCP Port from 1433 to 1118. restart SQL instance then it will start to listen to that port.

    Hope this helps.

    Monday, November 28, 2016 6:58 AM
  • Hi,

    I have already done this. I can connect to the SQL Server remotely, However i have to specify the port number every time i connect to SQL Server. I know we can avoid this by running browser service. I have browser service running on the server. I may be wrong here but what i can understand is that if the analysis service is installed along with SQL server DB Engine the browser service defaults to Analysis server functionality(Possibly a bug). Correct me if am wrong. Also can some please let me know if there is any workaround for this?

    Thanks

    Monday, November 28, 2016 7:56 PM
  • I would double-check that UDP port 1434 is really open in the firewall.

    Monday, November 28, 2016 11:10 PM
  • Thanks Erland

    Firewall on the server is turned off though.

    Cheers

    Bhanu

    Tuesday, November 29, 2016 3:10 AM
  • You could create an Alias (using Configuration Manager) on the SQL Server and then use the Alias name in your connection string.

    Martin Cairney SQL Server MVP

    Tuesday, November 29, 2016 4:23 AM
  • Hi bhanu_nz,

    Based on your error message, it seems your client application(assuming it’s SSMS) is still using named pipes. I would suggest you first change the network protocol in SSMS to TCP/IP see if the issue goes away.


    As for the SQL Server Browser properties display issue, I was able to reproduce it by install SSAS and database engine separately(and SSAS goes first). However it doesn’t affect SQL Server browser functionality as I can connect to both database engine and SSAS from remote computer.

    If you have any other questions, please let me know.

    Regards,
    Lin


    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.

    Tuesday, November 29, 2016 7:21 AM
    Moderator
  • HI Bhanu,

    As Martin said, we can go with Alias name from your workstation or applications server and use alias name same as server and mention 1118 port number(in Alias setup) , so you can use same host name on server or workstation where alias created.

    Hope this helps.


    Thanks, Dev SQL DBA



    • Edited by Hanuman D Tuesday, November 29, 2016 10:32 AM
    Tuesday, November 29, 2016 10:31 AM
  • Hi Lin,

    Named Pipes on the host is disabled though. I wouldn't expect the client connections to use Named Pipes. Also tried the above mentioned suggestion and this doesn't work. Not sure how it works for you if you have non-default port for default instance of SQL Server.

    Thanks

    Bhanu 

    Tuesday, November 29, 2016 8:33 PM
  • I just made a test and changed the port for a default instance, and then I tried to connect to it by server name only. Worked perfectly.

    Since it does not work in your case, my conclusion is that the Browser service is not reachable. That is, it is either not running or there is a firewall blocking.

    Whence my advice to double-check.

    Tuesday, November 29, 2016 10:28 PM
  • Erland, are you sure you're connecting over TCP/IP and not shared memory or named pipes? 

    I tried running the browser in a console, eg

    PS C:\Program Files (x86)\Microsoft SQL Server\90\Shared> .\sqlbrowser -c

    and it didn't show any requests when connecting without an instance name.

    eg

    PS C:\Users\dbrowne> sqlcmd -S tcp:dbrownebook

    But did when connecting to a named instance (even one that doesn't exist)

    PS C:\Users\dbrowne> sqlcmd -S tcp:dbrownebook\foo

    I didn't think the client stack even queries the browser service if there is no instance name.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Wednesday, November 30, 2016 12:15 AM
  • Hi Erland, 

    As David mentioned i think you are making client connections via named pipes. Found out there are not many options to overcome this except for a couple of options as mentioned in the link below(not very straight forward though)

    https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/03/03/running-sql-server-default-instance-on-a-non-default-or-non-standard-tcp-port-tips-for-making-application-connectivity-work/

    Have tried creating alias on the SQL Server configuration manager with no luck.

    Made a decision to go with a named instance where the browser service can kick in for the client connections to resolve the port numbers. 

    Hope MSFT have something in place for future editions of SQL Server with out this added complexity

    Thanks everyone for the help and support

    Regards,

    Bhanu 



    • Edited by bhanu_nz Wednesday, November 30, 2016 7:58 PM
    Wednesday, November 30, 2016 7:56 PM
  • My bad. I only changed the port for one IP address, and I never checked the SQL Server errorlog to verify that the change had effect.

    Once I make the change properly, connection with the name only does not work.

    Wednesday, November 30, 2016 10:14 PM