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

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:

Finding the TC/IP Port Number

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:

SQL Server Alias Configuration

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

Use the coupon code LAUNCHDAY for $10 off!

Learn React

25 Comments

Jared Watson

Just an FYI to others - If your TCP/IP protocal on SQL Server Network Configuration is not enabled, you will have to restart your SQL Server service before there is a TCP Dynamic Ports assignment to IPAll.

Nice clean and clear post by the way. Cheers.

brits

thanks, it helped :)

Thomas

Danke Marius! Sehr hilfreich. Gruß aus Nürnberg

EL

Thanks a lot! it helped me and saved lot of time

Pawel

Thanks! Very helpful

Sam Bendayan

Thanks, this was helpful for some of my servers but not all. What happens if you are not using Dynamic TCP ports?

Dicky

Thanks for the tip. I have the same situation where everyone in my dev team install SQL Server with different instance name.

BigPhil

Excellent, worked perfectly, thanks for writing this out.

Mhairi Mcclair

Thanks, this worked for me in similar circumstances.

Marie

You saved my sanity! Many thanks for this helpful post.

Steven K. Mariner

Nice demonstration of using the GUI to accomplish this for one server. What are the commands I'd use to script out six thousand of them?

Nick

Exactly what I needed, thanks!

Note to any future travelers who may find the TCP/IP Protocol disabled and can't figure out why the yes/no dropdown menu in the properties dialog doesn't allow you to select 'yes': You have to right-click the TCP/IP protocol row, THEN click 'Enable'. GAAAH!

Source: https://habaneroconsulting.com/insights/tcp-ip-is-disabled-default-in-microsoft-sql-server-2014#.VpVk3RUrKUk

jorgen

What happens when you're not using dynamic ports ? => you're probably using 1433 as port number and this will work even after server(SQL?) reboot. with dynamic port you can get another port number after reboot and your alias will be forwarding to the old and now wrong port number.

My advice : always use a static port, quick win, change the static port to the dynamic port number so you don't have to reboot (and not using the default port number everybody knows). how ? in the exaple above in the ipall remove the 2807 from "TCP dynamic ports" and put it in the "TCP port" field

Jahmal Lewis

Thank you SOO much. Even my DBA told me there wasn't any hope for this. HA!

Jagannath

As the port configured for SQL Server is Dynamic, it's very likely that the next time SQL Server restarts your port would different than it was. As you have configured the port number in the Alias you won't be able to connect. My suggestion would be to configure the SQL Server to listen on static port and then the Alias should work all the time.

Alexander Williamson

Is the solution to this a manual step? Can we have an automated build step please (so it can be built into a setup script)?

Also if you're using aliases in your normal setup, you're probably doing something wrong - and that something is probably your deployment scripts. Deployment scripts should setup your app from scratch, including setting your connection strings. Your .gitignore file should exclude your configs (because they should be generated from a template) so you won't have the checkin issues where you get incorrect values.

It's much easier to use actual values for keys rather than aliases which could get set by an external process or user at any time.

Devin

Thanks for leaving this article to help those of us that come after you. This was invaluable!