Using LOCALHOST to connect to a named SQL Server 2012 Instance

Apart from working on database servers that are part of a wide area network for most of my day to day duties as a Senior Data Warehouse Architect, I like to keep  local SQL Server instance(s) on my work PC.  This allows me to spend some time working through training demos that I come across or that are part of my self-paced learning objectives to prepare for passing certification exams.

I am about a week away from writing the first of 2 certification exams for the MCSA SQL Server 2012 certification.  In preparation for that, I am working through some material from Microsoft Press.  This material comes with excellent demos and these demos need to access to a named instance of SQL Server 2012.  I installed the developer edition of SQL Server 2012 on my PC and created an appropriate named instance.

For many years, I have kept various instances of SQL Server local to my PC so I could play in a secure environment.  I got in the habit of connecting to these instances by using the LOCALHOST hostname.  This allows me to access the network services that run on the host (my own PC) by using the loopback network interface.  I could type my computer name each time but this is just easier to type LOCALHOST or even the loopback IP 127.0.0.1 .

After I created my newest named instance, I noticed that I was having connection issues when I tried to connect using the LOCALHOST as my server name.  The same was true when I would try use the loopback IP address.  I would try to connect to SQL Server SSMS and if I used LOCALHOST or 127.0.0.1 as the server name it wouldn’t not connect and give me the following error.

Example: Connect to Server error message

After some investigation, I realized that because I was trying to connect to a named instance as opposed to a default instance there were a few steps I would need to take to continue to use LOCALHOST as my server name. 

If I wanted the hostname LOCALHOST to be associated with my newly installed named instance I would need to create an alias.  This can be done in the SQL Server Configuration Manager that is provided when you install SQL Server 2012.  This can found at Start>All Programs>Microsoft SQL Server 2012>Configuration Tools.  

Once the configuration manager is opened you can go to the node that is called SQL Native Client 11.0 Configuration (32bit).  You can right click and select New Alias and enter ‘LOCALHOST’ under Alias Name and your SERVERNAME/InstanceName under Server.  You should do the same on SQL Native Client 11.0 Configuration if your PC runs a 64 bit architecture.

Example: SQL Server Configuration Manager

Example: Add a new alias

Now you will find that you can connect to the database engine by using LOCALHOST as server name and because your alias points to a named instance you don’t even need to specify the \InstanceName as part of your server name when you connect to SSMS or SSIS etc.

It is important to also note that you can connect to a named instance using a loopback address as long as you also specify your instance name ie:  127.0.0.1\InstanceName and in this case an alias isn’t necessary.

The method I have described above is not a lot of work and if you are going to be doing a lot of work on a local named instance then being able to connect by using LOCALHOST instead of SERVERNAME\InstanceName is a convenient time saver

Thanks for reading,

SQL Canuck