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).

image

Now you should be able to resolve the name:

image

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:

image

 

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:

image

image

 

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

image

 

 

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:

image

 

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.

image

 

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

image

 

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 . . ..

http://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:

image

 

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 (22)

  1. Papy Normand says:

    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.

  2. 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…

  3. dbrowne says:

    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.

  4. 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.

  5. dbrowne says:

    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.

  6. Kenneth Fisher says:

    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

  7. dbrowne says:

    @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.

  8. I was afraid of that.  Unfortunatly the server I'm working with only has IP1, IP2 and IPAll and I'm using IP1 & 2.  I did find a suggestion on how to edit the registry here: http://www.sql-server-performance.com/…/problem-with-sql-server-configuration-manager-detecting-new-ip-address-added-to-server.27281

    I think I'll back up my registry and give it a shot.  It is a test box after all.

  9. Thanks, very useful information.

  10. J B says:

    Hey David

    Suppose we want to move to HA groups and we currently have sooo many clients using named instance (serverinstance).

    However , HA listener does NOT allow backslahes in its DNS name , how we can do the opposite ?

    Cheers

  11. dbrowne says:

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

  12. Lee says:

    Does this work?

  13. piers7 says:

    After setting this up, can you still refer to the SQL instance by the instance name *as well*, or only by the server/cluster name?

    I'm trying to work out a configuration that would allow two side-by-side instances to work in isolation, without knowing which one was currently set as 'active' for the clients. I guess given they are cluster instances they could both use their own cluster names (not the DNS alias for that).

  14. dbrowne says:

    @piers7 Yes.  After setting this up you can refer to the instance by either its real name or the alias.

  15. Carl Lee says:

    in a clustered environment, which steps do I need to perform on the other nodes in the cluster?

  16. Alexander Zoutenbier says:

    Great explanation it's working finally in my environment.

    facebook.com/sharepointsupport

  17. Vasanth Reddy says:

    Its a great articles to read, it worked fine to me, Never do a copy past 🙂

  18. Jon Reade says:

    "It's not usual find a SQL instance listen on that port in a production environment."

    I'd say the opposite. It is perfectly normal and typical for a SQL instance listen on that port in a production environment. Most of the client sites I've worked on configure their SQL Servers exactly like that

    If you meant a named instance, that's a different matter, but for a based instance, it's the norm.

    I think there's a miscomprehension that by switching ports, you stop hackers in their tracks. You don't. Script kiddies, maybe. But for anyone who knows what they're doing, you're just kidding yourself into a false sense of security. Any port scanner worth its name can cope with detecting a non-default port, and it's very easy to write a simple one in VB or c# that will do exactly that and report back on any SQL Servers it finds, regardless of port number.

    Bottom line is, if someone's skilful enough to have breached that far inside your infrastructure from outside, the chances of stopping them are going to be slim. Swapping a port to a non standard one if they're conducting a targeted database server attack is NOT going to stop them finding your database servers.

    All that aside, this is an exceptionally useful article for aliasing named SQL Server instances that are being migrated to, thank you for the very clear information! 🙂

  19. Marsha says:

    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.

  20. Thanks. says:

    Is it possible to have variable @@servername = dns alias ?

  21. Fermin Sanchez says:

    Is this officially supported by Microsoft, no restrictions of any kind?