Hostname Alias for 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.

There are three steps to creating a Hostname alias:

  • Create a DNS A record to the IP address of target Server
  • Configure SQL Server to listen on port 1433 on that that IP address
  • Add BackConnectionHostNames entries to enable NTLM Authentication.
    • This step is to overcome access error you try to access server using its FDQN. More details here at Microsoft KB article 926642.
    • Execute PowerShell script to add BackConnectionHostNames on SQL machine. Please note that highlighted value below is environment specific. It’s the DNS that was created in step#1.

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

For details on host name alias for SQL Server instance, see How to Add a Hostname Alias for a SQL Server Instance.

The following two tabs change content below.
Adnan Amin
Adnan is two time SharePoint MVP (Most Valuable Professional) with over 12 years of extensive experience with major expertise on SharePoint Server, Office 365, .Net Platform and Microsoft BI. He is currently working SharePoint Architect at FMT Consultants. He is MCT Regional Lead for Pakistan Chapter since 2012. He is working on SharePoint for past nine years and worked on different intranet/intranet solutions for private & govt. sector majorly in Gulf region, which include OOB and customized solutions. He is a trainer, technology evangelist and also speaks in community forums.

Leave a Reply

Your email address will not be published. Required fields are marked *