How to Create SQL Server Alias using PowerShell?

Do you need to create a SQL Server alias but don’t want to use the GUI? PowerShell is here to help! This post will show you how to create an alias for a SQL Server instance using PowerShell. This is a useful technique for making it easy to reference specific servers in your scripts or applications. So, if you’re looking for a more efficient way to create aliases for your SQL Servers, read on!

As per SharePoint best practices for SQL Server, SQL Alias needs to be created on all SharePoint Servers on the farm. Creating SQL Alias for SharePoint through UI is already explained in my other post: Create SQL Server Alias for SharePoint. Here is the PowerShell version of creating SQL Server Alias.

#Name of your SQL Server Alias
$AliasName = "SP13_PROD_SQL"

# Actual SQL Server Name
$SQLServerName = "G1VSP13-SQLC001" 

#TCP Port
$Port = "1433"

#These are the two Registry locations for the SQL Alias 
$x64 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"
$x86 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"

#if the ConnectTo key doesn't exists, create it.
if ((test-path -path $x86) -ne $True)
    New-Item $x86

if ((test-path -path $x64) -ne $True)
    New-Item $x64

#Define SQL Alias 
$TCPAliasName = "DBMSSOCN,$SQLServerName,$Port"

#Create TCP/IP Aliases
New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAliasName
New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAliasName

finally, open these two executables to verify:

  • C:\Windows\System32\cliconfg.exe
  • C:\Windows\SysWOW64\cliconfg.exe
create sql alias for sharepoint 2013 powershell
create sql alias for sharepoint 2016

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

6 thoughts on “How to Create SQL Server Alias using PowerShell?

  • $TCPAliasName = “DBMSSOCN,$SQLServerName,$Port”

    The line above is NOT correct, if you would like to create a SQL alias using TCP/IP, you should change this line to the following:
    $TCPAliasName = “TCP/IP,$SQLServerName,$Port”

  • Just a small observation: you’ve got the reg paths reversed: the x86 one refers to the 64 bit config and viceversa

  • I realize this was 3 years ago, but just curious if you tried rebooting or restarting the sql service since this is inherently a registry modification that might not be picked up right away.

  • This way does not properly create the alias using TCP/IP. I tried it and experienced SQL errors when trying to create the farm and configuration database.

    When I manually changed the alias to use TCP/IP, it then succeeded.

  • Can you tell me how I can add enabling/disabling the TCP/IP and Pipes protocols in this script?


Leave a Reply

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