PoshCode Archive  Artifact [0c7ca8ac3b]

Artifact 0c7ca8ac3bcbf46937fe39dc45a72b97900e8881bfcd67d3fdc34776b8e82d8f:

• File Add-SqlClientAlias.ps1 — part of check-in [0da0ac1faf] at 2018-06-10 14:14:08 on branch trunk — Provides same functionality as cliconfg.exe GUI. Although there is a WMI provider to add client network aliases, the differences between SQL version make it diffult to use. This method creates the registry key. (user: Chad Miller size: 1976)

# encoding: ascii
# api: powershell
# title: Add-SqlClientAlias
# description: Provides same functionality as cliconfg.exe GUI. Although there is a WMI provider to add client network aliases, the differences between SQL version make it diffult to use. This method creates the registry key.
# version: 1.0
# type: script
# author: Chad Miller
# license: CC0
# x-poshcode-id: 6365
# x-archived: 2016-06-04T19:04:11
# x-published: 2016-06-02T19:08:00
#
#
#######################
<#
.SYNOPSIS
Adds a SQL Server Client Alias by setting registry key. 
.DESCRIPTION
Provides same functionality as cliconfg.exe GUI. Although there is a WMI provider to add client network aliases, the differences between SQL version make it diffult to use. This method creates the registry key.
.EXAMPLE
./add-sqlclientalias.ps1 -ServerAlias Z001\sql2 -ServerName Z001XA\sql2 -Protocol TCP -Port 5658
This command add a SQL client alias
.NOTES
Version History
v1.0   - Chad Miller - 9/24/2012 - Initial release
.LINK
http://social.msdn.microsoft.com/Forums/sa/sqldataaccess/thread/39fe3b15-96a1-454f-b3bd-da6b1f74700a
#>
param(
[Parameter(Position=0, Mandatory=$true)]
[string]
$ServerAlias,
[Parameter(Position=1, Mandatory=$true)]
[string]
$ServerName,
[ValidateSet("NP", "TCP")]
[Parameter(Position=2, Mandatory=$true)]
[string]
$Protocol="TCP",
[Parameter(Position=3, Mandatory=$false)]
[int]
$PortNumber
)

if ($Protocol="TCP") {
    if ($PortNumber) {
        $value = "DBMSSOCN,{0},{1}" -f $ServerName,$PortNumber
    }
    else {
        $value = "DBMSSOCN,{0}" -f $ServerName
    }
}
else {
    $value = "DBNMPNTW,\\{0}\pipe\sql\query" -f $ServerName
}

Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo' -Name $ServerAlias -Value $value

### this sets the 64 bit equivalent  ###
Set-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo' -Name $ServerAlias -Value $value