はじめに
サーバーにリモートデスクトップで接続したら誰かが使用中だった形跡があります。
デフォルトだと同時2セッションまでなので、誰が使用しているのか調べたいことがあります。
その場合、リモート先で query user /server:コンピューター名 を使用することで、ユーザー名、セッション名、ID、状態、アイドル時間、ログオン時間の情報を取得することができます。
しかし、運用方法が適切に管理しておらずユーザー名は同一であるため、誰が使用しているか判断できません。クライアント名なら社員コードが含まれているため判断できるのですが、その情報がありません。
参照記事によれば下記のレジストリからクライアント名を取得することができるようです。
HKEY_CURRENT_USER\Volatile Environment\[セッションID]\CLIENTNAME
参照：取得できない %CLIENTNAME% をなんとかする（batあり／タスクスケジューラで利用可能）
そこで、ASP.NETで query userの情報とレジストリ情報のクライアント名を紐付けた一覧表のWebサイトを作ることを考えました。
デフォルトユーザーを使用しない運用の影響
【2025/02/22追記】
新しいサーバーから、Administrator や sa などのデフォルトユーザーを使用しない運用を求められました。それにより、リモート接続ユーザーと管理者ユーザーを作成しました。
SQLServerは管理者ユーザーで動作しているため、リモート接続ユーザーのクライアント名が取得できな苦なる現象が発生したので修正を行いました。
ASP.NET上でレジストリにアクセスできない
ASP.NET上でRegistry.CurrentUserのGetSubKeyNames()でサブキーを全取得すると下記の通りで、肝心なVolatile Environmentがありません。試しにコンソールアプリケーションにして試すと問題なく取得できます。
Control Panel
Environment
EUDC
Keyboard Layout
Printers
Software
SYSTEM
ASP.NET上だとセキュリティーの関連で取得できないようです。何かしらやり方はあるのでしょうが諦めました。
SQLServerのCLRで実装
そのサーバーにはSQLServer 2014がインストールされていたので、CLRの勉強がてら作成してみることにしました。
SQLServerには正規表現が標準で搭載されておらず、CLR統合機能(C#などの.NET言語を使ってユーザー定義関数)を使えば作成することができるので、何れCLRに挑戦するつもりでいました。
ソースコード
外部プロセス実行でコマンドのquery userを実行し、セッションIDから該当のレジストリのクライアント名を取得して、テーブル値関数で戻り値をIEnumerableにています。
using System.Data.SqlTypes;
using System.Collections;
using Microsoft.SqlServer.Server;
using Microsoft.Win32;
using System.Collections.Generic;
public partial class GetRDPClient
{
[SqlFunction(FillRowMethodName = "FillRow",
TableDefinition = "userName nvarchar(max), sessionName nvarchar(max), id nvarchar(max), " +
"state nvarchar(max), idleTime nvarchar(max), logonTime nvarchar(max), clientName nvarchar(max)")]
public static IEnumerable InitMethod()
{
string stdout = GetUserSessionInfo();
List<UserInfo> userInfoList = ConvertStdOutToDataTable(stdout);
AddClientName(userInfoList);
return userInfoList;
}
public static void FillRow(Object row, out SqlString userName, out SqlString sessionName, out SqlString id,
out SqlString state, out SqlString idleTime, out SqlString logonTime, out SqlString clientName)
{
var userInfo = (UserInfo)row;
userName = new SqlString(userInfo.UserName);
sessionName = new SqlString(userInfo.SessionName);
id = new SqlString(userInfo.ID);
state = new SqlString(userInfo.State);
idleTime = new SqlString(userInfo.IdleTime);
logonTime = new SqlString(userInfo.LogonTime);
clientName = new SqlString(userInfo.ClientName);
}
private static string GetUserSessionInfo()
{
using (Process p = new Process())
{
string path = Environment.GetEnvironmentVariable("WINDIR");
if (Environment.Is64BitOperatingSystem && IntPtr.Size == 4)
{
path = Path.Combine(path, @"SysNative\query.exe");
}
else
{
path = Path.Combine(path, @"System32\query.exe");
}
p.StartInfo.FileName = System.Environment.GetEnvironmentVariable("ComSpec");
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardInput = false;
p.StartInfo.CreateNoWindow = true;
p.StartInfo.Arguments = "/c chcp 437 && " + path + " user";
p.StartInfo.Verb = "RunAs";
p.Start();
// 出力を読み取る
string results = p.StandardOutput.ReadToEnd();
p.WaitForExit();
p.Close();
return results;
}
}
private static List<UserInfo> ConvertStdOutToDataTable(string stdout)
{
string[] lines = stdout.Trim().Split('\n');
bool isHeader = true;
List<UserInfo> userInfoList = new List<UserInfo>();
List<string> columnName = new List<string>();
var t = typeof(UserInfo);
foreach (var f in t.GetFields())
columnName.Add(f.Name);
foreach (string line in lines)
{
if (line.Trim().Length < 30) continue;
UserInfo info = null;
if (!isHeader)
{
info = new UserInfo();
info.UserName = line.Substring(1, 22).Trim();
info.SessionName = line.Substring(23, 19).Trim();
info.ID = line.Substring(42, 4).Trim();
info.State = line.Substring(46, 8).Trim();
info.IdleTime = line.Substring(54, 11).Trim();
info.LogonTime = line.Substring(65).Trim();
}
isHeader = false;
if (info != null)
userInfoList.Add(info);
}
return userInfoList;
}
private static void AddClientName(List<UserInfo> list)
{
const string REG_SUBKEY = "Volatile Environment";
const string REG_CLIENT = "CLIENTNAME";
foreach (UserInfo info in list)
{
try
{
info.ClientName = (string)Registry.CurrentUser.OpenSubKey(REG_SUBKEY + "\\" + info.ID).GetValue(REG_CLIENT);
}
catch
{
info.ClientName = "unknown"
}
}
}
}
public class UserInfo
{
/// <summary>
/// ユーザー名
/// </summary>
public String UserName;
/// <summary>
/// セッション名
/// </summary>
public String SessionName;
/// <summary>
/// セッションID
/// </summary>
public String ID;
/// <summary>
/// 状態
/// </summary>
public String State;
/// <summary>
/// アイドル時間
/// </summary>
public String IdleTime;
/// <summary>
/// ログオン時刻
/// </summary>
public String LogonTime;
/// <summary>
/// クライアント名
/// </summary>
public String ClientName;
}
修正内容
【2025/02/22追記】
上述したデフォルトユーザーを使用しない運用の影響により、SQLServerは管理ユーザーで動かしているため、リモート接続ユーザーで入るとクライアント名を取得する際にレジストリからIDが見つからず、例外エラーが発生していました。
そこでTry Catchを追記して、例外発生時のクライアント名を"unknown"としました。
リモート接続ユーザーのクライアント名の取得
リモート接続ユーザーのクライアント名は、タスクスケジューラをリモート接続ユーザーアカウントにて5分間隔で動かして、PowerShellでCSVファイルを出力するようにしました。
Webアプリケーション側でCSVファイルを読み込んで、"unknown"からクライアント名に書き換えています。
CLR側でもCSVファイルを読み込む実装をしてもよかったのですが、今回はWebアプリケーション側にしました。
余談
当初は、DataTable型で作成していて、query user のヘッダー名を列名として採用する上でchcp 437にて表示言語を英語に変換していました。それで、LINQの使用して抽出したりしていたのですが、SQLServer 2014のCLRのバージョンでは LINQが使用できなかったのです。
LINQを使える方法があるのですが、そこまでしてやるのはやめて UserInfoクラスを作成してList型に切り替えました。
LINQ on a DataTable IN a CLR Stored Procedure - stackoverflow
CLRバージョン
SQL Serverのバージョン別の.NET Frameworkバージョン
| SQL Server | .NET Frameworkバージョン |
|---|---|
| SQL Server 2017 | .NET Framework 4.6 |
| SQL Server 2016 | .NET Framework 4.6 |
| SQL Server 2014 | .NET Framework 3.5 SP1 |
| SQL Server 2012 | .NET Framework 3.5 SP1 |
| SQL Server 2008 R2 | .NET Framework 3.5 SP1 |
| SQL Server 2008 | .NET Framework 3.5 SP1 |
Which version of .NET framework SQL Server supports? - stackoverflow
| SQL Server version | CLR version | .NET Framework version(s) |
|---|---|---|
| 2005 | 2.0 | 2.0, 3.0 **, and 3.5 ** To use any functionality within System.Core or System.Xml.Linq libraries, they must be imported manually as UNSAFE. |
| 2008 and 2008 R2 | 2.0 | 2.0, 3.0, and 3.5 |
| 2012, 2014, 2016, 2017, and 2019, (and should also be Azure SQL DB Managed Instance) | 4.0 | 4.0+ |
SQLServerにCLRの登録
アセンブリ（dll）の作成
今回は、「RDPClinet.dll」を作成します。
- VisualStudio 20XXで新規プロジェクトとして「SQL Server データベースプロジェクト」を作成します。
- プロジェクトに「SQL CLR C# ユーザー定義関数」を追加します。
- ソースコードを作成してビルドします。
- プロジェクトのプロパティで権限のレベルを「UNSAFE」にします。
CLRの有効化
デフォルトでは.net framework でのユーザー コードの実行は無効となっているため、clr enabled 構成オプションを有効にしてください。
EXEC sp_configure 'clr enabled';
EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;
アセンブリとユーザー関数の登録
【2025/02/22追記】
プログラムを修正した場合、ユーザー関数とアセンブリを一旦削除して再登録しましょう。
そうしないと、.NETエラーが発生しました。
DROP FUNCTION IF EXISTS GetRDPClient;
DROP ASSEMBLY IF EXISTS RDPClient;
アセンブリの登録
権限セットですが、レジストリだけの使用だけだったら、EXTERNAL_ACCESSだけでも良かったのですが、今回は外部プロセスを使用するので、UNSAFEになります。
CLR Integration Code Access Security
CREATE ASSEMBLY RDPClient
FROM 'C:\Temp\RDPClient.dll'
WITH PERMISSION_SET = UNSAFE;
TRUSTWORTHYの有効化
権限セットを「UNSAFE」または「外部」に設定している CLR オブジェクトを実行するには、TRUSTWORTHY オプションを有効化しておく必要があります。
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
-- [DatabaseName]の部分に設定するデータベースの名前を指定してください。
ユーザー関数の登録
CREATE FUNCTION GetRDPClient()
RETURNS TABLE (userName nvarchar(max), sessionName nvarchar(max), id nvarchar(max), state nvarchar(max), idleTime nvarchar(max), logonTime nvarchar(max), clientName nvarchar(max))
AS EXTERNAL NAME RDPClient.GetRDPClient.InitMethod;
GO
SELECT * FROM dbo.GetRDPClient()
ASP.NETで一覧表示
ASP.NETを使用してSQLServerの「dbo.GetRDPClient()」でアクセスし、リモートデスクトップ接続のユーザー情報を取得して一覧表示を実現しています。
| ID | クライアント名 | ユーザー名 | セッション名 | 状態 | アイドル時間 | ログオン時間 |
|---|---|---|---|---|---|---|
| 19 | XXX-0001 | administrator | Disc | 5:34 | 2021/02/25 14:56 | |
| 23 | XXX-0003 | administrator | rdp-tcp#43 | Active | . | 2021/03/12 10:48 |
最後に
そもそも、ユーザー名で管理すれば、リモート先から query user /server:コンピューター名 で取得できるので、こんな面倒なことしなくてもいいんですけどね。
あと、アイドル時間が一定時間経ったら、終了させてしまうなどしてしまえばいいでしょうね。
正規表現
SQLServerのCLR統合機能で正規表現を作成できるだけの技術は学べたので、簡単な正規表現を作るか機能が充実しているオープンソースを使用するかですね。