Share via

SQL Alias PowerShell script

  • Article

Hello everyone, this time I am going to show the "Server Alias".

I have been asked by one of my customers to write down a PowerShell script to add an SQL Alias as part of an automatic installation.

The Alias is stored in the registry at this path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

The alias is configured via the configuration manager.

An alias is a name that can be used instead of a connection.

It holds inside some elements of the connection string and can be used from each and every client application. One of its advantages is that it contains all the connection details - protocol, ports and named pipes. It even allows you to redirect a connection to a remote server.

Here is the script:

 ## Setting the Alias name, Server name and setting the port number
 ## Adding an extension to the alias name _ALDB Customer Request 
 $ServerName = Get-Content Env:Computername
 $AliasName = $ServerName+"_ALDB" 
 $SN
 $AliasName
 $PortNum = 1111
  
 ## The Aliase is stord in the registry here i'm seting the 
 ## REG path for the Alias 32 & 64
 $Bit32 = "HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo"
 $bit64 = "HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
  
 ## chect if the ConnectTo SubFolder 
 ## exist in the registry if not it will create it.
 If((Test-Path -Path $Bit32)-ne $true)
     {$writable = $true
         $key = (get-item HKLM:\).OpenSubKey("SOFTWARE\Microsoft\MSSQLServer\Client", $writable).CreateSubKey("ConnectTo")
     }
     If((Test-Path -Path $Bit64)-ne $true)
     {        $key = (get-item HKLM:\).OpenSubKey("SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client", $writable).CreateSubKey("ConnectTo")
  
     }
  
 ## Create the TCPAlias
 $TCPAliasName="DBMSSCON,"+$ServerName+","+$PortNum
 New-ItemProperty -Path $Bit32 -Name $AliasName -PropertyType string -Value $TCPAliasName
 New-ItemProperty -Path $Bit64 -Name $AliasName -PropertyType string -Value $TCPAliasName

Comments

  • Anonymous
    July 17, 2014
    Hi, thank for sharing, but there are 2 mistakes in a script.
    1. 64/32 path are switched
    2. Keyword is DBMSSOCN

    Jakub
  • Anonymous
    April 06, 2015
    why did you change the port number to 1111 instead of 1433?