How to Add a Hostname Alias for a SQL Server Instance

A Hostname Alias SQL Server instance is a simple Hostname that allows clients to connect to an instance of SQL Server without specifying an instance name or a port number. They are very useful for upgrades, server migration, consolidation, and disaster recovery.  A network alias can redirect clients from one server to another. This is most useful for named instances because a Hostname Alias can make a named instance look just like a default instance. But it’s also useful for moving around default instances or consolidating multiple default instances onto a single server.

With a properly configured Hostname Alias clients can be configured to connect just to the host name, with no port or instance name. DNS will resolve the IP address. The SQL Client will default to connecting on TCP/IP port 1433, and will try to authenticate as if there was a default instance with that name listening on that port. A Hostname Alias is superior to a “Client Alias” as you don’t have to touch every client, and you can control the resolution on the server side.

There are four easy steps to creating a Hostname Alias, but they are poorly documented and often misunderstood. All you have to do is:

1) Create a DNS A Record pointing to the IP address of the target server

2) Configure SQL Server to listen on port 1433 on that IP address

3) Add SPNs to enable the SQL Server Service account to use Kerberos Authentication (optional if you don't use Kerberos).

4) Add BackConnectionHostNames entries to enable NTLM Authentication

The first step in creating a Network Alias is to create a Host Name that resolves to the IP address of your target SQL Server. This can be done in the host file of all the client machines or in DNS by creating a new DNS A Record.

To do this in DNS, open the DNS Manager and create a new A Record with the same IP address as the target server. Here I’m adding an alias NewHost that points to the IP address of the server. In production this step needs to be performed by a domain administrator, but I’m using an isolated with two VMs (under Hyper-V on Windows 8).

Now you should be able to resolve the name:

The next step is to configure SQL Server to listen on port 1433 on the target IP address. Note that the IP address could be a new IP address you are adding to the box or an existing IP address. Here I have a named instance called SQL2012, and have disabled dynamic ports and enabled listening on port 1433:

 

TCP Dynamic Ports should be blank, and enter 1433 in the TCP Port for IPAll.  Only one non-clustered instance can listen on port 1433 for IPAll.  A Failover Cluster Instance can always listen on port 1433 because each Failover Cluster Instance has at least one dedicated IP address.  For a Failover Cluster Instance “IPAll” doesn't mean all the IP addresses of the server.  It really means “All the IP Addresses that are dependencies of the main Network Name”.   So multiple Failover Cluster Instances can be configured to listen on port 1433 on IPAll without conflicting.

If you have other SQL instances on this server (and it’s not a Failover Cluster Instance), you’ll need to specify which instances listen on which IP addresses. Then you would disable IP Listen All, and configure the instance to listen on port 1433 on a specific IP address:

 

Then restart the instance and verify that SQL Server is listening on the IP address in the log file:

 

 

Now you should be able to connect using SQL Auth (if enabled), but Windows Auth may fail, because the we haven’t registered the SPNs to enable this server to run SQL Server with that network alias:

 

So now we add the SPNs. When a client connects to SQL Server it constructs an SPN from the connection string data. If you just connect using a Host Name with no port or instance the SPN will look like “MSSQLSvc/HostName” or “MSSQLSvc/HostName:1433”. Since Kerberos is a mutual authentication protocol, the server has to authenticate to the client that it really is the intended target server. It does this by registering the SPNs in Active Directory under the service account. Essentially an SPN is a declaration that a particular security principal is permitted to run a particular service. We’re using a Virtual Account for SQL Server (the new default in 2012), so the service authenticates on the network as the server’s machine account. Network Service also uses the machine account. So if you’re using a virtual account or Network service, register the SPNs for the machine account. Otherwise you must use a domain account as the SQL Service account and register the SPNs for the domain account.

So we need to register a SPN that declares that our server “dbrowne\sps$” is permitted to run the SQL instance at the new network alias “NewHost”. So the commands to add the SPNs are

setspn –A MSSQLSvc/NewHost.dbrowne.lab dbrowne\sps$

setspn –A MSSQLSvc/NewHost.dbrowne.lab:1433 dbrowne\sps$

I know these are the right SPNs because if you look in the log file of a default instance, you will see it registers these two SPNs. This step will also need to be performed by a domain administrator.

Notice that after adding the SPNs I had to issue a “klist purge” to get this working. You might also need to bounce SQL Server or perhaps get some coffee. I’ve never really been able to pin that down.

 

You can verify that you have a Kerberos connection with the following query:

select auth_scheme from sys.dm_exec_connections where session_id = @@spid

 

Now Kerberos is working. But note that we weren’t able to connect _at all_ until we got Kerberos working. That means NTLM wasn’t working either. It’s nice to get NTLM working too, so you can connect locally using the alias even if you can’t reach the domain controller. The reason NTLM isn’t working and how to fix it is here:

Error message when you try to access a server locally by using its FQDN or its CNAME alias . . ..

https://support.microsoft.com/kb/926642

So in our case we need to add “BackConnectionHostNames” for “NewHost” and “NewHost.dbrowne.lab”. You can do this in PowerShell like this:

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa\MSV1_0 -Name "BackConnectionHostNames" -Value "NewHost","NewHost.dbrowne.lab" -PropertyType multistring

Or in regedit as described in the KB article. If you are on a Failover Cluster you need to make this registry change to every cluster node that can run the instance. For a Failover Cluster Instance, this is typically all the nodes. For a standalone instance installed on a Failover Cluster Node (ie for AlwaysOn Availability Groups), you just need to make this change to one node.

Then Reboot, and test that NTLM works:

 

Note that with NTLM working you won’t be able to get a Kerberos connection from the local server.

So that’s how to create a Network Alias for a SQL Server instance. This works for both standalone default instances and named instances, running on a cluster node or a standalone server. And it works on Failover Cluster Instances too, but the procedure is a little different.

Comments

• Anonymous
  May 21, 2012
  Thanks for this useful article which is very well documented by numerous  screenshots which are easy to read ( even by the tired eyes of an old man.

• Anonymous
  May 28, 2012
  But what happen in a clustered instance? And if the instance can't listen on port 1433? The article is great, but I think is useful in a very specific scenario...

• Anonymous
  May 31, 2012
  A Failover Cluster Instance can always listen on port 1433 for "IPAll" because each Failover Cluster Instance has at least one dedicated IP address, and will only ever listen on its dedicated IP addresses.

• Anonymous
  June 04, 2012
  Ok, you're right But I still think that the main problem is the requirement to listen on port 1433. It's not usual find a SQL instance listen on that port in a production environment.

• Anonymous
  June 12, 2012
  You can configure the instances to listen on whatever port you want.  If all the instances in your environment listen on some non-default port, then you can still accomplish the same redirection.  And behind a firewall, many customers do leave SQL Server on the default port.

• Anonymous
  July 21, 2012
  I'm trying to do this exact thing, however I'm running into a problem.  The additional IP address isn't showing up in configuration manager under any of my instances.  Is there a way to add it?  I've found one forum that mentions editing the registry but I would rather not do that if I can help it. Thanks Kenneth Fisher

• Anonymous
  July 25, 2012
  @Kenneth  That list is not refreshed.  You will typically have a bunch of unused entries in that list, and you can just edit one of those to point to your new IP address.

• Anonymous
  July 27, 2012
  The comment has been removed

• Anonymous
  September 20, 2012
  Thanks, very useful information.

• Anonymous
  October 25, 2012
  The comment has been removed

• Anonymous
  January 15, 2013
  For named instances you would have to move them to an identically named instance on the new server, or reconfigure the clients.

• Anonymous
  March 04, 2013
  Does this work?

• Anonymous
  May 30, 2013
  The comment has been removed

• Anonymous
  August 20, 2013
  @piers7 Yes.  After setting this up you can refer to the instance by either its real name or the alias.

• Anonymous
  January 08, 2014
  in a clustered environment, which steps do I need to perform on the other nodes in the cluster?

• Anonymous
  August 17, 2014
  Great explanation it's working finally in my environment. facebook.com/sharepointsupport

• Anonymous
  October 15, 2014
  Its a great articles to read, it worked fine to me, Never do a copy past :)

• Anonymous
  February 10, 2015
  The comment has been removed

• Anonymous
  May 13, 2015
  Excellent

• Anonymous
  June 11, 2015
  I am trying this out with Windows 2012r2 and SQL Server 2014 (RTM) release. will it work for this combination. I am looking at server consolidation and need to do this.

• Anonymous
  October 28, 2015
  The comment has been removed

• Anonymous
  January 31, 2016
  Is this officially supported by Microsoft, no restrictions of any kind?