What are valid connection strings for SSMS login box?

By Posted on

Question :

What are valid connection strings for SSMS login box?

Every once in a while I will have to connect to an instance of SQL Server with some specific configuration setting or in a DMZ where no host names are available or I’ll have to connect to the Dedicated Admin Connection (DAC) via SSMS or SQLCMD. Because I rarely connect to a SQL Server via specific port or with the DAC, I’ll tend to forget what the exact syntax options are to get a connection up and running. I’ll then have to search the internet again for the umpteenth time to retrieve the exact connection strings and will regularly be frustrated that I can’t find the information required.

Research

I’ll read all the questions and answers previously posted on Serverfault, Stackoverflow, and DBA.

Serverfault

[1] DAC connection port SQL Server 2005 SP3
[2] Remote SQL server connection failure
[3] SQL Server 2008 R2 remote connection

Stackoverflow

[4] How to specify a port number in SQL Server connection string?

DBA

[5] DAC connection error

Microsoft

Additionally, I’ll search the Microsoft site for relevant articles:

[6] How to configure SQL Server to listen on a specific port
[7] Configure the Windows Firewall to Allow SQL Server Access
[8] Surface Area Configuration
[9] How to configure SQL server to listen on different ports on different IP addresses?
[10] Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
[11] Diagnostic Connection for Database Administrators

Brent Ozar

And Kendra Little has an article for some interesting aspects of DAC.
[12] The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It

On a side note: sometimes the DAC connection is also referred to as the ADMIN connection.

I find that in the end there is never an actual summary of simple connection strings to input into an SSMS login box, nor what to add to a SQLCMD.

This question and the following answers are intended to sum up what I found so far and to put the information into one single knowledge base article.

Actual Question

Given the following settings for a SQL Server Standard Edition:

Servername.....:    SERVERNAME
IP address.....:    123.1.2.3

1st instance (default instance):
CNAME/Alias....:    SERVERNAME-I01
Name...........:    MSSQLSERVER
Port...........:    1433 (TCP)
IP address.....:    123.1.2.3
DAC Port.......:    1434 (TCP)

2nd instance (default ip & port configuration according to [7]):
CNAME/Alias....:    SERVERNAME-I02
Name...........:    Instance2
Port...........:    dynamic (TCP)
IP address.....:    123.1.2.3
DAC port.......:    dynamic (TCP)

3rd instance (dedicated IP and port):
CNAME/Alias....:    SERVERNAME-I03
Name...........:    Instance3
Port...........:    1433 (TCP)
IP address.....:    123.1.2.4
DAC port.......:    dynamic (TCP)

Browser Service:    1434 (UDP)
Browser service:    ON

…what are valid connection strings for each instance that I can enter into the SSMS login box

  1. …with the host name?
  2. …an alias (CNAME)?
  3. …with an ip address?
  4. …for a “Dedicated Admin Connection” (DAC aka ADMIN:)?

Assumptions

  1. The server firewall is either deactivated or configured according to the Microsoft documentation mentioned in 6, 7 or 8. This is also true for any network firewall inside the LAN (including the connection to a DMZ server).
  2. The additional instances, apart from the default instance, have manually been configured to listen on different IP addresses according to 9 and in this example have been configured to listen on fixed ports 10.
  3. In some cases DAC will have been enable according to 11 and 12.

Answer :

There are various possibilities and I will try to answer them according to the numbered questions.

Valid connection strings for the SSMS login box are:

1st instance

  1. Using the host name
    • SERVERNAME
    • SERVERNAME,1433
  2. Using the CNAME/Alias
    • SERVERNAME-I01
    • SERVERNAME-I01,1433
  3. Using the IP address
    • 123.1.2.3
    • 123.1.2.3,1433

  4. For the Dedicated Admin Connection (DAC); depends on Browser Service status

    a) Browser Service ON

    • ADMIN:SERVERNAME
    • ADMIN:SERVERNAME-I01
    • ADMIN:123.1.2.3

    b) Browser Service OFF

    • SERVERNAME,1434
    • SERVERNAME-I01,1434
    • 123.1.2.3,1434

2nd instance

Because the 2nd instance has a dynamic port for the SQL Server instance and a dynamic port for DAC, the ports have to be retrieved from the ERROR log file in advance before connecting to the SQL Server. See 7 for more information. In this example I will assume port 63390 is used for the SQL Server instance and port 63389 is used for the Dedicated Admin Connection. Microsoft recommends to fix the IP port, thus allowing you to properly configure a firewall 6.

  1. Using the host name
    • SERVERNAMEInstance2 (Browser Service ON)
    • SERVERNAME,63390 (Browser Service OFF)
  2. Using the CNAMEAlias
    • SERVERNAME-I02 (Browser Service ON)
    • SERVERNAME-I02,63390 (Browser Service OFF)
  3. Using the IP address
    • 123.1.2.3,63390 (Browser Service is ON or OFF)
      Using the IP address without port, would connect you to the default instance

  4. For the Dedicated Admin Connection (DAC); really depends on Browser Service status

    a) Browser Service ON

    • ADMIN:SERVERNAMEInstance2
    • ADMIN:SERVERNAME-I02Instance2
    • ADMIN:123.1.2.3Instance2
      Always requires instance name, because the CNAME or IP address references the same IP address as the default standard instance

    b) Browser Service OFF

    • SERVERNAME,63389
    • SERVERNAME-I01,63389
    • 123.1.2.3,63389

3rd instance

  1. Using the host name

    • SERVERNAMEInstance3
      Because this instance is using the same SQL Server port as the default instance, but a different IP address, the SERVERNAME,1433 notation would not work. You would be connected to the default instance.

  2. Using the CNAME/Alias

    • SERVERNAME-I03 (Browser Service ON)
    • SERVERNAME-I03,1433 (Browser Service OFF)

  3. Using the IP address

    • 123.1.2.4
    • 123.1.2.4,1435

  4. For the Dedicated Admin Connection (DAC); depends on Browser Service status

    a) Browser Service ON

    • ADMIN:SERVERNAMEInstance3
    • ADMIN:SERVERNAME-I03 (works because the CNAME references a dedicated IP address)
    • ADMIN:123.1.2.4

    b) Browser Service OFF

    • SERVERNAME,63389 (works because the port references a dedicated port for DAC)
    • SERVERNAME-I01,63389 (works because the CNAME and port reference a dedicated instance/DAC)
    • 123.1.2.4,63389

Browser Service

If the browser service is DISABLED or has not been started, then connections cannot be routed to the relevant port. In this case the DAC port has to be retrieved from the ERRORLOG file of the instance. You should find a line with something like Dedicated admin connection support was established for listening remotely on port 63389. for the DAC port. This port number is the port the DAC will listen on. You are required to supply the relevant port for the Dedicated Admin connection and the corresponding port for the SQL Server connection if your Browser Service is turned off. You do not have to add the instance name to the connection string when using the port, because the port is reserved for the instance.

In short the Browser Service is responsible for converting instance names to ports. If the Browser Service is not running, then you have to supply the port instead of the instance name.

Reference Material

Leave a Reply

Your email address will not be published. Required fields are marked *