Public/Test-OracleConnection.ps1
|
function Test-OracleConnection { <# .SYNOPSIS Test Oracle database connection .DESCRIPTION Check that an Oracle database connection is working. .PARAMETER Hostname The host name parameter corresponds to the name of the database host. .PARAMETER PortNumber The port number parameter corresponds to the port number of the database server. .PARAMETER ServiceName The service name parameter corresponds to the name of the database service. .PARAMETER Credentials The credentials parameter corresponds to the credentials of accoun to use in case of SQL authentication. .PARAMETER Username The username parameter corresponds to the username of the account to use in case of SQL authentication. .PARAMETER Password The password parameter corresponds to the password of the account to use in case of SQL authentication. .PARAMETER TimeOut The optional time-out parameter corresponds to the time in seconds before the connection is deemed unresponsive. The default value is 3 seconds. .INPUTS None. You cannot pipe objects to Test-OracleConnection. .OUTPUTS Boolean. Test-OracleConnection returns a boolean depending on the result of the connection attempt. .NOTES File name: Test-OracleConnection.ps1 Author: Florian Carrier Creation date: 2020-02-03 Last modified: 2020-02-04 Dependencies: Test-OracleConnection requires Oracle Data Provider for .NET .LINK https://www.powershellgallery.com/packages/PSTK .LINK https://www.oracle.com/database/technologies/appdev/dotnet/odp.html .LINK https://www.nuget.org/packages/Oracle.ManagedDataAccess.Core #> [CmdletBinding ( SupportsShouldProcess = $true )] Param ( [Parameter ( Position = 1, Mandatory = $true, HelpMessage = "Name of the database host" )] [ValidateNotNullOrEmpty ()] [Alias ("Server")] [System.String] $Hostname, [Parameter ( Position = 2, Mandatory = $true, HelpMessage = "Database server port number" )] [ValidateNotNullOrEmpty ()] [System.String] $PortNumber, [Parameter ( Position = 3, Mandatory = $true, HelpMessage = "Name of the Oracle service" )] [ValidateNotNullOrEmpty ()] [System.String] $ServiceName, [Parameter ( Position = 4, Mandatory = $false, HelpMessage = "Database user credentials", ParameterSetName = "Credentials" )] [ValidateNotNullOrEmpty ()] [System.Management.Automation.PSCredential] $Credentials, [Parameter ( Position = 4, Mandatory = $false, HelpMessage = "User name", ParameterSetName = "UserPassword" )] [ValidateNotNullOrEmpty ()] [Alias ("Name")] [System.String] $Username, [Parameter ( Position = 5, Mandatory = $false, HelpMessage = "Password", ParameterSetName = "UserPassword" )] [ValidateNotNullOrEmpty ()] [Alias ("Pw")] [System.String] $Password, [Parameter ( Position = 5, Mandatory = $false, HelpMessage = "Connection timeout (in seconds)", ParameterSetName = "Credentials" )] [Parameter ( Position = 6, Mandatory = $false, HelpMessage = "Connection timeout (in seconds)", ParameterSetName = "UserPassword" )] [ValidateNotNullOrEmpty ()] [System.Int32] $TimeOut = 3 ) Begin { # Get global preference variables Get-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState } Process { # Define connection string $ConnectionString = "Data Source='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$Hostname)(PORT=$PortNumber))(CONNECT_DATA=(SERVICE_NAME=$ServiceName)))';" # Check authentication mode if ($PSBoundParameters.ContainsKey("Credentials")) { # If "secured" credentials are provided $ConnectionString = $ConnectionString + "User ID=$($Credentials.Username);Password=$($Credentials.GetNetworkCredential().Password);" $SensitiveData = $Credentials.GetNetworkCredential().Password } elseif ($PSBoundParameters.ContainsKey("Username") -And $PSBoundParameters.ContainsKey("Password")) { # If plain text credentials are provided if ($Username) { $ConnectionString = $ConnectionString + "User ID=$Username;Password=$Password;" $SensitiveData = $Password } else { Write-Log -Type "ERROR" -Message "Invalid username ""$Username""" -ExitCode 1 } } else { # Else default to integrated security (Windows authentication) Write-Log -Type "DEBUG" -Message "Using Integrated Security" $ConnectionString = $ConnectionString + "Integrated Security=True;" } # Technical parameters (Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;) $ConnectionString = $ConnectionString + "Connection Timeout=$TimeOut;" # Create connection object Write-Log -Type "DEBUG" -Object $ConnectionString -Obfuscate $SensitiveData $Connection = New-Object -TypeName "Oracle.ManagedDataAccess.Client.OracleConnection" -ArgumentList $ConnectionString # Try to open the connection try { $Connection.Open() $Connection.Close() return $true } catch { # If connection fails return $false } } } |