SQL Server convenience names

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:

Dot (.),
(local),
localhost, or
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.

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.

See more

Consider these free tools for SQL Server that improve database developer productivity.

Sifiso Ndlovu

Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. 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.

Sifiso's LinkedIn profile

View all posts by Sifiso W. Ndlovu