Back to Insights

Create a SQL Alias with a PowerShell Script

Over the next few weeks I'll be creating a dozen or so virtual machines (VM). To save time I'm trying to script as much of the installation and configuration as possible, and today the portion I wanted to script was the creation of a SQL Alias. I'm sure everyone has read my wonderful blog post on Creating a SQL Alias for SharePoint, but that's a manual process ?

Enter PowerShell!

I found a few threads that discussed doing this, but none detailed the steps 100 percent. I did a little digging with the SQL Server Client Network Utility and the registry, and came up with the script below. Using this I'm able to create the same alias for both x86 and x64 SQL connections on every machine!

#This is the name of your SQL Alias
$AliasName = "Alias2"

#This is the name of your SQL server (the actual name!)
$ServerName = "ServerName2"

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

#We're going to see if the ConnectTo key already exists, and create it if it doesn't.
if ((test-path -path $x86) -ne $True)
{
    write-host "$x86 doesn't exist"
    New-Item $x86
}
if ((test-path -path $x64) -ne $True)
{
    write-host "$x64 doesn't exist"
    New-Item $x64
}

#Adding the extra "fluff" to tell the machine what type of alias it is
$TCPAlias = "DBMSSOCN," + $ServerName
$NamedPipesAlias = "DBNMPNTW,\\" + $ServerName + "\pipe\sql\query"

#Creating our TCP/IP Aliases
New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAlias
New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAlias

#Creating our Named Pipes Aliases
New-ItemProperty -Path $x86 –Name $AliasName -PropertyType String -Value $NamedPipesAlias
New-ItemProperty -Path $x64 –Name $AliasName -PropertyType String -Value $NamedPipesAlias

Enjoy!

Share