PoshCode Archive  Artifact [2bd0873d11]

Artifact 2bd0873d11e0f23411c1bc10c839b55a3b511b41cec3676a9c05310c1dcd0260:

• File Add-SqlClientAlias.ps1 — part of check-in [18fc79dbe4] at 2018-06-10 13:41:30 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. If the host is running 64-bit architecture, it will add a matching alias for the 32-bit SQL client also. (user: Chad Miller size: 2219)

# 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. If the host is running 64-bit architecture, it will add a matching alias for the 32-bit SQL client also.
# version: 1.0
# type: script
# author: Chad Miller
# license: CC0
# x-poshcode-id: 4473
# x-archived: 2015-06-29T13:23:48
# x-published: 2015-09-17T18:14: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
# Check if the server is 64-bit and add a matching alias for the 32-bit SQL client if it is.
if ((Get-WmiObject Win32_OperatingSystem).OSArchitecture="64-bit") {
	Set-ItemProperty -Path 'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\' -Name $ServerAlias -Value $value
}