Enable and Disable New Remote Desktop Connections with Powershell

Enable and Disable New Remote Desktop Connections with Powershell

I was doing maintenance this morning on a Remote Desktop farm that contains few Session Collections and 15 + Servers, so I had to Deny all new connections from the farm.

Because I am lazy and don't want to click on GUI too many times, I wanted to do it on powershell. My goal is actually automatise most of the regular maintenance tasks, so this script comes handy. To my supprice, Google didn't find any ready scripts to this, so I had to write it myself.

Here are two scripts, Deny connections and Allow connections. It is easy also to put Allow/Deny as parameter, but in my case, this is more useful. 

This is how it works:

1) Get all RD Sessionhosts to $Hosts variable

2) For each loop to allow/deny connections

3) Write host

Deny New Connections:

$Hosts = Get-RDServer | where 'Roles' -eq "RDS-RD-Server" | Select -Expand Server

 ForEach($Hostx in $Hosts)

{

$Hostx

set-RDSessionHost $Hostx -NewConnectionAllowed No

}

Write-Host -ForegroundColor Red "New Connections are disabled"

Allow New Connections

$Hosts = Get-RDServer | where 'Roles' -eq "RDS-RD-Server" | Select -Expand Server

 ForEach($Hostx in $Hosts)

{

$Hostx

set-RDSessionHost $Hostx -NewConnectionAllowed Yes

}

Write-Host -ForegroundColor Red "New Connections are enabled"

Restoring database with powershell

Related to  SFTP Transfer script with logging -post, I needed to a way to schedule database restore every night. After a bit of googleing and copy pasteing I came up with following script.

Short descrition

1. Look for latest file in folder
2. Drop existing datatabase
3. Restore latest backup
4. Add dbuser rights

Requirements:
- PowerShell Logging Module (https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85)
- SQLServer -Powershell module
- SQLASCmdlets -Powershell module


<#.Synopsis
This scripts restores SQL Database from BAK file and relocates files

.Description
Writen by Miikka Kallberg

.Parameter SQLInstance
SQL Instance name

.Parameter Database
Database name

.Parameter DBBAKPath
Path of BAK file

.Parameter DBDataPath
Database Data Path

.Parameter DBLogPath
Database Log Path

.Parameter logpath
Script log Path

.Parameter synclogfile
Script Log file

.Parameter DBUser
user account that will be added to database

.Parameter DBUserRole
role for that user

.Example
.\restore_tk.ps1
Restores Database with values configured below

#>
param (
# Edited 30.03.2017 Miikka Kallberg /CSC - IT Center for Science Ltd.

$SQLInstance = "",  # SQL Instance name
$Database = "", # Database name
$DBLogicalName = "", # Database logical name
$DBBAKPathFolder = "", #Path of BAK file
$DBDataPath = "", #Database Data Path
$DBLogPath = "", #Database Log Path
$logpath = "", # Log Path
$synclogfile = "", # Log file
$DBUser = "", #user account that will be added to database
$DBUserRole ="" #role for that user
)
#This script requires Powershell Logging and SQLASCmdlets modules to be imported to powershell modules folder

# This is a custom module and needs to be installed before running this script
# Default location for PowerShell Modules is: C:\Windows\System32\WindowsPowerShell\v1.0\Modules
# https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85

Import-Module SQLServer
Import-Module SQLASCmdlets
Import-Module PowerShellLogging

#Add timestamp to script log
get-date | out-file -filePath (Join-Path $logpath $synclogfile) -Encoding ascii -Force

#Start logging
#Format date and time for the logfilename
$sdate = ((get-date).ToString("yyyyMMdd_HHmmss_"))

#Combine logfile name with time
$synclog =  $sdate + $synclogfile

#Define logfile
$LogFile = Enable-LogFile -Path (Join-Path $logpath $synclog)

# Note - it is necessary to save the result of Enable-LogFile to a variable in order to keep the object alive.  As soon as the $LogFile variable is reassigned or falls out of scope, the LogFile object becomes eligible for garbage collection.

$VerbosePreference = 'Continue'
$DebugPreference = 'Continue'

#Start restore script

# Get latest file from network path
$DBBAKfilename = Get-ChildItem $DBBAKPathFolder | select -last 1
$DBBAKfile = $DBBAKfilename.Name
$DBBAKPath = $DBBAKPathFolder + $DBBAKfile

#Drop existing Database
$databasePath = join-path $SQLInstance "Databases\$Database"
Invoke-SqlCmd "USE [master]; ALTER DATABASE [$Database] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$Database] "

#$DBLogicalName = $Database
$DBDataRelocPath =  $DBDataPath + $Database + ".mdf"
$DBLogRelocPatch =  $DBLogPath + $Database + ".log"
$DBLogicalName_Log = $DBLogicalName +"_log"
$Database_Log = $Database +"_log"
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$DBLogicalName", "$DBDataRelocPath")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$DBLogicalName_Log","$DBLogRelocPatch")

Restore-SqlDatabase -ServerInstance $SQLInstance -Database $Database -BackupFile $DBBAKPath  -ReplaceDatabase  -RelocateFile @($RelocateData,$RelocateLog) -RestoreAction Database

#Rename logical name
Invoke-SqlCmd "ALTER DATABASE [$Database] MODIFY FILE (NAME=N'$DBLogicalName',NEWNAME=N'$Database') "
Invoke-SqlCmd "ALTER DATABASE [$Database] MODIFY FILE (NAME=N'$DBLogicalName_Log',NEWNAME=N'$Database_Log') "

# Add user rights to database
# Define server defaults
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance

# Define Databse
$db = $svr.Databases[$Database]

# Add User
$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $DBUser)
$usr.Login = $DBUser
$usr.Create()

#Add user to role
$Rol = $db.Roles[$DBUserRole]
$Rol.AddMember($DBUser)
Write-Host "$DBUser added to $DBUserRole Role in $Database on $SQLInstance "
 
# Disable logging before the script exits (good practice, but the LogFile will be garbage collected so long as this variable was not in the Global Scope, as a backup in case the script crashes or you somehow forget to call Disable-LogFile).

$LogFile | Disable-LogFile

SFTP Transfer script with logging

We had a need to copy database dumbs from our data provider to our servers daily. Since I have already used WinSCP with Powershell, so I re-used that script and added logging and folder cleanup.

Requirements:
- WinSCP installed
- PowerShell Logging Module (https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85)


param (
# Edited 30.03.2017 Miikka Kallberg /CSC - IT Center for Science Ltd.

$sftpserver = "", # SFTP hostname
$sftpuser = "", # SFTP Username
$sftppass = "", # SFTP Password
$sftpkeyfp = "", # SFP Host Key Finger Print
$winscppath = "C:\Program Files (x86)\WinSCP\WinSCPnet.dll", # Path to WinCSPnet.dll
$localPath = "", # Path where to sync
$remotePath = "", # SFTP Path
$filenumber = "", # Number of saved files
$sleepTime = "",  # sleep time in seconds between synchronizations
$logpath = "", # Synck log Path
$synclogfile = "" # Sync Log file
)

# This is a custom module and needs to be installed before running this script
# Default location for PowerShell Modules is: C:\Windows\System32\WindowsPowerShell\v1.0\Modules
# https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85
#And WinSCP installed with automation package

#Add timestamp to script log
get-date | out-file -filePath (Join-Path $logpath $synclogfile) -Encoding ascii -Force

#Start logging
#Format date and time for the logfilename
$sdate = ((get-date).ToString("yyyyMMdd_HHmmss_"))

#Combine logfile name with time
$synclog =  $sdate + $synclogfile

Import-Module PowerShellLogging

#Define logfile
$LogFile = Enable-LogFile -Path (Join-Path $logpath $synclog)

# Note - it is necessary to save the result of Enable-LogFile to a variable in order to keep the object alive.  As soon as the $LogFile variable is reassigned or falls out of scope, the LogFile object becomes eligible for garbage collection.

$VerbosePreference = 'Continue'
$DebugPreference = 'Continue'

#Start syncin script
try
{
    # Load WinSCP .NET assembly
Add-Type -Path $winscppath

    # Setup session options
    $sessionOptions = New-Object WinSCP.SessionOptions
    $sessionOptions.Protocol = [WinSCP.Protocol]::Sftp
    $sessionOptions.HostName = $sftpserver
    $sessionOptions.UserName = $sftpuser
    $sessionOptions.Password = $sftppass
    $sessionOptions.SshHostKeyFingerprint = $sftpkeyfp

    $session = New-Object WinSCP.Session
 
     try
    {
        # Connect
        $session.Open($sessionOptions)

        # Download files
        $transferOptions = New-Object WinSCP.TransferOptions
        $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary

        $transferResult = $session.GetFiles( $remotePath, $localPath, $False, $transferOptions)

        # Throw on any error
        $transferResult.Check()

        # Print results
        foreach ($transfer in $transferResult.Transfers)
        {
            Write-Host ("Download of {0} succeeded" -f $transfer.FileName)
        }
    }

    finally
    {
        # Disconnect, clean up
        $session.Dispose()
   }
    exit 0
}
catch [Exception]
{
    Write-Host $_.Exception.Message
    exit 1
}

#Delete files that are oldest based on $filenumber
Get-ChildItem $localPath | where{-not $_.PsIsContainer}| sort CreationTime -desc|  select -Skip $filenumber | Remove-Item -Force

# Disable logging before the script exits (good practice, but the LogFile will be garbage collected so long as this variable was not in the Global Scope, as a backup in case the script crashes or you somehow forget to call Disable-LogFile).

$LogFile | Disable-LogFile