xSQLServerHelper.psm1
|
# Set Global Module Verbose $VerbosePreference = 'Continue' # Load Localization Data Import-LocalizedData LocalizedData -filename xSQLServer.strings.psd1 -ErrorAction SilentlyContinue Import-LocalizedData USLocalizedData -filename xSQLServer.strings.psd1 -UICulture en-US -ErrorAction SilentlyContinue function Connect-SQL { [CmdletBinding()] param ( [ValidateNotNull()] [System.String] $SQLServer = $env:COMPUTERNAME, [ValidateNotNull()] [System.String] $SQLInstanceName = "MSSQLSERVER", [ValidateNotNull()] [System.Management.Automation.PSCredential] $SetupCredential ) $null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') if($SQLInstanceName -eq "MSSQLSERVER") { $ConnectSQL = $SQLServer } else { $ConnectSQL = "$SQLServer\$SQLInstanceName" } if ($SetupCredential) { $SQL = New-Object Microsoft.SqlServer.Management.Smo.Server $SQL.ConnectionContext.ConnectAsUser = $true $SQL.ConnectionContext.ConnectAsUserPassword = $SetupCredential.GetNetworkCredential().Password $SQL.ConnectionContext.ConnectAsUserName = $SetupCredential.GetNetworkCredential().UserName $SQL.ConnectionContext.ServerInstance = $ConnectSQL $SQL.ConnectionContext.connect() } else { $SQL = New-Object Microsoft.SqlServer.Management.Smo.Server $ConnectSQL } if($SQL) { New-VerboseMessage -Message "Connected to SQL $ConnectSQL" $SQL } else { Throw -Message "Failed connecting to SQL $ConnectSQL" Exit } } function New-TerminatingError { [CmdletBinding()] [OutputType([System.Management.Automation.ErrorRecord])] param ( [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [String] $ErrorType, [Parameter(Mandatory = $false)] [String[]] $FormatArgs, [Parameter(Mandatory = $false)] [System.Management.Automation.ErrorCategory] $ErrorCategory = [System.Management.Automation.ErrorCategory]::OperationStopped, [Parameter(Mandatory = $false)] [Object] $TargetObject = $null, [Parameter(Mandatory = $false)] [System.Exception] $InnerException = $null ) $errorMessage = $LocalizedData.$ErrorType if(!$errorMessage) { $errorMessage = ($LocalizedData.NoKeyFound -f $ErrorType) if(!$errorMessage) { $errorMessage = ("No Localization key found for key: {0}" -f $ErrorType) } } $errorMessage = ($errorMessage -f $FormatArgs) if( $InnerException ) { $errorMessage += " InnerException: $($InnerException.Message)" } $callStack = Get-PSCallStack # Get Name of calling script if($callStack[1] -and $callStack[1].ScriptName) { $scriptPath = $callStack[1].ScriptName $callingScriptName = $scriptPath.Split('\')[-1].Split('.')[0] $errorId = "$callingScriptName.$ErrorType" } else { $errorId = $ErrorType } Write-Verbose -Message "$($USLocalizedData.$ErrorType -f $FormatArgs) | ErrorType: $errorId" $exception = New-Object System.Exception $errorMessage, $InnerException $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $ErrorCategory, $TargetObject return $errorRecord } function New-VerboseMessage { [CmdletBinding()] [Alias()] [OutputType([String])] Param ( [Parameter(Mandatory=$true)] $Message ) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": $Message"); } function Grant-ServerPerms { [CmdletBinding()] param ( [ValidateNotNull()] [System.String] $SQLServer = $env:COMPUTERNAME, [ValidateNotNull()] [System.String] $SQLInstanceName= "MSSQLSERVER", [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SetupCredential, [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] [System.String] $AuthorizedUser ) if(!$SQL) { $SQL = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -SetupCredential $SetupCredential } Try{ $sps = New-Object Microsoft.SqlServer.Management.Smo.ServerPermissionSet([Microsoft.SqlServer.Management.Smo.ServerPermission]::AlterAnyAvailabilityGroup) $sps.Add([Microsoft.SqlServer.Management.Smo.ServerPermission]::ViewServerState) $SQL.Grant($sps,$AuthorizedUser) New-VerboseMessage -Message "Granted Permissions to $AuthorizedUser" } Catch{ Write-Error "Failed to grant Permissions to $AuthorizedUser." } } function Grant-CNOPerms { [CmdletBinding()] Param ( [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] [System.String] $AvailabilityGroupNameListener, [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] [System.String] $CNO ) #Verify Active Directory Tools are installed, if they are load if not Throw Error If (!(Get-Module -ListAvailable | Where-Object {$_.Name -eq "ActiveDirectory"})){ Throw "Active Directory Module is not installed and is Required." Exit } else{Import-Module ActiveDirectory -ErrorAction Stop -Verbose:$false} Try{ $AG = Get-ADComputer $AvailabilityGroupNameListener $comp = $AG.DistinguishedName # input AD computer distinguishedname $acl = Get-Acl "AD:\$comp" $u = Get-ADComputer $CNO # get the AD user object given full control to computer $SID = [System.Security.Principal.SecurityIdentifier] $u.SID $identity = [System.Security.Principal.IdentityReference] $SID $adRights = [System.DirectoryServices.ActiveDirectoryRights] "GenericAll" $type = [System.Security.AccessControl.AccessControlType] "Allow" $inheritanceType = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All" $ace = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity,$adRights,$type,$inheritanceType $acl.AddAccessRule($ace) Set-Acl -AclObject $acl "AD:\$comp" New-VerboseMessage -Message "Granted privileges on $comp to $CNO" } Catch{ Throw "Failed to grant Permissions on $comp." Exit } } function New-ListenerADObject { [CmdletBinding()] Param ( [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] [System.String] $AvailabilityGroupNameListener, [ValidateNotNull()] [System.String] $SQLServer = $env:COMPUTERNAME, [ValidateNotNull()] [System.String] $SQLInstanceName = "MSSQLSERVER", [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SetupCredential ) if(!$SQL) { $SQL = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -SetupCredential $SetupCredential } $CNO= $SQL.ClusterName #Verify Active Directory Tools are installed, if they are load if not Throw Error If (!(Get-Module -ListAvailable | Where-Object {$_.Name -eq "ActiveDirectory"})){ Throw "Active Directory Module is not installed and is Required." Exit } else{Import-Module ActiveDirectory -ErrorAction Stop -Verbose:$false} try{ $CNO_OU = Get-ADComputer $CNO #Accounts for the comma and CN= at the start of Distinguished Name #We want to remove these plus the ClusterName to get the actual OU Path. $AdditionalChars = 4 $Trim = $CNO.Length+$AdditionalChars $CNOlgth = $CNO_OU.DistinguishedName.Length - $trim $OUPath = $CNO_OU.ToString().Substring($Trim,$CNOlgth) } catch{ Throw ": Failed to find Computer in AD" exit } $m = Get-ADComputer -Filter {Name -eq $AvailabilityGroupNameListener} -Server $env:USERDOMAIN | Select-Object -Property * | Measure-Object If ($m.Count -eq 0) { Try{ #Create Computer Object for the AgListenerName New-ADComputer -Name $AvailabilityGroupNameListener -SamAccountName $AvailabilityGroupNameListener -Path $OUPath -Enabled $false -Credential $SetupCredential New-VerboseMessage -Message "Created Computer Object $AvailabilityGroupNameListener" } Catch{ Throw "Failed to Create $AvailabilityGroupNameListener in $OUPath" Exit } $SucccessChk =0 #Check for AD Object Validate at least three successful attempts $i=1 While ($i -le 5) { Try{ $ListChk = Get-ADComputer -filter {Name -like $AvailabilityGroupNameListener} If ($ListChk){$SuccessChk++} Start-Sleep -Seconds 10 If($SuccesChk -eq 3){break} } Catch{ Throw "Failed Validate $AvailabilityGroupNameListener was created in $OUPath" Exit } $i++ } } Try{ Grant-CNOPerms -AvailabilityGroupNameListener $AvailabilityGroupNameListener -CNO $CNO } Catch{ Throw "Failed Validate grant permissions on $AvailabilityGroupNameListener in location $OUPAth to $CNO" Exit } } function Import-SQLPSModule { [CmdletBinding()] param() <# If SQLPS is not removed between resources (if it was started by another DSC resource) getting objects with the SQL PS provider will fail in some instances because of some sort of inconsistency. Uncertain why this happens. #> if( (Get-Module SQLPS).Count -ne 0 ) { Write-Debug "Unloading SQLPS module." Remove-Module -Name SQLPS -Force -Verbose:$False } Write-Debug "SQLPS module changes CWD to SQLSERVER:\ when loading, pushing location to pop it when module is loaded." Push-Location try { New-VerboseMessage -Message "Importing SQLPS module." Import-Module -Name SQLPS -DisableNameChecking -Verbose:$False -ErrorAction Stop # SQLPS has unapproved verbs, disable checking to ignore Warnings. Write-Debug "SQLPS module imported." } catch { throw New-TerminatingError -ErrorType FailedToImportSQLPSModule -ErrorCategory InvalidOperation -InnerException $_.Exception } finally { Write-Debug "Popping location back to what it was before importing SQLPS module." Pop-Location } } function Get-SQLPSInstanceName { [CmdletBinding()] [OutputType([String])] param ( [Parameter(Mandatory = $true)] [System.String] $InstanceName ) if( $InstanceName -eq "MSSQLSERVER" ) { $InstanceName = "DEFAULT" } return $InstanceName } function Get-SQLPSInstance { [CmdletBinding()] [OutputType([String])] param ( [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter(Mandatory = $true)] [System.String] $NodeName ) $InstanceName = Get-SQLPSInstanceName -InstanceName $InstanceName $Path = "SQLSERVER:\SQL\$NodeName\$InstanceName" New-VerboseMessage -Message "Connecting to $Path as $([System.Security.Principal.WindowsIdentity]::GetCurrent().Name)" Import-SQLPSModule $instance = Get-Item $Path return $instance } function Get-SQLAlwaysOnEndpoint { [CmdletBinding()] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.String] $Name, [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter(Mandatory = $true)] [System.String] $NodeName ) $instance = Get-SQLPSInstance -InstanceName $InstanceName -NodeName $NodeName $Path = "$($instance.PSPath)\Endpoints" Write-Debug "Connecting to $Path as $([System.Security.Principal.WindowsIdentity]::GetCurrent().Name)" [String[]] $presentEndpoint = Get-ChildItem $Path if( $presentEndpoint.Count -ne 0 -and $presentEndpoint.Contains("[$Name]") ) { Write-Debug "Connecting to endpoint $Name as $([System.Security.Principal.WindowsIdentity]::GetCurrent().Name)" $endpoint = Get-Item "$Path\$Name" } else { $endpoint = $null } return $endpoint } |