functions/Invoke-UdeDbQuery.ps1
|
<# .SYNOPSIS Invokes a SQL query against a UDE database using JIT access credentials. .DESCRIPTION Executes SQL with the bundled client using fresh, supplied, or cached JIT credentials. Fresh and supplied credentials stay in memory and need no CredentialManager or SSMS. .PARAMETER Id Cached JIT credential ID. Wildcards must match exactly one entry. .PARAMETER EnvironmentId Environment name or GUID for fresh JIT access, without caching credentials. Supports wildcards. .PARAMETER Role JIT access role for fresh credentials. Writer permits data changes. Default: Reader. .PARAMETER JitCredential Credential object from Get-UdeDbJit. Used in memory without CredentialManager. .PARAMETER Query The SQL query to execute against the environment database. .PARAMETER QueryTimeout The time in seconds to wait for the query to execute before timing out. Defaults to 60. .PARAMETER AsExcelOutput Exports all result properties to an Excel file instead of returning rows. .EXAMPLE PS C:\> Invoke-UdeDbQuery -EnvironmentId "dev-environment" -Query "SELECT DB_NAME() AS DatabaseName" Requests temporary Reader access and queries the environment. It uses -EnvironmentId to request fresh JIT credentials without caching them. It uses the default -Role Reader. It executes the SQL in -Query after the JIT propagation wait. It uses the default -QueryTimeout of 60 seconds. It leaves -AsExcelOutput disabled by default and returns rows. .EXAMPLE PS C:\> $jit = Get-UdeDbJit -EnvironmentId "dev-environment" PS C:\> Invoke-UdeDbQuery -JitCredential $jit -Query "SELECT DB_NAME() AS DatabaseName" Reuses temporary credentials without storing them in the SSMS cache. It uses -EnvironmentId to request the credentials. It passes -JitCredential directly from Get-UdeDbJit. It uses the default -Role Reader when requesting the credentials. It executes the SQL in -Query. It uses the default -QueryTimeout of 60 seconds. It leaves -AsExcelOutput disabled by default and returns rows. .EXAMPLE PS C:\> Invoke-UdeDbQuery -Id "dev-reader" -Query "SELECT TOP (10) name FROM sys.tables ORDER BY name" Runs a query with cached Reader credentials. It uses -Id to select the cached credentials. It uses the cached role; -Role defaults to Reader for fresh access. It executes the SQL in -Query and returns one object per row. It uses the default -QueryTimeout of 60 seconds. It leaves -AsExcelOutput disabled by default and returns rows. .EXAMPLE PS C:\> Invoke-UdeDbQuery -Id "dev-writer" -Query "UPDATE dbo.MyTable SET MyColumn = 1 WHERE Id = 42" Updates rows with cached Writer credentials. It uses -Id to select the cached credentials. It uses the cached role; -Role defaults to Reader for fresh access. It executes -Query and returns RowsAffected when no rows are returned. It uses the default -QueryTimeout of 60 seconds. It leaves -AsExcelOutput disabled by default and returns the result. .EXAMPLE PS C:\> Get-UdeDbJitCache -Id "dev-reader" | Invoke-UdeDbQuery -Query "SELECT * FROM dbo.MyTable" -AsExcelOutput Exports query results to Excel. It uses -Id from the piped cache entry to select credentials. It uses the cached role; -Role defaults to Reader for fresh access. It executes the SQL in -Query. It uses the default -QueryTimeout of 60 seconds. It enables -AsExcelOutput to write all result properties to Excel. .NOTES Author: Mötz Jensen (@Splaxi) #> function Invoke-UdeDbQuery { [CmdletBinding(DefaultParameterSetName = 'Cached', PositionalBinding = $false)] [OutputType([PSCustomObject[]])] param ( [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, ParameterSetName = 'Cached', Position = 0)] [ValidateNotNullOrEmpty()] [string] $Id, [Parameter(Mandatory = $true, ParameterSetName = 'Environment')] [ValidateNotNullOrEmpty()] [string] $EnvironmentId, [Parameter(ParameterSetName = 'Environment')] [ValidateSet('Reader', 'Writer')] [string] $Role = 'Reader', [Parameter(Mandatory = $true, ParameterSetName = 'Credential')] [ValidateNotNull()] [psobject] $JitCredential, [Parameter(Mandatory = $true, Position = 1)] [ValidateNotNullOrEmpty()] [string] $Query, [Parameter(Position = 2)] [int] $QueryTimeout = 60, [switch] $AsExcelOutput ) process { if ($PSCmdlet.ParameterSetName -eq 'Environment') { $colJitCredentials = @(Get-UdeDbJit -EnvironmentId $EnvironmentId ` -Role $Role) if (Test-PSFFunctionInterrupt) { return } if ($colJitCredentials.Count -ne 1) { throw "Expected one JIT credential for '$EnvironmentId'; found $($colJitCredentials.Count)." } $jit = $colJitCredentials[0] $credentialLabel = "environment '$EnvironmentId'" } elseif ($PSCmdlet.ParameterSetName -eq 'Credential') { $jit = $JitCredential $credentialLabel = 'supplied JIT credentials' } else { $colCacheHits = @(Get-UdeDbJitCache -Id $Id ` -ShowPassword) if (Test-PSFFunctionInterrupt) { return } if ($colCacheHits.Count -eq 0) { $messageString = "No cached JIT database access credentials found for Id <c='em'>$Id</c>. Obtain them first using <c='em'>Get-UdeDbJit</c> and store them using <c='em'>Set-UdeDbJitCache -Id `"$Id`"</c>, then try again. List cached credentials using <c='em'>Get-UdeDbJitCache</c>." Write-PSFMessage -Level Important ` -Message $messageString Stop-PSFFunction -Message "Stopping because no cached JIT credentials were found." ` -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) return } if ($colCacheHits.Count -gt 1) { $messageString = "Multiple cached JIT database access credentials match Id <c='em'>$Id</c>. Please be specific and try again. List cached credentials using <c='em'>Get-UdeDbJitCache</c>." Write-PSFMessage -Level Important ` -Message $messageString Stop-PSFFunction -Message "Stopping because multiple cached JIT credentials matched." ` -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) return } $jit = $colCacheHits[0] $credentialLabel = "cached ID '$Id'" } foreach ($itemField in @('Server', 'Database', 'Username', 'Password', 'Expiration')) { if ([string]::IsNullOrWhiteSpace([string]$jit.$itemField)) { throw "The JIT credentials for $credentialLabel are missing $itemField. Obtain fresh credentials using Get-UdeDbJit." } } try { $expiration = [DateTimeOffset]$jit.Expiration } catch { throw "The JIT credentials for $credentialLabel have an invalid expiration. Obtain fresh credentials using Get-UdeDbJit." } if ($expiration -lt [DateTimeOffset]::UtcNow) { $messageString = "The JIT database access credentials for $credentialLabel expired at $($expiration.ToString('o')). Obtain fresh credentials using Get-UdeDbJit and try again." Write-PSFMessage -Level Important ` -Message $messageString Stop-PSFFunction -Message "Stopping because the JIT credentials expired." ` -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) return } $connectionStringBuilder = $script:SqlClientFactory.CreateConnectionStringBuilder() $connectionStringBuilder['Server'] = $jit.Server $connectionStringBuilder['Initial Catalog'] = $jit.Database $connectionStringBuilder['User ID'] = $jit.Username $connectionStringBuilder['Password'] = $jit.Password $connectionStringBuilder['Encrypt'] = $true $connectionStringBuilder['TrustServerCertificate'] = $false $connectionStringBuilder['Connect Timeout'] = 30 $connectionStringBuilder['Application Name'] = 'd365bap.tools' $sqlConnection = $script:SqlClientFactory.CreateConnection() $sqlConnection.ConnectionString = $connectionStringBuilder.ConnectionString $sqlCommand = $sqlConnection.CreateCommand() $sqlCommand.CommandText = $Query $sqlCommand.CommandTimeout = $QueryTimeout $colResults = [Collections.Generic.List[object]]::new() try { $sqlConnection.Open() $reader = $sqlCommand.ExecuteReader() try { $emitted = 0 do { while ($reader.Read() -eq $true) { $tmpProperties = [ordered]@{} for ($i = 0; $i -lt $reader.FieldCount; $i++) { $columnName = $reader.GetName($i) if ([string]::IsNullOrEmpty($columnName)) { $columnName = "Column$($i + 1)" } $uniqueName = $columnName $suffix = 1 while ($tmpProperties.Keys -contains $uniqueName) { $suffix++ $uniqueName = "$($columnName)_$($suffix)" } $value = $reader.GetValue($i) if ($value -is [System.DBNull]) { $value = $null } $tmpProperties[$uniqueName] = $value } $emitted++ $colResults.Add([PSCustomObject]$tmpProperties) } } while ($reader.NextResult() -eq $true) if (($emitted -eq 0) ` -and ($reader.RecordsAffected -ge 0)) { $colResults.Add([PSCustomObject][ordered]@{ RowsAffected = $reader.RecordsAffected }) } } finally { $reader.Close() $reader.Dispose() } } catch { Write-PSFMessage -Level Important ` -Message "Something went wrong while working against the database using JIT access for $credentialLabel" ` -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" ` -Exception $PSItem.Exception return } finally { if ($sqlConnection.State -ne [System.Data.ConnectionState]::Closed) { $sqlConnection.Close() } $sqlCommand.Dispose() $sqlConnection.Dispose() } if ($AsExcelOutput) { $colResults | Export-Excel -WorksheetName "Invoke-UdeDbQuery" return } $colResults } } |