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.
I'll read all the questions and answers previously posted on Serverfault, Stackoverflow, and DBA.
Additionally, I'll search the Microsoft site for relevant articles:
 How to configure SQL Server to listen on a specific port
 Configure the Windows Firewall to Allow SQL Server Access
 Surface Area Configuration
 How to configure SQL server to listen on different ports on different IP addresses?
 Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
 Diagnostic Connection for Database Administrators
And Kendra Little has an article for some interesting aspects of DAC.
 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.
Given the following settings for a SQL Server Standard Edition:
Servername.....: SERVERNAME IP address.....: 220.127.116.11 1st instance (default instance): CNAME/Alias....: SERVERNAME-I01 Name...........: MSSQLSERVER Port...........: 1433 (TCP) IP address.....: 18.104.22.168 DAC Port.......: 1434 (TCP) 2nd instance (default ip & port configuration according to ): CNAME/Alias....: SERVERNAME-I02 Name...........: Instance2 Port...........: dynamic (TCP) IP address.....: 22.214.171.124 DAC port.......: dynamic (TCP) 3rd instance (dedicated IP and port): CNAME/Alias....: SERVERNAME-I03 Name...........: Instance3 Port...........: 1433 (TCP) IP address.....: 126.96.36.199 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
- ...with the host name?
- ...an alias (CNAME)?
- ...with an ip address?
- ...for a "Dedicated Admin Connection" (DAC aka ADMIN:)?
- 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).
- 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.
- In some cases DAC will have been enable according to 11 and 12.