A Powershell Script to configure the LANDESK Rollup Core (9.6 or newer - SQL Replication)

Version 1

    Verified Product Versions

    LANDESK Management Suite 9.6

    Here is a script that you can use to configure your rollup core after you've ran the installer. This script is based in the BKM document located here: Best Known Method To Configure Rollup for LDMS 9.6

     

    Please feel free to edit the document as you see fit, and if you have agents on your SQL servers that you'd like to rollup you could even deploy this script to those machines through LANDESK Software Distribution.

     

    <#  Configure-Rollup v0.1
        .DESCRIPTION
        This utility will configure and freshly installed 9.6 (or newer) Rollup core.
    
        {Script Switches}
            -mypass:        Used to specifiy your own password
            
            -subscriber:    Used to specifiy if you are running the script on a subscriber 
            
            -publisher:     Used to specifiy if you are running the script on a publisher
                **Note** Leave off -subscriber and -publisher if you are running both on one SQL server.
    
            -SQLInstance:   Used to specifiy your SQL Instance Name
    
            -remove:        Used to remove configurations created by this utility.
        .NOTES
        {Step 1} Create Accounts
            <machine_name>\ldms_snapshot
            <machine_name>\ldms_distribution
            <machine_name>\ldms_logreader
        {Step 2} Create a replication share and assign users - Publisher
            \Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\**ReplData**
            need to check if it exists already, then created it and add users.
            <machine_name>\ldms_snapshot - Full
            <machine_name>\ldms_distribution - Read
            <machine_name>\ldms_logreader - Read
        {Step 3} Add Users to Files & Folders - Subcriber
            ldms_distribution
                Modify - \Program Files\Microsoft SQL Server\<version>\COM
        {Step 4} Configure SQL Server Agent to Start Automatically.
                To change the startup type, open the Windows Services dialog by clicking on Start | Run and entering services.msc. 
                Locate the SQL Server Agent (MSSQLSERVER) service and change the startup type to Automatic save and restart the service.
    #>    
    
    
    [cmdletbinding()]
    Param (
        [switch]$mypass,
        [switch]$remove, 
        [switch]$subscriber, 
        [switch]$publisher, 
        [string]$SQLInstance = "MSSQLSERVER"
        
        )
    Begin { 
        Write-Output "[*]   Rollup Config Initializing..."
        $_USERNAMES = @("ldms_snapshot","ldms_distribution","ldms_logreader")
        $_REPLDATAPATH = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\repldata"
        $_COMPATH = "C:\Program Files\Microsoft SQL Server\120\COM"
        $compname = $env:ComputerName
        function createADSI($computer){
            return [ADSI]"WinNT://$computer"
        }
    
        function createUser($cn, $UserName, $Password) {
            Write-Output ("[+]   Creating User {0}" -f "$UserName")
            Write-Output ("[#]   With Password {0}" -f "$Password")
            $user = $cn.Create("User",$UserName)
            $user.SetPassword("$Password")
            $user.SetInfo()
            $user.description = "SQL Replication User"
            $user.SetInfo()
        }
    
        function createPass() {
          if (!$mypass) {
            $ref = [Reflection.Assembly]::LoadWithPartialName("System.Web")
            $_password = [System.Web.Security.Membership]::GeneratePassword(20,0) 
          }
          else {
            $Password = Read-Host -Prompt "Enter password" -AsSecureString
            $BSTR = [system.runtime.interopservices.marshal]::SecureStringToBSTR($Password)
            $_password = [system.runtime.interopservices.marshal]::PtrToStringAuto($BSTR)
            [Runtime.InteropServices.Marshal]::ZeroFreeBSTR($BSTR)
          }
          return $_password
        }
    
        function deleteUser($cn, $UserName){
            Write-Output("[-]   Deleting Replication User: {0}" -f $UserName)
            $cn.Delete('User',"$UserName")
        }
    
        function createReplData {
    
            if (!(Test-Path $_REPLDATAPATH)) {
                $item = new-item $_REPLDATAPATH -type Directory
                Write-Output ("[+]   Creating {0} Directory" -f $item.Name)
            }
            $Shares =[WMICLASS]"WIN32_Share"
            if (!(Get-WmiObject WIN32_Share -filter "name='ReplData'")) {
                $Shares.Create($_REPLDATAPATH,"ReplData",0) | Out-Null
    
            }
        }
    
        function deleteReplData {
            Write-Output("[-]   Deleting ReplData and Removing SMB Share")
            Remove-SmbShare "ReplData" -Force
            remove-item $_REPLDATAPATH
        }
    
        function addUserToShare($path, $username, $access){
            if(!(get-smbshare "ReplData")){
                $share = New-SMBShare -Name "ReplData" -Path "$path" -ContinuoslyAvailable -FullAccess "$compname\$username" 
                Write-Output "[+]   Creating ReplData Share"
                Write-Output ("[+]   Adding {0} to ReplData" -f "$username")
                $share | where {$_.AccountName -eq "$compname\$username"}
            }
            else {
                Write-Output ("[+]   Adding {0} to ReplData" -f "$username")
                Grant-SmbShareAccess -Name "ReplData" -AccountName "$compname\$username" -AccessRight Full -Force | where {$_.AccountName -eq "$compname\$username"}
            }
        }
    
        function addDistToCom($path, $username){
            Write-Output("[+]   Adding {0} to COM" -f "$username")
            $Acl = (get-item $_COMPATH).getaccesscontrol('access')
            $Ar = New-Object system.security.accesscontrol.filesystemaccessrule($username,'Modify','ContainerInherit, ObjectInherit', 'None', 'Allow')
            $Acl.AddAccessRule($Ar)
            Set-Acl $path $Acl
            (get-acl $path).Access | where {$_.IdentityReference -eq "$compname\$username"}
        }
    
        function removeUserFromCom {
            $user = "$compname\ldms_distribution" 
            Write-Output("[-]   Removing {0} From COM folder" -f $user)
            $acls = (get-item $_COMPATH).getaccesscontrol('access')
            $access = $acls.access | where {$_.IdentityReference -eq "$user"}
            $acls.RemoveAccessRule($access) | Out-Null
            Set-Acl -path $_COMPATH -aclObject $acl 
        }
    
        function configureSQLAgent {
            $_service = Get-Service -Name SQLSERVERAGENT
            $service = $_service | where {$_.DisplayName -eq "SQL Server Agent ($SQLInstance)"}
            Set-Service –Name $service.Name –StartupType "Automatic"
            Restart-Service -Name $service.Name
        }
    
        function configureRollup {
            $adsi = createADSI(hostname)
            $p = createPass
            $inLoop = $true
            if ($inLoop) {
                createReplData
            }
            ForEach ($u in $_USERNAMES) {
                if ($adsi.children | where {$_.SchemaClassName -eq 'user'} | where {$_.Name -eq "$u"}) {
                    Write-Output "[?]   Cannot create $u, cause it already exists!"
                    $inLoop = $false
                }
                else {
                    Write-Verbose "false"
                    createUser $adsi $u $p
                    addUserToShare $_REPLDATAPATH $u "FullControl"
                }
            }
            if ($inLoop) {
                addDistToCom $_COMPATH $_USERNAMES[1]
                configureSQLAgent
            }
            $inLoop = $false
        }
    
        function configureSubScriber {
            $adsi = createADSI(hostname)
            $p = createPass
            if ($adsi.children | where {$_.SchemaClassName -eq 'user'} | where {$_.Name -eq $_USERNAMES[1]}) {
                    Write-Output "[?]   Cannot create $_USERNAMES[1], cause it already exists!"
                }
                else {
                    Write-Verbose "false"
                    createUser $adsi $_USERNAMES[1] $p
                }
            addDistToCom $_COMPATH $_USERNAMES[1]
            configureSQLAgent
        }
    
        function configurePublisher {
            $adsi = createADSI(hostname)
            $p = createPass
            $inLoop = $true
            if ($inLoop) {
                createReplData
            }
            ForEach ($u in $_USERNAMES) {
                if ($adsi.children | where {$_.SchemaClassName -eq 'user'} | where {$_.Name -eq "$u"}) {
                    Write-Output "[?]   Cannot create $u, cause it already exists!"
                    $inLoop = $false
                }
                else {
                    Write-Verbose "false"
                    createUser $adsi $u $p
                    addUserToShare $_REPLDATAPATH $u "FullControl"
                }
            }
            if ($inLoop) {
                configureSQLAgent
            }
            $inLoop = $false
        }
    }
    Process { 
        if (!$remove) {
            if ($subscriber -and $publisher) { Write-Output "[*]    Failed!! Do not specify -subscriber and -publisher if you have both on the same SQL server." }
            if ($subscriber) { 
                Write-Output "[*]    Configuring Subscriber Settings on $compname."
                configureSubScriber
            }
            elseif ($publisher) { 
                Write-Output "[*]    Configuring Publisher Settings on $compname."
                configurePublisher
            }
            else {
                 Write-Output "[*]    Configuring Subscriber and Publisher settings on $compname."
                configureRollup  
            }
            
        }
        else {
            $adsi = createADSI($compname)
            removeUserFromCom
            deleteReplData
            ForEach($u in $_USERNAMES){
                deleteUser $adsi $u
            }
        }
    }
    End { Write-Output "[*]   Rollup Config Complete."}