PoshCode Logo PowerShell Code Repository

Add-SqlClientAlias by Chad Miller 26 months ago
View followups from PlateSpinner | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/3662"></script>download | new post

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.

  1. #######################
  2. <#
  3. .SYNOPSIS
  4. Adds a SQL Server Client Alias by setting registry key.
  5. .DESCRIPTION
  6. 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.
  7. .EXAMPLE
  8. ./add-sqlclientalias.ps1 -ServerAlias Z001\sql2 -ServerName Z001XA\sql2 -Protocol TCP -Port 5658
  9. This command add a SQL client alias
  10. .NOTES
  11. Version History
  12. v1.0   - Chad Miller - 9/24/2012 - Initial release
  13. .LINK
  14. http://social.msdn.microsoft.com/Forums/sa/sqldataaccess/thread/39fe3b15-96a1-454f-b3bd-da6b1f74700a
  15. #>
  16. param(
  17. [Parameter(Position=0, Mandatory=$true)]
  18. [string]
  19. $ServerAlias,
  20. [Parameter(Position=1, Mandatory=$true)]
  21. [string]
  22. $ServerName,
  23. [ValidateSet("NP", "TCP")]
  24. [Parameter(Position=2, Mandatory=$true)]
  25. [string]
  26. $Protocol="TCP",
  27. [Parameter(Position=3, Mandatory=$false)]
  28. [int]
  29. $PortNumber
  30. )
  31.  
  32. if ($Protocol="TCP") {
  33.     if ($PortNumber) {
  34.         $value = "DBMSSOCN,{0},{1}" -f $ServerName,$PortNumber
  35.     }
  36.     else {
  37.         $value = "DBMSSOCN,{0}" -f $ServerName
  38.     }
  39. }
  40. else {
  41.     $value = "DBNMPNTW,\\{0}\pipe\sql\query" -f $ServerName
  42. }
  43.  
  44. Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo' -Name $ServerAlias -Value $value

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me