How to Configure a SQL Server Alias for a Named Instance on a Development Machine

February 9, 2014 #sqlserver

There are plenty of tutorials out there that explain how to configure an MS SQL Server alias. However, since none of them worked for me, I wrote this post so I'll be able to look it up in the future. Here's what finally got it working for me.

My Use Case

In my development team at work, some of our local database instances have different names. Manually adapting the connection string to my current local development machine every single time is not an option for me because it's error-prone (changes might get checked into version control) and outright annoying.

The connection string we're using is defined in our Web.config like this:

<add name="SqlServer" connectionString="server=(local)\FooBarSqlServer; …"
    providerName="System.Data.SqlClient" />

This is the perfect use case for an alias. Basically, an alias maps an arbitrary database name to an actual database server. So I created an alias for FooBarSqlServer, which allows me to use the above (unchanged) connection string to connect to my local (differently named) SQL Server instance. That was when I ran into the trouble motivating me to write this post. The alias simply didn't work: I couldn't use it to connect to the database, neither in our application nor using SQL Server Management Studio.

The Working Solution

I googled around quite a bit and finally found the solution in Microsoft's How to connect to SQL Server by using an earlier version of SQL Server: The section Configure a server alias to use TCP/IP sockets pointed out that I had to look up the specific port number used by the TCP/IP protocol:

Here's how you find the port number that's being used by TCP/IP on your machine:

1. Open the SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration and select Protocols for <INSTANCE_NAME>.
3. Double-click on TCP/IP and make sure Enabled is set to Yes.
4. Remember whether Listen All is set to Yes or No and switch to the IP Addresses tab.

• Now, if Listen All was set to Yes (which it was for me), scroll down to the IPAll section at the very bottom of the window and find the value that's displayed for TCP Dynamic Ports.
• If Listen All was set to No, locate the value of TCP Dynamic Ports for the specific IP address you're looking for.

You'll have to copy this port number into the Port No field when you're configuring your alias:

Note that you'll have to set the Alias Name to the exact value used in your connection string. Also, if you're not using the default SQL Server instance on your development machine (which I am), you'll need to specify its name in the Server field in addition to the server name. In my case, that would be something like MARIUS\NAMED_SQL_INSTANCE. Remember to also define the alias for 32-bit clients when your database has both 64-bit and 32-bit clients.

Hope this helped you,

Marius

© 2011 – 2015 Marius Schulz