443 lines
17 KiB
PowerShell
443 lines
17 KiB
PowerShell
# Modules\SqlService.ps1
|
|
# Microsoft SQL Server Management Service Module (.NET System.Data.SqlClient Live & Fallback)
|
|
|
|
# P/Invoke helper for Windows Token Impersonation (AD Domain user outbound network authentication)
|
|
$winAuthTypeDef = @"
|
|
using System;
|
|
using System.Runtime.InteropServices;
|
|
using System.Security.Principal;
|
|
using Microsoft.Win32.SafeHandles;
|
|
|
|
public class WinAuthUniversalImpersonator {
|
|
[DllImport("advapi32.dll", SetLastError = true, CharSet = CharSet.Unicode)]
|
|
public static extern bool LogonUser(
|
|
string lpszUsername,
|
|
string lpszDomain,
|
|
string lpszPassword,
|
|
int dwLogonType,
|
|
int dwLogonProvider,
|
|
out IntPtr phToken
|
|
);
|
|
|
|
[DllImport("kernel32.dll", SetLastError = true)]
|
|
[return: MarshalAs(UnmanagedType.Bool)]
|
|
public static extern bool CloseHandle(IntPtr handle);
|
|
|
|
public const int LOGON32_LOGON_NEW_CREDENTIALS = 9; // /netonly network outbound impersonation
|
|
public const int LOGON32_PROVIDER_DEFAULT = 0;
|
|
|
|
public static void RunImpersonatedAction(string username, string domain, string password, Action action) {
|
|
IntPtr tokenHandle = IntPtr.Zero;
|
|
bool success = LogonUser(username, domain, password, LOGON32_LOGON_NEW_CREDENTIALS, LOGON32_PROVIDER_DEFAULT, out tokenHandle);
|
|
if (!success || tokenHandle == IntPtr.Zero) {
|
|
int err = Marshal.GetLastWin32Error();
|
|
throw new InvalidOperationException("AD 도메인 로그온(LogonUser) 실패: Win32 에러 코드 " + err);
|
|
}
|
|
|
|
try {
|
|
using (SafeAccessTokenHandle safeHandle = new SafeAccessTokenHandle(tokenHandle)) {
|
|
WindowsIdentity.RunImpersonated(safeHandle, action);
|
|
}
|
|
}
|
|
finally {
|
|
// SafeAccessTokenHandle owns and closes tokenHandle
|
|
}
|
|
}
|
|
}
|
|
"@
|
|
|
|
if (-not ("WinAuthUniversalImpersonator" -as [type])) {
|
|
Add-Type -TypeDefinition $winAuthTypeDef
|
|
}
|
|
|
|
function Invoke-WithSqlAuthContext {
|
|
param(
|
|
[string]$AuthType = "Integrated",
|
|
[string]$Domain = "",
|
|
[string]$Username = "",
|
|
[string]$Password = "",
|
|
[scriptblock]$Action
|
|
)
|
|
|
|
if ($AuthType -eq "AdImpersonated" -and -not [string]::IsNullOrWhiteSpace($Username) -and -not [string]::IsNullOrWhiteSpace($Password)) {
|
|
$cleanDomain = $Domain
|
|
$cleanUser = $Username
|
|
|
|
if ($cleanUser -like "*\*") {
|
|
$parts = $cleanUser -split "\\", 2
|
|
$cleanDomain = $parts[0]
|
|
$cleanUser = $parts[1]
|
|
} elseif ($cleanUser -like "*@*") {
|
|
$parts = $cleanUser -split "@", 2
|
|
$cleanUser = $parts[0]
|
|
$cleanDomain = $parts[1]
|
|
}
|
|
if ([string]::IsNullOrWhiteSpace($cleanDomain)) {
|
|
$cleanDomain = "."
|
|
}
|
|
|
|
$resHolder = [hashtable]@{ Result = $null; Exception = $null }
|
|
[WinAuthUniversalImpersonator]::RunImpersonatedAction($cleanUser, $cleanDomain, $Password, [Action]{
|
|
try {
|
|
$resHolder.Result = & $Action
|
|
} catch {
|
|
$resHolder.Exception = $_
|
|
}
|
|
})
|
|
|
|
if ($resHolder.Exception) {
|
|
throw $resHolder.Exception
|
|
}
|
|
return $resHolder.Result
|
|
} else {
|
|
return & $Action
|
|
}
|
|
}
|
|
|
|
function Build-SqlConnectionString {
|
|
param(
|
|
[string]$Server,
|
|
[string]$AuthType = "Integrated",
|
|
[string]$Username = "",
|
|
[string]$Password = "",
|
|
[string]$DefaultDatabase = "master",
|
|
[int]$Timeout = 10
|
|
)
|
|
|
|
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
|
|
$builder["Data Source"] = $Server
|
|
$builder["Initial Catalog"] = if ([string]::IsNullOrWhiteSpace($DefaultDatabase)) { "master" } else { $DefaultDatabase }
|
|
$builder["Connect Timeout"] = $Timeout
|
|
$builder["Application Name"] = "EnterpriseAdminStudio"
|
|
|
|
# Integrated(현재 세션) 및 AdImpersonated(AD 위장 토큰)는 SQL Server 관점에서 둘 다 Windows 통합 인증(SSPI)
|
|
if ($AuthType -eq "Integrated" -or $AuthType -eq "AdImpersonated" -or [string]::IsNullOrWhiteSpace($Username)) {
|
|
$builder["Integrated Security"] = $true
|
|
} else {
|
|
$builder["Integrated Security"] = $false
|
|
$builder["User ID"] = $Username
|
|
$builder["Password"] = $Password
|
|
}
|
|
|
|
return $builder.ConnectionString
|
|
}
|
|
|
|
function Test-SqlConnection {
|
|
param(
|
|
[string]$Server,
|
|
[string]$AuthType = "Integrated",
|
|
[string]$Domain = "",
|
|
[string]$Username = "",
|
|
[string]$Password = "",
|
|
[int]$Timeout = 5
|
|
)
|
|
|
|
try {
|
|
$connStr = Build-SqlConnectionString -Server $Server -AuthType $AuthType -Username $Username -Password $Password -Timeout $Timeout
|
|
|
|
$execResult = Invoke-WithSqlAuthContext -AuthType $AuthType -Domain $Domain -Username $Username -Password $Password -Action {
|
|
$conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
|
|
try {
|
|
$conn.Open()
|
|
$cmd = $conn.CreateCommand()
|
|
$cmd.CommandText = "SELECT SUSER_SNAME() AS [LoginName], @@VERSION AS [Version], SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('Edition') AS [Edition]"
|
|
$reader = $cmd.ExecuteReader()
|
|
if ($reader.Read()) {
|
|
$loginName = $reader["LoginName"].ToString()
|
|
$ver = $reader["ProductVersion"].ToString()
|
|
$edition = $reader["Edition"].ToString()
|
|
|
|
$authDesc = switch ($AuthType) {
|
|
"AdImpersonated" { "AD 계정 위장 Windows 통합 인증 ($loginName)" }
|
|
"Integrated" { "현재 세션 Windows 통합 인증 ($loginName)" }
|
|
default { "SQL 계정 인증 ($loginName)" }
|
|
}
|
|
|
|
return [PSCustomObject]@{
|
|
Success = $true
|
|
Message = "SQL Server 연결 성공!`n- 인증 방식: $authDesc`n- 서버 버전: $ver ($edition)"
|
|
LoginName = $loginName
|
|
Version = "$ver ($edition)"
|
|
}
|
|
}
|
|
return [PSCustomObject]@{ Success = $true; Message = "SQL Server 연결 성공" }
|
|
} finally {
|
|
if ($conn.State -eq [System.Data.ConnectionState]::Open) {
|
|
$conn.Close()
|
|
}
|
|
$conn.Dispose()
|
|
}
|
|
}
|
|
return $execResult
|
|
} catch {
|
|
return [PSCustomObject]@{
|
|
Success = $false
|
|
Message = "SQL Server 연결 실패: $($_.Exception.Message)"
|
|
}
|
|
}
|
|
}
|
|
|
|
function Get-SqlDatabases {
|
|
param(
|
|
[string]$Filter = "",
|
|
[object]$Config = $null
|
|
)
|
|
|
|
$useLive = if ($Config -and $Config.MsSql) { [bool]$Config.MsSql.UseLive } else { $false }
|
|
|
|
if ($useLive) {
|
|
$server = $Config.MsSql.Server
|
|
$auth = $Config.MsSql.AuthType
|
|
$domain = if ($Config.MsSql.Domain) { $Config.MsSql.Domain } else { "" }
|
|
$user = $Config.MsSql.Username
|
|
$pass = $Config.MsSql.Password
|
|
$timeout = if ($Config.MsSql.ConnectionTimeout) { [int]$Config.MsSql.ConnectionTimeout } else { 3 }
|
|
|
|
try {
|
|
$connStr = Build-SqlConnectionString -Server $server -AuthType $auth -Username $user -Password $pass -Timeout $timeout
|
|
|
|
$dbList = Invoke-WithSqlAuthContext -AuthType $auth -Domain $domain -Username $user -Password $pass -Action {
|
|
$conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
|
|
try {
|
|
$conn.Open()
|
|
$cmd = $conn.CreateCommand()
|
|
$cmd.CommandText = @"
|
|
SELECT
|
|
d.name AS DatabaseName,
|
|
d.state_desc AS StateDesc,
|
|
d.recovery_model_desc AS RecoveryModel,
|
|
d.collation_name AS CollationName,
|
|
CAST(ROUND(SUM(CAST(mf.size AS bigint)) * 8.0 / 1024, 1) AS varchar(30)) + ' MB' AS TotalSizeMB,
|
|
ISNULL(CONVERT(varchar(20), MAX(b.backup_finish_date), 120) + ' (' + b.type + ')', '백업 없음') AS LastBackupDate
|
|
FROM sys.databases d
|
|
LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
|
|
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D'
|
|
GROUP BY d.name, d.state_desc, d.recovery_model_desc, d.collation_name, b.type
|
|
ORDER BY d.name;
|
|
"@
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
|
|
$table = New-Object System.Data.DataTable
|
|
$adapter.Fill($table) | Out-Null
|
|
|
|
$list = [System.Collections.Generic.List[PSCustomObject]]::new()
|
|
foreach ($row in $table.Rows) {
|
|
$dbName = $row["DatabaseName"]
|
|
if ([string]::IsNullOrWhiteSpace($Filter) -or $dbName -like "*$Filter*") {
|
|
$list.Add([PSCustomObject]@{
|
|
DatabaseName = $dbName
|
|
StateDesc = $row["StateDesc"]
|
|
TotalSizeMB = $row["TotalSizeMB"]
|
|
FreeSpaceMB = "실시간 계산"
|
|
RecoveryModel = $row["RecoveryModel"]
|
|
CollationName = $row["CollationName"]
|
|
LastBackupDate = $row["LastBackupDate"]
|
|
IsLive = $true
|
|
})
|
|
}
|
|
}
|
|
return $list.ToArray()
|
|
} finally {
|
|
if ($conn.State -eq [System.Data.ConnectionState]::Open) { $conn.Close() }
|
|
$conn.Dispose()
|
|
}
|
|
}
|
|
return $dbList
|
|
} catch {
|
|
Write-Warning "실제 SQL Server 조회 실패: $($_.Exception.Message) (Mock 데이터로 전환합니다)"
|
|
}
|
|
}
|
|
|
|
return Get-SqlSampleDatabases -Filter $Filter
|
|
}
|
|
|
|
function Invoke-SqlBackupAction {
|
|
param(
|
|
[string]$DatabaseName,
|
|
[string]$BackupType = "FULL",
|
|
[object]$Config = $null
|
|
)
|
|
|
|
$useLive = if ($Config -and $Config.MsSql) { [bool]$Config.MsSql.UseLive } else { $false }
|
|
|
|
if ($useLive) {
|
|
$server = $Config.MsSql.Server
|
|
$auth = $Config.MsSql.AuthType
|
|
$domain = if ($Config.MsSql.Domain) { $Config.MsSql.Domain } else { "" }
|
|
$user = $Config.MsSql.Username
|
|
$pass = $Config.MsSql.Password
|
|
$backupDir = if ($Config.MsSql.BackupDirectory) { $Config.MsSql.BackupDirectory } else { "C:\Backups" }
|
|
|
|
if (-not (Test-Path $backupDir)) {
|
|
New-Item -ItemType Directory -Path $backupDir -Force | Out-Null
|
|
}
|
|
|
|
$timeStamp = (Get-Date).ToString("yyyyMMdd_HHmmss")
|
|
$fileName = "$DatabaseName`_$timeStamp.bak"
|
|
$targetPath = Join-Path $backupDir $fileName
|
|
|
|
try {
|
|
$connStr = Build-SqlConnectionString -Server $server -AuthType $auth -Username $user -Password $pass -Timeout 30
|
|
|
|
$res = Invoke-WithSqlAuthContext -AuthType $auth -Domain $domain -Username $user -Password $pass -Action {
|
|
$conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
|
|
try {
|
|
$conn.Open()
|
|
$cmd = $conn.CreateCommand()
|
|
$cmd.CommandTimeout = 300 # 백업은 5분 타임아웃
|
|
$cmd.CommandText = "BACKUP DATABASE [$DatabaseName] TO DISK = N'$targetPath' WITH INIT, STATS = 10, NAME = N'$DatabaseName-Full Backup'"
|
|
$cmd.ExecuteNonQuery() | Out-Null
|
|
|
|
return [PSCustomObject]@{
|
|
Success = $true
|
|
Database = $DatabaseName
|
|
BackupPath = $targetPath
|
|
Message = "실제 SQL Server 데이터베이스 [$DatabaseName] 백업이 완료되었습니다.`n저장 경로: $targetPath"
|
|
}
|
|
} finally {
|
|
if ($conn.State -eq [System.Data.ConnectionState]::Open) { $conn.Close() }
|
|
$conn.Dispose()
|
|
}
|
|
}
|
|
return $res
|
|
} catch {
|
|
return [PSCustomObject]@{
|
|
Success = $false
|
|
Database = $DatabaseName
|
|
Message = "SQL 백업 실패: $($_.Exception.Message)"
|
|
}
|
|
}
|
|
}
|
|
|
|
return [PSCustomObject]@{
|
|
Success = $true
|
|
Database = $DatabaseName
|
|
BackupPath = "\\backup-nas.corp.local\sqlbackups\$DatabaseName-$((Get-Date).ToString('yyyyMMdd_HHmmss')).bak"
|
|
Message = "[데모 모드] 데이터베이스 [$DatabaseName]의 $BackupType 백업이 가상 완료되었습니다."
|
|
}
|
|
}
|
|
|
|
function Invoke-SqlQuerySnippet {
|
|
param(
|
|
[string]$Query,
|
|
[object]$Config = $null
|
|
)
|
|
|
|
$useLive = if ($Config -and $Config.MsSql) { [bool]$Config.MsSql.UseLive } else { $false }
|
|
|
|
if ($useLive) {
|
|
$server = $Config.MsSql.Server
|
|
$auth = $Config.MsSql.AuthType
|
|
$domain = if ($Config.MsSql.Domain) { $Config.MsSql.Domain } else { "" }
|
|
$user = $Config.MsSql.Username
|
|
$pass = $Config.MsSql.Password
|
|
|
|
try {
|
|
$connStr = Build-SqlConnectionString -Server $server -AuthType $auth -Username $user -Password $pass -Timeout 15
|
|
|
|
$res = Invoke-WithSqlAuthContext -AuthType $auth -Domain $domain -Username $user -Password $pass -Action {
|
|
$conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
|
|
try {
|
|
$conn.Open()
|
|
$cmd = $conn.CreateCommand()
|
|
$cmd.CommandText = $Query
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
|
|
$table = New-Object System.Data.DataTable
|
|
$adapter.Fill($table) | Out-Null
|
|
|
|
$sw = [System.IO.StringWriter]::new()
|
|
$table | Format-Table -AutoSize | Out-String | ForEach-Object { $sw.WriteLine($_) }
|
|
|
|
return [PSCustomObject]@{
|
|
Success = $true
|
|
RowCount = $table.Rows.Count
|
|
Output = "(성공: $($table.Rows.Count)개 행 반환됨 - $((Get-Date).ToString('HH:mm:ss')))`n$($sw.ToString())"
|
|
}
|
|
} finally {
|
|
if ($conn.State -eq [System.Data.ConnectionState]::Open) { $conn.Close() }
|
|
$conn.Dispose()
|
|
}
|
|
}
|
|
return $res
|
|
} catch {
|
|
return [PSCustomObject]@{
|
|
Success = $false
|
|
RowCount = 0
|
|
Output = "(오류 발생)`n$($_.Exception.Message)"
|
|
}
|
|
}
|
|
}
|
|
|
|
return [PSCustomObject]@{
|
|
Success = $true
|
|
RowCount = 10
|
|
Output = "(데모 모드 성공: [$Query] - 10개 행 반환됨 - $((Get-Date).ToString('HH:mm:ss')))`n1. tbl_Users (Row: 14,200)`n2. tbl_AuditLogs (Row: 240,119)`n3. tbl_Orders (Row: 82,900)"
|
|
}
|
|
}
|
|
|
|
function Get-SqlSampleDatabases {
|
|
param([string]$Filter = "")
|
|
|
|
$databases = @(
|
|
[PSCustomObject]@{
|
|
DatabaseName = "ERP_PRODUCTION"
|
|
StateDesc = "ONLINE"
|
|
TotalSizeMB = "56,370 MB"
|
|
FreeSpaceMB = "6,410 MB"
|
|
RecoveryModel = "FULL"
|
|
CollationName = "Korean_Wansung_CI_AS"
|
|
LastBackupDate = "2026-10-20 02:00:15 (Full)"
|
|
IsLive = $false
|
|
},
|
|
[PSCustomObject]@{
|
|
DatabaseName = "HR_PORTAL_DB"
|
|
StateDesc = "ONLINE"
|
|
TotalSizeMB = "12,450 MB"
|
|
FreeSpaceMB = "2,120 MB"
|
|
RecoveryModel = "FULL"
|
|
CollationName = "Korean_Wansung_CI_AS"
|
|
LastBackupDate = "2026-10-20 02:30:00 (Full)"
|
|
IsLive = $false
|
|
},
|
|
[PSCustomObject]@{
|
|
DatabaseName = "WAREHOUSE_ANALYTICS"
|
|
StateDesc = "ONLINE"
|
|
TotalSizeMB = "184,000 MB"
|
|
FreeSpaceMB = "18,900 MB"
|
|
RecoveryModel = "SIMPLE"
|
|
CollationName = "Korean_Wansung_CI_AS"
|
|
LastBackupDate = "2026-10-19 23:00:12 (Full)"
|
|
IsLive = $false
|
|
},
|
|
[PSCustomObject]@{
|
|
DatabaseName = "DEV_STAGING_TEST"
|
|
StateDesc = "ONLINE"
|
|
TotalSizeMB = "4,200 MB"
|
|
FreeSpaceMB = "850 MB"
|
|
RecoveryModel = "SIMPLE"
|
|
CollationName = "Korean_Wansung_CI_AS"
|
|
LastBackupDate = "2026-10-18 10:15:00 (Full)"
|
|
IsLive = $false
|
|
},
|
|
[PSCustomObject]@{
|
|
DatabaseName = "ARCHIVE_2025"
|
|
StateDesc = "READ_ONLY"
|
|
TotalSizeMB = "92,500 MB"
|
|
FreeSpaceMB = "45 MB"
|
|
RecoveryModel = "SIMPLE"
|
|
CollationName = "Korean_Wansung_CI_AS"
|
|
LastBackupDate = "2026-01-01 00:00:00 (Full)"
|
|
IsLive = $false
|
|
}
|
|
)
|
|
|
|
if ([string]::IsNullOrWhiteSpace($Filter)) {
|
|
return ,$databases
|
|
} else {
|
|
$matched = @($databases | Where-Object {
|
|
$_.DatabaseName -like "*$Filter*" -or
|
|
$_.StateDesc -like "*$Filter*"
|
|
})
|
|
return ,$matched
|
|
}
|
|
}
|