none
Different behavior on similar servers when omitting -S from sqlcmd RRS feed

  • Question

  • We use all named instances in our shop.  I was concerned about having to figure out a way to determine each server's instance name so that I could run the sqlcmd, but I discovered that a command like this worked:

    sqlcmd -E -Q "select @@version"

    Then I discovered that while this worked on 90% of our servers, there are a few that this doesn't work on.  They happen to be SQL 2012 SP4, but I have many others on this same version that work.  The error I get is:

    Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
    Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
    Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

    I had to use this syntax instead to get it working:

    sqlcmd -E -Q "select @@version" -S <server>\<instance>

    I looked at the MS docs for sqlcmd to understand what not having that flag does and it states "If no server computer is specified, sqlcmd connects to the default instance of SQL Server on the local computer." and "If you do not specify a server_name [ \ ] when you start sqlcmd, SQL Server checks for and uses the SQLCMDSERVER environment variable."

    I'm not sure why these few servers aren't working the same as the rest of them.  I would say it's got to be a configuration problem somewhere, but of all the things I've checked I can't seem to find the magic setting that's different.

    Some things I've already checked/verified on the server that's not working:

    1.  I'm connecting locally so no remote connection involved.

    2.  SQL Server, SQL Agent and SQL Browser services are all up and running.

    3.  Even though I'm connecting locally, I verified that "Allow remote connections to this server" is enabled.

    4.  On both working and non-working systems, Shared Memory and TCP/IP are enabled, Named Pipes is disabled.

    5.  Server authentication is set to "SQL Server and Windows Authentication mode"

    6.  Just for kicks, I had the sysadmin verify that UDP 1434 was open, just in case that was involved somehow in determining the "default instance".


    • Edited by dbagirl78 Wednesday, November 13, 2019 1:23 PM Added version information for failing servers
    Wednesday, November 13, 2019 1:16 PM

Answers

  • You are misunderstanding the concept of default instance, port numbers and role of the SQL Server Browser Service (1434).

    A default instance listens by default to port 1433.

    A named instance listens to some other port.

    SQL Server browser is there so it can convert your instance name to the port number that the named instance is listening to.

    If you try to use only the server name when you connect, it will try port 1433. SQL server browser is not involved at all!

    So you have a few options:

    Specify the instance when you connect /S servername\instancename.

    Use the environment variable to "redirect" what it means when you don't specify the /S param.

    Change the named instance so it listens to 1433.

    Use a client alias on the client machine that maps the machine name to your machine\instance name.

    Those are the ones that comes to me directly. It is up to you to decide which you like better.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Tom Phillips Wednesday, November 13, 2019 6:44 PM
    • Marked as answer by dbagirl78 Wednesday, November 13, 2019 7:35 PM
    Wednesday, November 13, 2019 6:14 PM
    Moderator

All replies

  • You are misunderstanding the concept of default instance, port numbers and role of the SQL Server Browser Service (1434).

    A default instance listens by default to port 1433.

    A named instance listens to some other port.

    SQL Server browser is there so it can convert your instance name to the port number that the named instance is listening to.

    If you try to use only the server name when you connect, it will try port 1433. SQL server browser is not involved at all!

    So you have a few options:

    Specify the instance when you connect /S servername\instancename.

    Use the environment variable to "redirect" what it means when you don't specify the /S param.

    Change the named instance so it listens to 1433.

    Use a client alias on the client machine that maps the machine name to your machine\instance name.

    Those are the ones that comes to me directly. It is up to you to decide which you like better.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Tom Phillips Wednesday, November 13, 2019 6:44 PM
    • Marked as answer by dbagirl78 Wednesday, November 13, 2019 7:35 PM
    Wednesday, November 13, 2019 6:14 PM
    Moderator
  • Ah yes!  That explains it.  Thank you very much for your reply Tibor.  We have typically changed all the named instances back to 1433, but I forgot that we had a few outliers that were still using the dynamic ports assigned by SQL during installation.
    Wednesday, November 13, 2019 7:35 PM