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.

Adnan is six time Microsoft MVP (Since 2015) with over 16 years of extensive experience with major expertise on SharePoint, SharePoint based development, Microsoft 365, Microsoft Teams, .Net Platform and Microsoft BI. He is currently working Sr Microsoft Consultant at Olive + Goose. He is MCT Regional Lead for Pakistan Chapter since 2012. He is working on SharePoint for past 12 years and worked on different intranet/intranet solutions for private & govt. sector majorly in United states and Gulf region and have experience of working with multiple Fortune 500 companies. He is a trainer, technology evangelist and also speaks in community forums.

Leave a Reply