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
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
- ...with the host name?
- ...an alias (CNAME)?
- ...with an ip address?
- ...for a "Dedicated Admin Connection" (DAC aka ADMIN:)?
Assumptions
- 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.