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, a distinguished professional, boasts an impressive track record as a Microsoft MVP, having achieved this prestigious recognition for the eighth consecutive year since 2015. With an extensive career spanning over 18 years, Adnan has honed his expertise in various domains, notably excelling in SharePoint, Microsoft 365, Microsoft Teams, the .Net Platform, and Microsoft BI. Presently, he holds the esteemed position of Senior Microsoft Consultant at Olive + Goose. Notably, Adnan served as the MCT Regional Lead for the Pakistan Chapter from 2012 to 2017, showcasing his leadership and commitment to fostering growth within the tech community. His journey in the realm of SharePoint spans 14 years, during which he has undertaken diverse projects involving both intranet and internet solutions for both private and government sectors. His impact has transcended geographical boundaries, leaving a mark on projects in the United States and the Gulf region, often collaborating with Fortune 500 companies. Beyond his roles, Adnan is a dedicated educator, sharing his insights and knowledge as a trainer. He also passionately advocates for technology, frequently engaging with the community through speaking engagements in various forums. His multifaceted contributions exemplify his dedication to the tech field and his role in driving its evolution.

Leave a Reply