none
Can't connect to named instance on FCI without specifying port RRS feed

  • Question

  • Hey all,

    I'm struggling with an odd problem on my new SQL cluster and wondered what I am missing. Briefly, I have deployed a 2 node cluster with two instances, one default and one named, on separate cluster network names. NB: As this is a new deployment we are using SQL 2019, although even though it's very new, I don't think the version a factor.

      I have servers SQL01 & SQL02, running Server 2019 Core.
      Virtual network names SQLCL01 & SQLCL02
      SQLCL01 has a default instance on it
      SQLCL02 has a named instance called Instance2

    I can connect to the default instance all day long no problems, but the named instance is giving me some jip. From the SQL server connect dialog, I can browse from a remote computer and see the instance in the list. If I connect from our old SQL 2014 server (Management Studio 12), it connects immediately no problems whatsoever.

    When I try to connect from a new server I have spun up (on the same subnet), or my desktop machine (different subnet) with the latest Management Studio (18 or 17) on it will not connect unless I specify the port number. This happens regardless of using dynamic ports or setting the port to 1433 for the named instance.

    Things I have tried / ruled out
      The Server firewalls have been disabled during testing
      There are no firewalls or ACLs blocking ports between SSMS & the cluster
      I have forced the port to 1433 for the named instance and also used dynamic ports with the same results
      DNS is resolving to the right place
      Verified that the SQL browser service is running
      Verified port 1433 TCP (or the dynamic port) & 1434 UDP is open with Nmmap


    I can still only connect when specifying the port. I don't want to do this because I want to keep the setup of any client apps as standard as possible so as not to confuse the helpdesk. What is really doing my nut is that SQL 2014 / SSMS 12 is going straight in, no messing.

    Does anyone have any clues for me? I am tearing the remainder of my hair out!

    Cheers,

    Mark
    Monday, November 18, 2019 10:38 AM

All replies

  • Make sure that TCP/IP protocol is enabled and right click on TCP/IP and select the Properties option. In the TCP/IP Properties dialog select the IP Addresses tab and scroll down to IPAII. If the TCP Dynamic Ports dialog box contains 0, which indicates that the Database Engine is listening on dynamic ports, delete the 0 and set the TCP Dynamic Ports to blank and TCP Port to 1433.

    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

    Monday, November 18, 2019 11:32 AM
  • Thanks Uri,

    I have tested this and still need to specify the port i.e. sqlcl02,1433\Instance2 when connecting!

    I have tried from a machine on the same subnet/VLAN to ensure there are no firewall issues. The older SQL Server Management Studio 12 connects fine. SSMS 17/18 seem to have trouble.

    It's very strange, do you have any other ideas?


    Monday, November 18, 2019 11:44 AM
  • Are you sure that UDP 1434  is opened?

    Can you use an ALIAS?

    https://blogs.msdn.microsoft.com/dbrowne/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance/


    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

    Monday, November 18, 2019 11:50 AM
  • Hi fiddley,

     

    >>When I try to connect from a new server I have spun up (on the same subnet), or my desktop machine (different subnet) with the latest Management Studio (18 or 17) on it will not connect unless I specify the port number. This happens regardless of using dynamic ports or setting the port to 1433 for the named instance.

     

    Would you please show us the error message when you cannot connect to the sql server without specifying the port number?  According to your current description, my guess may be related to spn. You can use the error log to see if the sql server successfully registered two spn. One is with a port number, and the other is without a port number.

     

    Or you can verify your spn problem with kerberos configuration manager. Please refer to https://blog.pythian.com/using-kerberos-configuration-manager-to-resolve-microsoft-sql-server-spn-issues/ This is temporarily a possible reason I can think of because I didn't see your error message.

     

    Best regards,

    Dedmon Dai


    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 19, 2019 9:21 AM
  • Many thanks both for your suggestions. I have been reading how to properly implement an Alias as I haven't used one before, and I will investigate the SPN setup today.

    I will report back with my findings later today.

    Wednesday, November 20, 2019 10:21 AM
  • Looking forward to hearing from you.

    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

    Wednesday, November 20, 2019 10:23 AM
  • OK, Partial success! I can confirm that setting an alias gives us a workaround.

    Create A DNS entry for SQLI-02
    Set the listening port to 1433
    Add SPNs to the service account with the commands: 
    setspn -A MSSQLSvc/SQLI-02.domain.org.uk:1433 domain\SQLDBSvc
    setspn -A MSSQLSvc/SQLI-02.domain.org.uk domain\SQLDBSvc

    So, we have a fallback position if needed! Thanks Uri!

    I would like to continue exploring the issue though as adding an alias adds extra complexity to the setup, so I have investigated the SPN situation.

    So, Dedmon, in answer to your questions:

    Here is the error message we are receiving (although it is a red herring):

    TITLE: Connect to Server
    ------------------------------
    Cannot connect to SQLCL-02\INSTANCE2.
    ------------------------------
    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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
    ------------------------------
    BUTTONS:
    OK
    ------------------------------

    I am fairly certain that the SPNs are registered correctly. The following entries were found in the log:

    Date,Source,Severity,Message
    11/19/2019 10:25:17,Server,Unknown,The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQLCL-02.domain.org.uk:1433 ] for the SQL Server service.
    11/19/2019 10:25:17,Server,Unknown,The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQLCL-02.domain.org.uk:INSTANCE2 ] for the SQL Server service.
    11/19/2019 10:25:17,Server,Unknown,SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

    Also I confirmed by using setspn:

    C:\WINDOWS>setspn -L SQLDBSvc
    Registered ServicePrincipalNames for CN=SQL Database Service,OU=SQL Cluster,OU=Servers,DC=domain,DC=org,DC=uk:
    MSSQLSvc/SQLCL-02.domain.org.uk:1433
    MSSQLSvc/SQLCL-02.domain.org.uk:INSTANCE2
    MSSQLSvc/SQLCL-02.domain.org.uk:53898

    (I don't see the SPNs for the default instance, even though they appeared to be succesfully registered as per the error log)

    Given the SPNs are all in order, do you have any ideas of what to check next?
    Wednesday, November 20, 2019 2:40 PM
  • Hi Mark,

    Let's say the port of named instance is not 1433, are you able to connect from the passive node using the virtualname\instance?

    The issue only happens when the clients are other machine in the subnet?

    If that's the case, I suggest to run network monitor on both sql active node and the client.

    Other test you need to do is to stop the sql browser.

    Run it in command prompte, see if it's able to receive the request from other clients

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


    https://sqlserver.code.blog/

    Tuesday, November 26, 2019 12:42 AM