We need to change the port sql server uses from the default to… something else. Towards that end I want to make aliases on all the end user computers and other sql servers so they can still connect without problems.
Thank you to microsoft’s scripting guy and this article.
First I made a file with all the computers in the domain. Save this as getadcomputers.ps1 and then run it within powershell like this: .\getadcomputers.ps1 > .\computer.txt
# run as the following to save all the AD registered computers
# .\getadcomputers.ps1 > .\computer.txt
$strCategory = “computer”
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = (“(objectCategory=$strCategory)”)
$colProplist = “name”
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults)
{$objComputer = $objResult.Properties; $objComputer.name}
Then I added that to a friendly Access table, decided what computers to exclude, and made a routine to loop through and create the aliases.
Sub AddRegKey()
‘ Loops through the table sfhpComputers and
‘ 1. checks if the keypath & value exists
‘ 2. if yes then logs a message and continues
‘ 3. if not then adds the key and checks the add worked and logs a message
Dim strKeyRoot, strKeyPath, strValueName, strData, strValue, strType
Const HKEY_LOCAL_MACHINE = &H80000002
strKeyRoot = “HKEY_LOCAL_MACHINE”
strKeyPath = “SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo”
strValueName = “newAliasName”
strData = “DBMSSOCN,fullSQLServerName,NewPortNumber”
strType = “REG_SZ”
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(“select name, Note1, Note2 from Computers”)
With rs
Do While Not .BOF And Not .EOF
strComputer = !Name
‘ Check if the key exists
Set objregistry = GetObject(“winmgmts:\\” & _
strComputer & “\root\default:StdRegProv”)
objregistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue
If Not IsNull(strValue) Then
.Edit
!Note1 = “The registry key ” & strValueName & ” – ” & strData & ” already exists.”
!Note2 = Null
.Update
GoTo nextComputer
End If
‘ End check if the key exists
‘ Begin code to add registry key
shellcmd = “reg.exe add \\” & strComputer & “\” & strKeyRoot & “\” & strKeyPath & ” /v ” & strValueName & _
” /t ” & strType & ” /d ” & strData
output = Shell(shellcmd)
‘ End code to add registry key
‘ Now check the key was added
Set objregistry = GetObject(“winmgmts:\\” & _
strComputer & “\root\default:StdRegProv”)
.Edit
If Err = 462 Then
!Note1 = “Computer ” & strComputer & ” does not exist.”
!Note2 = output
GoTo nextComputer
End If
objregistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue
If IsNull(strValue) Then
!Note1 = “ERROR: The registry key ” & strValueName & ” – ” & strData & ” not added successfully.”
!Note2 = output
Else
!Note1 = “The registry key ” & strValueName & ” – ” & strValue & ” added successfully.”
!Note2 = output
End If
.Update
nextComputer:
.MoveNext
Loop
End With
End Sub
