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