New-WEMDatabaseConnection.ps1
|
<#
.Synopsis Sets up a database connection. .Description Sets up a database connection. .Link https://msfreaks.wordpress.com .Parameter Server .. .Parameter Database .. .Parameter Credential .. .Example .Notes Author: Arjan Mensch #> function New-WEMDatabaseConnection { [CmdletBinding()] param( [Parameter(Mandatory=$True)][string]$Server, [Parameter(Mandatory=$True)][string]$Database, [Parameter(Mandatory=$False)][PSCredential]$Credential = [PSCredential]::Empty ) $ConnectionString = $null if ($Credential -ne [PSCredential]::Empty) { Write-Verbose "Credential provided. Setting up connection using credentials." $ConnectionString = "Server=$Server;Database=$Database;User Id=$($Credential.UserName);Password=$($Credential.GetNetworkCredential().password);" } else { Write-Verbose "No credential provided. Setting up connection using Integrated Security." $ConnectionString = "Server=$($Server);Database=$($Database);Trusted_Connection=True" #$ConnectionString = "Data Source=$Server; Integrated Security=SSPI; Initial Catalog=$Database" } Write-Verbose "Connection string: $($ConnectionString)" $connection = New-Object -TypeName "System.Data.SqlClient.SqlConnection" $ConnectionString # grab database version $SQLQuery = "SELECT value FROM VUEMParameters WHERE IdSite = 1 AND Name = 'VersionInfo'" $result = Invoke-SQL -Connection $connection -Query $SQLQuery $script:databaseVersion = $result.Tables.Rows.value $script:databaseSchema = $script:databaseVersion.Substring(0, $script:databaseVersion.IndexOf(".")) # 1903.0.1.1, 1906.0.1.1, 1909.1.0.1, 1912.1.0.1 Write-Verbose "Database version $($script:databaseVersion) detected (schema $($script:databaseSchema))" if (-not $configurationSettings[$script:databaseSchema]) { Write-Warning "Connected to a database version with unsupported schema: $($script:databaseVersion) (schema $($script:databaseSchema))`nLatest supported schema version for this module: $($configurationSettings.Keys | Sort-Object -Descending | Select-Object -First 1)`n`nFull functionality is not guaranteed`nFalling back to latest supported schema version" $script:databaseSchema = ($configurationSettings.Keys | Sort-Object -Descending | Select-Object -First 1) } return $connection } |