Sifiso W. Ndlovu

SQL Server convenience names

February 19, 2016 by

Introduction

A while ago I discovered that you can conveniently connect to a local instance of SQL Server by typing one of the following names:

  1. Dot (.),
  2. (local),
  3. localhost, or
  4. 127.0.0.1.

Figure 1 illustrates how a successful SQL Server connection using the aforementioned names would look like in SQL Server Management Studio (SSMS):


Figure 1: Connected to SSMS Using Convenient Names

Since the “discovery”, I had never seen any Microsoft-issued documentation that properly describes such a method of connecting to SQL Server, thus, I was personally referring to such practice as “connecting to SQL Server using shortcut keys”. However, it was only when I was researching for my article, SQL Server Lessons from a TFS Installation, that I came across the term “Convenience Names” as a formal reference by Microsoft (or at least from a Microsoft product) to “connecting to SQL Server using shortcut keys”. Figure 2 displays a TFS 2015 Readiness Check results in which the highlighted error relates to a prohibition for using SQL Server convenience names during a TFS Configuration.

https://www.sqlshack.com/wp-content/uploads/2016/01/word-image16.png

Figure 2: TFS 2015 Readiness Check Results

This article sets out to provide an in depth analysis of SQL Server convenience names by focusing on its usage, its architecture, and providing guidance on when is most appropriate to make use of convenience names in SQL Server.

A convenient analogy

For me SQL Server convenience names are analogous to dialling a business telephone extension. For instance, as a South African based professional, when I have enquires relating to my payslip, instead of dialling the complete set of telephone numbers i.e. 011 345 9804, I can just dial the last 4 digits which is the extension to the Human Resources (HR) office of the organisation I work for. However, I can only get away with dialling the extension number only when I am in the premises of my employer. If I were to dial the last 4 digits of the aforementioned number from my house telephone, I will not reach the HR office as the number will become unrecognisable. Similarl to the telephone extension, if you attempt to connect to a remote SQL Server instance by typing a dot(.) – SQL Server will not recognise such a server address and you will run into the error message depicted in Figure 3.


Figure 3: SQL Server connection error

Internals of SQL Server convenience names

1. SQL Server Network Configurations

A significant part of the discussion around the topic of SQL Server convenience names pertains to a discussion surrounding SQL Server Network Configurations. This is because part of making SQL Server convenience names work, involves a proper configuration of SQL Server TCP IP ports. Figure 4 provides a listing via netstat command of network connectivity and indicates that Process ID (PID) 1804 is listening on port 1433.


Figure 4: Windows netstat command output

When we go through the list of running services via Windows Task Manager, we can easily identify PID 1804 as belonging to the SQL Server default instance (MSSQLSERVER). Thus, one of the checks you can do to resolve the error depicted in Figure 3, is to run netstat commands to verify whether or not the instance you are trying to connect to has been configured for TCP connections.


Figure 5: Windows Task Manager

2. The Connection Process

Figure 6 depicts the sequence of events involved when connecting to SQL Server using convenience names. At the very beginning, a user uses a SQL Server enabled client tools such as SQL Server Management Studio (SSMS) or Visual Studio to establish a connection to a SQL Server instance. Within a client tool, a convenience name is provided as part of the connection string for a either a default instance or named instance. Usually, the default instance is linked to port 443 whilst ports for named instances are often dynamic.

C:\Users\sinndlovu\Downloads\Capture.jpg

Figure 6: List of events triggered during a connection using convenience names

In order for us to illustrate the activities depicted in Figure 6, I have setup an Oracle Virtual Box VM named WIN-7. The VM has Windows 7 and SQL Server 2012 installed. SQL Server 2012 has two named instances, namely, WIN7-PC\SIFISOTEST123 and WIN7-PC\SIFISOTESTINSTAN. Figure 7 and 8 shows the TCP IP All properties for the two named instances. You can already see that although the default instance is not installed – port 1433 – is not readily assigned to any of the two instances, instead dynamic port numbers are allocated.


Figure 7: TCP IP All Properties – SIFISOTEST123


Figure 8: TCP IP All Properties – SIFISOTESTINSTAN

It is no surprise, therefore, that when we connect using a dot(.) a failure message is returned as no instance is linked to port 1433.


Figure 9: SQL Server Convenience Name Connection Error

Another way to confirm this is that when we run netstat command, there is no PID listening on port 1433, instead only the ports associated with named instances are configured.


Figure 10: Netstat command showing the missing port 1433

2.1. Connect to Default Instance Using SQL Server Convenience Names

I later added a default instance to my VM and the default TCP IP all property is correctly configured to listen on port 1433 as shown in Figure 11.


Figure 11: TCP IP All Properties – MSSQLSERVER

Now, if we try connecting using the dot(.) again, we successfully establish connection as shown in Figure 12.


Figure 12: Connecting to default instance using convenience name

2.2. Connect to Named Instance Using SQL Server Convenience Names

So what if you don’t have a default instance and you want to connect to named instances using convenience names? The simplest way is to remove the named instance’s dynamic TCP port and set its TCP port property to 1433. In order for the changes to be effective, you will be required to restart the SQL Server the windows service linked to the named instance. Figure 13 indicates the updated TCP settings of instance SIFISOTEST123.


Figure 13: Updated TCP settings of instance SIFISOTEST123

As it can be seen in Figure 14 – we are now able to connect using the dot(.) and we can confirm this because an application event has been logged against instance WIN7-PC\SIFISOTEST123 (such an event is only logged upon successful connection).


Figure 14: Connecting to named instance using convenience name

Conclusion

In this article we have covered the internals and workings of SQL Server Convenience Names. Connecting to SQL Server using convenience names is an efficient and quick way to establish a connection to a SQL Server instance. It usually connects easily against a default instance – if configured but alterations can be done on the TCP IP All property to connect conveniently against named instances too. However, similarly to the business telephone extension, there are limitations to the usage of convenience names such as not being able to use them whilst connecting to a remote SQL Server.

Reference


Sifiso W. Ndlovu

Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management.

He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

He is currently under the employment of Karabina Solutions

View all posts by Sifiso W. Ndlovu
Sifiso W. Ndlovu
SQL Database design

About Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg. He is currently under the employment of Karabina Solutions View all posts by Sifiso W. Ndlovu

4 comments
Joshua Guttman
Joshua Guttman

The first two have been there since 1992 and before as Sybase. The last two are just properties of tcp/ip.

Sifiso W. Ndlovu
Sifiso W. Ndlovu

@Joshua Guttman thanks for feedback. I skipped a network course in my school days and so I don't know much about networking. If the last two are the properties of tcp/ip - do you think there are additional properties that could allow for convenient connection against remote instance (i.e. connect to SQL by typing "remotehost" and have that auto-resolved to a remote SQL Server instance)?

Ivan
Ivan

What would be the case on prod environments, where you would use convenience Names instead of the proper connection string? I mean this is only working for local connections and if you are a production dba, local connection are the last thing you should do.

PS: Can you post some documentation on usage of port 443 mentioned in your Figure 6?

Sifiso W. Ndlovu
Sifiso W. Ndlovu

@Ivan Thanks for the feedback Ivan. Yes, convenience names are only used within local connections and they should never be used in a production environment. Honestly, it's a lazy way of establishing a SQL Server connection and I suspect that's why there isn't much written officially from Microsoft Library sites i.e. MSDN, TechNet etc. As for documentation on port 443 - so much has been written about it. Remember it only becomes a default port for SQL Server but generally they are applications that can still be configured to listen on it.


General overview of what port 443 entails: http://www.auditmypc.com/tcp-port-443.asp


Specific overview of port 443 as it refers to usage by SQL Server: http://sqlmag.com/sql-server/sql-server-tcp-and-udp-portshttps://msdn.microsoft.com/en-us/library/cc646023.aspx