2015-12-25

VDI Shadow Remote Control Select (With SQL DB)

看這篇文章前先參考前一篇開頭的說明
http://blog.dino9021.com/2015/12/vdi-shadow-control-rdcb-remote-desktop.html

由於遠端執行 PowerShell Get-RDUserSession 指令需要開的權限太多太複雜
一直搞不出來, 於是決定山不轉路轉, 將 RDCB DB 從 Windows Internal Database 搬到 SQL Server 來 Query [備註]
用 SQL Server 的方式 Query 速度比 Get-RDUserSession 快非常多, 還有可做 HA 的彈性
好處非常多, 非常推薦大家都改用 SQL Server, 真心不騙

這隻程式用來列出目前 RDCB SQL DB 上記載的 Remote Desktop Session 供選擇 Shadow 控制
可簡化遠端協助的步驟, 並可 Shadow Windows 10 Client
(Windows Server 2012 R2 RDCB 目前以 Server Manager 無法 Shadow 至 Windows 10 Client)

執行本 PowerShell Script 的帳號必須具有連線目的 Client 的本機管理員權限
以及連線至 RDCB SQL Server 與讀取 RDCB DB 的權限 (詳細權限容後敘述)

有關 Prompt User 的相關說明及設定參考以下連結
https://blogs.technet.microsoft.com/askperf/2013/10/22/windows-8-1-windows-server-2012-r2-rds-shadowing-is-back/

2016.12.01 Update: 撈出 Pooled VM 所屬 RDVH Server Name

執行時範例圖片:

首先在 AD 中建立一個賦予此目的的群組, 以下以 HelpDesk 群組為例
在 RDCB DB 中建立一個 View 名為 SessionList, 語法如下:

SELECT        SessionTable.UserName, SessionTable.ServerName, SessionTable.SessionId, SessionTable.SessionState, SessionTable.SessionDisconnectTime, VMHostTable.VMHostName
FROM            (SELECT        rds.Session.UserName, rds.Target.Name AS ServerName, rds.Session.SessionId, rds.Session.State AS SessionState, rds.Session.DisconnectTime AS SessionDisconnectTime
FROM            rds.Session INNER JOIN
rds.Target ON rds.Session.TargetId = rds.Target.Id) AS SessionTable LEFT OUTER JOIN
(SELECT        rds.Server.Name AS VMHostName, rds.Vm.VmHostId, rds.Vm.Name
FROM            rds.Server INNER JOIN
rds.Vm ON rds.Vm.VmHostId = rds.Server.Id) AS VMHostTable ON SessionTable.ServerName = VMHostTable.Name


接著設定安全性, 在 Security -> Logins 新增登入帳戶
General Tab:
  Login name 的地方按右方的 [Search], Object Types 勾選 Groups
  Locations 選擇 Entire Directory, 便可將 HelpDesk 群組選入
Server Roles:
  選擇預設的 public
User Mapping:
勾選 RDCB_DB 並給予預設的 public 權限
到剛剛建立的 SessionList View, 在 Properties -> Permission 中點選 [Search]
重複剛剛的選擇方式將 HelpDesk 群組選進來, 並賦予 (Grant) Select 權限 (只需要 Select 即可)

之後只要將 User Account 加入 Help Desk 群組
就可以在 SQL FireWall 允許連線的情況下以本程式來 Query 目前 Session 狀態
並選擇欲 Shadow Remote Control 的對象

RDShadowSelectSQLDB.ps1

$DomainName = "Contoso.com"
$RDCBServer = "rdcb.$DomainName"
$SQLServer = "RDCBSQL"
$DBName = "RDCB_DB"
$ViewName = "SessionList"

Function AddSpace {

# Position:
# 0 = Front
# else = End

Param(
[String]$String,
[Int]$Length,
[Int]$Position
);

$SpaceToAdd = $Length - $String.Length
for ($i=0;$i -lt $SpaceToAdd;$i++) {
if ($Position = 0) {
$String = " " + $String
} else {
$String = $String + " "
}
};

return $String;
};

Function QuerySQL {
$SQLConn = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Server=$SQLServer;Database=$DBName;Integrated Security=True"
$SQLConn.Open()

$QueryString = "SELECT * FROM [" + $DBName + "].[dbo].[" + $ViewName + "]"
$SQLQuery = new-object System.Data.SQLClient.SQLCommand($QueryString,$SQLConn)
$SQLResult = $SQLQuery.ExecuteReader()

while($SQLResult.Read()) {
$Script:UserName += @($SQLResult["UserName"])
$Script:ServerName += @($SQLResult["ServerName"])
$Script:SessionId += @($SQLResult["SessionId"])
$Script:SessionStateTemp = $SQLResult["SessionState"]
$Script:VMHostName += $SQLResult["VMHostName"]

switch($Script:SessionStateTemp) {
0 {
$Script:SessionStateTemp = "ACTIVE"
}
1 {
$Script:SessionStateTemp = "INVALID"
}
2 {
$Script:SessionStateTemp = "CONNECTQUERY"
}
3 {
$Script:SessionStateTemp = "SHADOW"
}
4 {
$Script:SessionStateTemp = "DISCONNECTED"
}
5 {
$Script:SessionStateTemp = "IDLE"
}
6 {
$Script:SessionStateTemp = "LISTEN"
}
7 {
$Script:SessionStateTemp = "RESET"
}
8 {
$Script:SessionStateTemp = "DOWN"
}
9 {
$Script:SessionStateTemp = "INIT"
}
Default {
$Script:SessionStateTemp = "UNKNOW"
}
}
$Script:SessionState += @($Script:SessionStateTemp)
}

$SQLResult.Close()
$SQLConn.Close()
};

Function MainBoard {

$Script:TargetSession = ""
while ($Script:TargetSession -eq "") {

cls
Write-Host ""
Write-Host "`tReading session, please wait..."

$Script:UserName = @()
$Script:ServerName = @()
$Script:SessionId = @()
$Script:SessionState = @()
$Script:VMHostName = @()

QuerySQL;
Start-Sleep -m 500

Write-Host ""
Write-Host "`tSession List: (ServerName with a * Means it's a RemoteAPP Session)"
Write-Host ""
Write-Host "`t No.`t UserName`t   ServerName      `t VMHostName       `t SessionState"
Write-Host "`t----`t--------------`t  -----------------`t-----------------`t--------------"
for ($i=0;$i -lt $Script:UserName.Count;$i++) {

$TrimServerName = $Script:ServerName[$i] -replace ".$DomainName", ""
if ($TrimServerName.Length -ne $Script:ServerName[$i].Length) {
$TrimServerName = "* " + $TrimServerName
};
$Script:ServerName[$i] = $Script:ServerName[$i] -replace ".$DomainName", ""

$TrimVMHostName = $Script:VMHostName[$i] -replace ".$DomainName", ""
if ([string]::IsNullOrEmpty($TrimVMHostName)) {
$TrimVMHostName = '-----------------'
};

#if ((($i/5) -is [int]) -and (($i/5) -gt 0)) {Write-Host "`t----`t--------------`t  -----------------`t-------------- $i"};
if (($i+1) -lt 10) {$No =  AddSpace ($i+1) 3 0;$No = " $No"} else {$No = AddSpace ($i+1) 3 0};
$Script:UserNameDisplay = AddSpace $Script:UserName[$i] 12 1
$Script:ServerNameDisplay = AddSpace $TrimServerName 17 1
$Script:VMHostNameDisplay = AddSpace $TrimVMHostName 17 1
Write-Host "`t" $No "`t" $Script:UserNameDisplay "`t  " $Script:ServerNameDisplay "`t" $Script:VMHostNameDisplay "`t" $Script:SessionState[$i]
};

Write-Host ""

$Script:ChooseString = "`tChoose 1-" + $Script:UserName.Count + " (Enter to Reload / q to Leave)"
$Script:TargetSession = (Read-Host $Script:ChooseString)

}

for ($i=0;$i -lt $Script:UserName.Count;$i++) {
if ($Script:TargetSession -eq ($i+1)) {
if (($Script:SessionState[$i] -ne "ACTIVE") -and ($Script:SessionState[$i] -ne "INVALID")) {
write-host ""
write-host "`tThe Session is not active, could not connect."
write-host ""
write-host "`tPress any key to leave ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
} else {
Write-Host "`tSelect Method:"
write-host ""
Write-Host "`t`t1 - View Only"
Write-Host "`t`t2 - Control"
write-host ""
$ViewOrControl = (Read-Host "`t`tChoose options 1-2 (Default: View Only)") -as [int]
write-host ""

switch($ViewOrControl) {
1 {
$ViewOrControl = ""
}
2 {
$ViewOrControl = " /Control"
}
Default {
$ViewOrControl = ""
}
}

Write-Host "`tSelect Option:"
write-host ""
Write-Host "`t`t1 - Prompt User"
Write-Host "`t`t2 - No Prompt"
write-host ""
Write-Host "`t`tDefault:"
Write-Host "`t`t`tView Only - No Prompt"
Write-Host "`t`t`tControl - Prompt User"
write-host ""
$PromptUser = (Read-Host "`t`tChoose from options 1-2") -as [int]
write-host ""

switch($PromptUser) {
1 {
$PromptUser = ""
}
2 {
$PromptUser = " /noConsentPrompt"
}
Default {
if ($ViewOrControl -eq "") {
$PromptUser = " /noConsentPrompt"
} else {
$PromptUser = ""
}

}
}

$Command = "mstsc /v:" + $Script:ServerName[$i] + "." + $DomainName + " /shadow:" + $Script:SessionId[$i] + $ViewOrControl + $PromptUser
write-host "`tCommand:" $Command
write-host ""
Invoke-Expression $Command
write-host "`tPress any key to reload ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
};
break;
};
};
};

while ( ($Script:TargetSession -ne "q") -and ($Script:TargetSession -ne "Q")  ) {
MainBoard;
};

write-host ""
write-host "`tPress any key to leave ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
write-host ""
write-host ""


RDShadowSelectSQLDB.bat

@echo off
mode con: cols=110 lines=40
"C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe" -command ". 'C:\Command\RDShadowSelectSQLDB.ps1';"

利用這個 .bat 檔來呼叫上面的 PowerShell 程式


[註]
詳細搬出來的辦法請參考 https://www.google.com.tw/search?q=rdcb+ha
SQL Server 可以用 Express Edition, RDCB DB 要儲存的資料非常小

需要注意的是 SQL Server 不能安裝在 RDCB 身上, 否則在建立 DB 的時候永遠都會失敗
在 EventViewer-Microsoft-Windows-TerminalServices-SessionBroker/Admin 中會顯示類似如下錯誤:

The Remote Desktop Connection Broker server could not install the database named RDCB_DB by using the connection string:
DRIVER=SQL Server Native Client 11.0;SERVER=RDCBSQL;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=master.
The database name in the connection string is shown as [master] because in order to create a new database with admin specified name, a connection must be made to master database.

Error: Database is offline.

沒有留言: