SqlServerDsc.psm1
|
#Region '.\prefix.ps1' 0 using module .\Modules\DscResource.Base $script:dscResourceCommonModulePath = Join-Path -Path $PSScriptRoot -ChildPath 'Modules/DscResource.Common' Import-Module -Name $script:dscResourceCommonModulePath # TODO: The goal would be to remove this, when no classes and public or private functions need it. $script:sqlServerDscCommonModulePath = Join-Path -Path $PSScriptRoot -ChildPath 'Modules/SqlServerDsc.Common' Import-Module -Name $script:sqlServerDscCommonModulePath $script:localizedData = Get-LocalizedData -DefaultUICulture 'en-US' #EndRegion '.\prefix.ps1' 11 #Region '.\Classes\002.DatabasePermission.ps1' 0 <# .SYNOPSIS The possible database permission states. .PARAMETER State The state of the permission. .PARAMETER Permission The permissions to be granted or denied for the user in the database. .NOTES The DSC properties specifies the attribute Mandatory but State was meant to be attribute Key, but those attributes are not honored correctly during compilation in the current implementation of PowerShell DSC. If the attribute would have been left as Key then it would not have been possible to add an identical instance of DatabasePermission in two separate DSC resource instances in a DSC configuration. The Key property only works on the top level DSC properties. E.g. two resources instances of SqlDatabasePermission in a DSC configuration trying to grant the database permission 'connect' in two separate databases would have failed compilation as a the property State would have been seen as "duplicate resource". Since it is not possible to use the attribute Key the State property is evaluate during runtime so that no two states are enforcing the same permission. The method Equals() returns $false if type is not the same on both sides of the comparison. There was a thought to throw an exception if the object being compared was of another type, but since there was issues with using for example [ServerPermission[]], it was left out. This can be the correct way since if moving for example [ServerPermission[]] to the left side and the for example [ServerPermission] to the right side, then the left side array is filtered with the matching values on the right side. This is the normal behavior for other types. #> class DatabasePermission : IComparable, System.IEquatable[Object] { [DscProperty(Mandatory)] [ValidateSet('Grant', 'GrantWithGrant', 'Deny')] [System.String] $State [DscProperty(Mandatory)] [AllowEmptyCollection()] [ValidateSet( 'Alter', 'AlterAnyAsymmetricKey', 'AlterAnyApplicationRole', 'AlterAnyAssembly', 'AlterAnyCertificate', 'AlterAnyDatabaseAudit', 'AlterAnyDataspace', 'AlterAnyDatabaseEventNotification', 'AlterAnyExternalDataSource', 'AlterAnyExternalFileFormat', 'AlterAnyFulltextCatalog', 'AlterAnyMask', 'AlterAnyMessageType', 'AlterAnyRole', 'AlterAnyRoute', 'AlterAnyRemoteServiceBinding', 'AlterAnyContract', 'AlterAnySymmetricKey', 'AlterAnySchema', 'AlterAnySecurityPolicy', 'AlterAnyService', 'AlterAnyDatabaseDdlTrigger', 'AlterAnyUser', 'Authenticate', 'BackupDatabase', 'BackupLog', 'Control', 'Connect', 'ConnectReplication', 'Checkpoint', 'CreateAggregate', 'CreateAsymmetricKey', 'CreateAssembly', 'CreateCertificate', 'CreateDatabase', 'CreateDefault', 'CreateDatabaseDdlEventNotification', 'CreateFunction', 'CreateFulltextCatalog', 'CreateMessageType', 'CreateProcedure', 'CreateQueue', 'CreateRole', 'CreateRoute', 'CreateRule', 'CreateRemoteServiceBinding', 'CreateContract', 'CreateSymmetricKey', 'CreateSchema', 'CreateSynonym', 'CreateService', 'CreateTable', 'CreateType', 'CreateView', 'CreateXmlSchemaCollection', 'Delete', 'Execute', 'Insert', 'References', 'Select', 'Showplan', 'SubscribeQueryNotifications', 'TakeOwnership', 'Unmask', 'Update', 'ViewDefinition', 'ViewDatabaseState' )] [System.String[]] $Permission [System.Boolean] Equals([System.Object] $object) { $isEqual = $false if ($object -is $this.GetType()) { if ($this.Grant -eq $object.Grant) { if (-not (Compare-Object -ReferenceObject $this.Permission -DifferenceObject $object.Permission)) { $isEqual = $true } } } return $isEqual } [System.Int32] CompareTo([Object] $object) { [System.Int32] $returnValue = 0 if ($null -eq $object) { return 1 } if ($object -is $this.GetType()) { <# Less than zero - The current instance precedes the object specified by the CompareTo method in the sort order. Zero - This current instance occurs in the same position in the sort order as the object specified by the CompareTo method. Greater than zero - This current instance follows the object specified by the CompareTo method in the sort order. #> $returnValue = 0 # Order objects in the order 'Grant', 'GrantWithGrant', 'Deny'. switch ($this.State) { 'Grant' { if ($object.State -in @('GrantWithGrant', 'Deny')) { # This current instance precedes $object $returnValue = -1 } } 'GrantWithGrant' { if ($object.State -in @('Grant')) { # This current instance follows $object $returnValue = 1 } if ($object.State -in @('Deny')) { # This current instance precedes $object $returnValue = -1 } } 'Deny' { if ($object.State -in @('Grant', 'GrantWithGrant')) { # This current instance follows $object $returnValue = 1 } } } } else { $errorMessage = $script:localizedData.InvalidTypeForCompare -f @( $this.GetType().FullName, $object.GetType().FullName ) New-InvalidArgumentException -ArgumentName 'Object' -Message $errorMessage } return $returnValue } } #EndRegion '.\Classes\002.DatabasePermission.ps1' 206 #Region '.\Classes\002.ServerPermission.ps1' 0 <# .SYNOPSIS The possible server permission states. .PARAMETER State The state of the permission. .PARAMETER Permission The permissions to be granted or denied for the user in the database. .NOTES The DSC properties specifies the attribute Mandatory but State was meant to be attribute Key, but those attributes are not honored correctly during compilation in the current implementation of PowerShell DSC. If the attribute would have been left as Key then it would not have been possible to add an identical instance of ServerPermission in two separate DSC resource instances in a DSC configuration. The Key property only works on the top level DSC properties. E.g. two resources instances of SqlPermission in a DSC configuration trying to grant the database permission 'AlterAnyDatabase' in two separate databases would have failed compilation as a the property State would have been seen as "duplicate resource". Since it is not possible to use the attribute Key the State property is evaluate during runtime so that no two states are enforcing the same permission. The method Equals() returns $false if type is not the same on both sides of the comparison. There was a thought to throw an exception if the object being compared was of another type, but since there was issues with using for example [ServerPermission[]], it was left out. This can be the correct way since if moving for example [ServerPermission[]] to the left side and the for example [ServerPermission] to the right side, then the left side array is filtered with the matching values on the right side. This is the normal behavior for other types. #> class ServerPermission : IComparable, System.IEquatable[Object] { [DscProperty(Mandatory)] [ValidateSet('Grant', 'GrantWithGrant', 'Deny')] [System.String] $State [DscProperty(Mandatory)] [AllowEmptyCollection()] [ValidateSet( 'AdministerBulkOperations', 'AlterAnyServerAudit', 'AlterAnyCredential', 'AlterAnyConnection', 'AlterAnyDatabase', 'AlterAnyEventNotification', 'AlterAnyEndpoint', 'AlterAnyLogin', 'AlterAnyLinkedServer', 'AlterResources', 'AlterServerState', 'AlterSettings', 'AlterTrace', 'AuthenticateServer', 'ControlServer', 'ConnectSql', 'CreateAnyDatabase', 'CreateDdlEventNotification', 'CreateEndpoint', 'CreateTraceEventNotification', 'Shutdown', 'ViewAnyDefinition', 'ViewAnyDatabase', 'ViewServerState', 'ExternalAccessAssembly', 'UnsafeAssembly', 'AlterAnyServerRole', 'CreateServerRole', 'AlterAnyAvailabilityGroup', 'CreateAvailabilityGroup', 'AlterAnyEventSession', 'SelectAllUserSecurables', 'ConnectAnyDatabase', 'ImpersonateAnyLogin' )] [System.String[]] $Permission <# TODO: It was not possible to move this to a parent class. But since these are generic functions for DatabasePermission and ServerPermission we could make this a private function. #> [System.Boolean] Equals([System.Object] $object) { $isEqual = $false if ($object -is $this.GetType()) { if ($this.Grant -eq $object.Grant) { if (-not (Compare-Object -ReferenceObject $this.Permission -DifferenceObject $object.Permission)) { $isEqual = $true } } } return $isEqual } <# TODO: It was not possible to move this to a parent class. But since these are generic functions for DatabasePermission and ServerPermission we could make this a private function. #> [System.Int32] CompareTo([Object] $object) { [System.Int32] $returnValue = 0 if ($null -eq $object) { return 1 } if ($object -is $this.GetType()) { <# Less than zero - The current instance precedes the object specified by the CompareTo method in the sort order. Zero - This current instance occurs in the same position in the sort order as the object specified by the CompareTo method. Greater than zero - This current instance follows the object specified by the CompareTo method in the sort order. #> $returnValue = 0 # Order objects in the order 'Grant', 'GrantWithGrant', 'Deny'. switch ($this.State) { 'Grant' { if ($object.State -in @('GrantWithGrant', 'Deny')) { # This current instance precedes $object $returnValue = -1 } } 'GrantWithGrant' { if ($object.State -in @('Grant')) { # This current instance follows $object $returnValue = 1 } if ($object.State -in @('Deny')) { # This current instance precedes $object $returnValue = -1 } } 'Deny' { if ($object.State -in @('Grant', 'GrantWithGrant')) { # This current instance follows $object $returnValue = 1 } } } } else { $errorMessage = $script:localizedData.InvalidTypeForCompare -f @( $this.GetType().FullName, $object.GetType().FullName ) New-InvalidArgumentException -ArgumentName 'Object' -Message $errorMessage } return $returnValue } } #EndRegion '.\Classes\002.ServerPermission.ps1' 183 #Region '.\Classes\011.SqlResourceBase.ps1' 0 <# .SYNOPSIS The SqlResource base have generic properties and methods for the class-based resources. .PARAMETER InstanceName The name of the _SQL Server_ instance to be configured. Default value is `'MSSQLSERVER'`. .PARAMETER ServerName The host name of the _SQL Server_ to be configured. Default value is the current computer name. .PARAMETER Credential Specifies the credential to use to connect to the _SQL Server_ instance. If parameter **Credential'* is not provided then the resource instance is run using the credential that runs the configuration. .PARAMETER Reasons Returns the reason a property is not in desired state. #> class SqlResourceBase : ResourceBase { <# Property for holding the server connection object. This should be an object of type [Microsoft.SqlServer.Management.Smo.Server] but using that type fails the build process currently. See issue https://github.com/dsccommunity/DscResource.DocGenerator/issues/121. #> hidden [System.Object] $SqlServerObject [DscProperty(Key)] [System.String] $InstanceName [DscProperty()] [System.String] $ServerName = (Get-ComputerName) [DscProperty()] [PSCredential] $Credential [DscProperty(NotConfigurable)] [Reason[]] $Reasons # Passing the module's base directory to the base constructor. SqlResourceBase () : base ($PSScriptRoot) { $this.SqlServerObject = $null } <# Returns and reuses the server connection object. If the server connection object does not exist a connection to the SQL Server instance will occur. This should return an object of type [Microsoft.SqlServer.Management.Smo.Server] but using that type fails the build process currently. See issue https://github.com/dsccommunity/DscResource.DocGenerator/issues/121. #> hidden [System.Object] GetServerObject() { if (-not $this.SqlServerObject) { $connectSqlDscDatabaseEngineParameters = @{ ServerName = $this.ServerName InstanceName = $this.InstanceName ErrorAction = 'Stop' } if ($this.Credential) { $connectSqlDscDatabaseEngineParameters.Credential = $this.Credential } $this.SqlServerObject = Connect-SqlDscDatabaseEngine @connectSqlDscDatabaseEngineParameters } return $this.SqlServerObject } } #EndRegion '.\Classes\011.SqlResourceBase.ps1' 84 #Region '.\Classes\020.SqlAudit.ps1' 0 <# .SYNOPSIS The `SqlAudit` DSC resource is used to create, modify, or remove server audits. .DESCRIPTION The `SqlAudit` DSC resource is used to create, modify, or remove server audits. The built-in parameter **PSDscRunAsCredential** can be used to run the resource as another user. The resource will then authenticate to the SQL Server instance as that user. It also possible to instead use impersonation by the parameter **Credential**. ## Requirements * Target machine must be running Windows Server 2012 or later. * Target machine must be running SQL Server Database Engine 2012 or later. * Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module. ## Known issues All issues are not listed here, see [here for all open issues](https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlAudit). ### Property **Reasons** does not work with **PSDscRunAsCredential** When using the built-in parameter **PSDscRunAsCredential** the read-only property **Reasons** will return empty values for the properties **Code** and **Phrase. The built-in property **PSDscRunAsCredential** does not work together with class-based resources that using advanced type like the parameter **Reasons** have. ### Using **Credential** property SQL Authentication and Group Managed Service Accounts is not supported as impersonation credentials. Currently only Windows Integrated Security is supported to use as credentials. For Windows Authentication the username must either be provided with the User Principal Name (UPN), e.g. `username@domain.local` or if using non-domain (for example a local Windows Server account) account the username must be provided without the NetBIOS name, e.g. `username`. Using the NetBIOS name, e.g using the format `DOMAIN\username` will not work. See more information in [Credential Overview](https://github.com/dsccommunity/SqlServerDsc/wiki/CredentialOverview). .PARAMETER Name The name of the audit. .PARAMETER LogType Specifies the to which log an audit logs to. Mutually exclusive to parameter **Path**. .PARAMETER Path Specifies the destination path for a file audit. Mutually exclusive to parameter **LogType**. .PARAMETER Filter Specifies the filter that should be used on the audit. .PARAMETER MaximumFiles Specifies the number of files on disk. Mutually exclusive to parameter **MaximumRolloverFiles**. Mutually exclusive to parameter **LogType**. .PARAMETER MaximumFileSize Specifies the maximum file size in units by parameter **MaximumFileSizeUnit**. If this is specified the parameter **MaximumFileSizeUnit** must also be specified. Mutually exclusive to parameter **LogType**. Minimum allowed value is 2 (MB). It also allowed to set the value to 0 which mean unlimited file size. .PARAMETER MaximumFileSizeUnit Specifies the unit that is used for the file size. If this is specified the parameter **MaximumFileSize** must also be specified. Mutually exclusive to parameter **LogType**. .PARAMETER MaximumRolloverFiles Specifies the amount of files on disk before SQL Server starts reusing the files. Mutually exclusive to parameter **MaximumFiles** and **LogType**. .PARAMETER OnFailure Specifies what should happen when writing events to the store fails. This can be `Continue`, `FailOperation`, or `Shutdown`. .PARAMETER QueueDelay Specifies the maximum delay before a event is written to the store. When set to low this could impact server performance. When set to high events could be missing when a server crashes. .PARAMETER ReserveDiskSpace Specifies if the needed file space should be reserved. only needed when writing to a file log. Mutually exclusive to parameter **LogType**. .PARAMETER Enabled Specifies if the audit should be enabled. Defaults to `$false`. .PARAMETER Ensure Specifies if the server audit should be present or absent. If set to `Present` the audit will be added if it does not exist, or updated if the audit exist. If `Absent` then the audit will be removed from the server. Defaults to `Present`. .PARAMETER Force Specifies if it is allowed to re-create the server audit if a current audit exist with the same name but of a different audit type. Defaults to `$false` not allowing server audits to be re-created. .EXAMPLE Invoke-DscResource -ModuleName SqlServerDsc -Name SqlAudit -Method Get -Property @{ ServerName = 'localhost' InstanceName = 'SQL2017' Credential = (Get-Credential -UserName 'myuser@company.local' -Message 'Password:') Name = 'Log1' } This example shows how to call the resource using Invoke-DscResource. #> [DscResource(RunAsCredential = 'Optional')] class SqlAudit : SqlResourceBase { [DscProperty(Key)] [System.String] $Name [DscProperty()] [ValidateSet('SecurityLog', 'ApplicationLog')] [System.String] $LogType # The Path is evaluated if exist in AssertProperties(). [DscProperty()] [System.String] $Path [DscProperty()] [System.String] $AuditFilter [DscProperty()] [Nullable[System.UInt32]] $MaximumFiles [DscProperty()] # There is an extra evaluation in AssertProperties() for this range. [ValidateRange(0, 2147483647)] [Nullable[System.UInt32]] $MaximumFileSize [DscProperty()] [ValidateSet('Megabyte', 'Gigabyte', 'Terabyte')] [System.String] $MaximumFileSizeUnit [DscProperty()] [ValidateRange(0, 2147483647)] [Nullable[System.UInt32]] $MaximumRolloverFiles [DscProperty()] [ValidateSet('Continue', 'FailOperation', 'Shutdown')] [System.String] $OnFailure [DscProperty()] # There is an extra evaluation in AssertProperties() for this range. [ValidateRange(0, 2147483647)] [Nullable[System.UInt32]] $QueueDelay [DscProperty()] [ValidatePattern('^[0-9a-fA-F]{8}-(?:[0-9a-fA-F]{4}-){3}[0-9a-fA-F]{12}$')] [System.String] $AuditGuid [DscProperty()] [Nullable[System.Boolean]] $ReserveDiskSpace [DscProperty()] [Nullable[System.Boolean]] $Enabled [DscProperty()] [Ensure] $Ensure = [Ensure]::Present [DscProperty()] [Nullable[System.Boolean]] $Force SqlAudit () : base () { # These properties will not be enforced. $this.ExcludeDscProperties = @( 'ServerName' 'InstanceName' 'Name' 'Credential' 'Force' ) } [SqlAudit] Get() { # Call the base method to return the properties. return ([ResourceBase] $this).Get() } [System.Boolean] Test() { # Call the base method to test all of the properties that should be enforced. return ([ResourceBase] $this).Test() } [void] Set() { # Call the base method to enforce the properties. ([ResourceBase] $this).Set() } <# Base method Get() call this method to get the current state as a hashtable. The parameter properties will contain the key properties. #> hidden [System.Collections.Hashtable] GetCurrentState([System.Collections.Hashtable] $properties) { Write-Verbose -Message ( $this.localizedData.EvaluateServerAudit -f @( $properties.Name, $properties.InstanceName ) ) $currentStateCredential = $null if ($this.Credential) { <# This does not work, even if username is set, the method Get() will return an empty PSCredential-object. Kept it here so it at least return a Credential object. #> $currentStateCredential = [PSCredential]::new( $this.Credential.UserName, [SecureString]::new() ) } <# Only set key property Name if the audit exist. Base class will set it and handle Ensure. #> $currentState = @{ Credential = $currentStateCredential InstanceName = $properties.InstanceName ServerName = $this.ServerName Force = $this.Force } $serverObject = $this.GetServerObject() $auditObject = $serverObject | Get-SqlDscAudit -Name $properties.Name -ErrorAction 'SilentlyContinue' if ($auditObject) { $currentState.Name = $properties.Name if ($auditObject.DestinationType -in @('ApplicationLog', 'SecurityLog')) { $currentState.LogType = $auditObject.DestinationType } if ($auditObject.FilePath) { # Remove trailing slash or backslash. $currentState.Path = $auditObject.FilePath -replace '[\\|/]*$' } $currentState.AuditFilter = $auditObject.Filter $currentState.MaximumFiles = [System.UInt32] $auditObject.MaximumFiles $currentState.MaximumFileSize = [System.UInt32] $auditObject.MaximumFileSize # The value of MaximumFileSizeUnit can be zero, so have to check against $null if ($null -ne $auditObject.MaximumFileSizeUnit) { $convertedMaximumFileSizeUnit = ( @{ 0 = 'Megabyte' 1 = 'Gigabyte' 2 = 'Terabyte' } ).($auditObject.MaximumFileSizeUnit.value__) $currentState.MaximumFileSizeUnit = $convertedMaximumFileSizeUnit } $currentState.MaximumRolloverFiles = [System.UInt32] $auditObject.MaximumRolloverFiles $currentState.OnFailure = $auditObject.OnFailure $currentState.QueueDelay = [System.UInt32] $auditObject.QueueDelay $currentState.AuditGuid = $auditObject.Guid $currentState.ReserveDiskSpace = $auditObject.ReserveDiskSpace $currentState.Enabled = $auditObject.Enabled } return $currentState } <# Base method Set() call this method with the properties that should be enforced are not in desired state. It is not called if all properties are in desired state. The variable $properties contain the properties that are not in desired state. #> hidden [void] Modify([System.Collections.Hashtable] $properties) { $serverObject = $this.GetServerObject() $auditObject = $null if ($properties.Keys -contains 'Ensure') { # Evaluate the desired state for property Ensure. switch ($properties.Ensure) { 'Present' { # Create the audit since it was missing. Always created disabled. $auditObject = $this.CreateAudit() } 'Absent' { # Remove the audit since it was present $serverObject | Remove-SqlDscAudit -Name $this.Name -Force } } } else { <# Update any properties not in desired state if the audit should be present. At this point it is assumed the audit exist since Ensure property was in desired state. If the desired state happens to be Absent then ignore any properties not in desired state (user have in that case wrongly added properties to an "absent configuration"). #> if ($this.Ensure -eq [Ensure]::Present) { $auditObject = $serverObject | Get-SqlDscAudit -Name $this.Name -ErrorAction 'Stop' if ($auditObject) { $auditIsWrongType = ( # If $auditObject.DestinationType is not null. $null -ne $auditObject.DestinationType -and ( # Path is not in desired state but the audit is not of type File. $properties.ContainsKey('Path') -and $auditObject.DestinationType -ne 'File' ) -or ( # LogType is not in desired state but the audit is of type File. $properties.ContainsKey('LogType') -and $auditObject.DestinationType -eq 'File' ) ) # Does the audit need to be re-created? if ($auditIsWrongType) { if ($this.Force -eq $true) { $auditObject | Remove-SqlDscAudit -Force $auditObject = $this.CreateAudit() } else { New-InvalidOperationException -Message $this.localizedData.AuditIsWrongType } } else { <# Should evaluate DestinationType so that is does not try to set a File audit property when audit type is of a Log-type. #> if ($null -ne $auditObject.DestinationType -and $auditObject.DestinationType -ne 'File') { # Look for file audit properties not in desired state $fileAuditProperty = $properties.Keys.Where({ $_ -in @( 'Path' 'MaximumFiles' 'MaximumFileSize' 'MaximumFileSizeUnit' 'MaximumRolloverFiles' 'ReserveDiskSpace' ) }) # If a property was found, throw an exception. if ($fileAuditProperty.Count -gt 0) { New-InvalidOperationException -Message ($this.localizedData.AuditOfWrongTypeForUseWithProperty -f $auditObject.DestinationType) } } # Get all optional properties that has an assigned value. $assignedOptionalDscProperties = $this | Get-DscProperty -HasValue -Attribute 'Optional' -ExcludeName @( # Remove optional properties that is not an audit property. 'ServerName' 'Ensure' 'Force' 'Credential' # Remove this audit property since it must be handled later. 'Enabled' ) <# Only call Set when there is a property to Set. The property Enabled was ignored, so it could be the only one that was not in desired state (Enabled is handled later). #> if ($assignedOptionalDscProperties.Count -gt 0) { <# This calls Set-SqlDscAudit to set all the desired value even if they were in desired state. Then the no logic is needed to make sure we call using the correct parameter set that Set-SqlDscAudit requires. #> $auditObject | Set-SqlDscAudit @assignedOptionalDscProperties -Force } } } } } <# If there is an audit object either from a newly created or fetched from current state, and if the desired state is Present, evaluate if the audit should be enable or disable. #> if ($auditObject -and $this.Ensure -eq [Ensure]::Present -and $properties.Keys -contains 'Enabled') { switch ($properties.Enabled) { $true { $serverObject | Enable-SqlDscAudit -Name $this.Name -Force } $false { $serverObject | Disable-SqlDscAudit -Name $this.Name -Force } } } } <# Base method Assert() call this method with the properties that was assigned a value. #> hidden [void] AssertProperties([System.Collections.Hashtable] $properties) { # The properties MaximumFiles and MaximumRolloverFiles are mutually exclusive. $assertBoundParameterParameters = @{ BoundParameterList = $properties MutuallyExclusiveList1 = @( 'MaximumFiles' ) MutuallyExclusiveList2 = @( 'MaximumRolloverFiles' ) } Assert-BoundParameter @assertBoundParameterParameters # LogType is mutually exclusive from any of the File audit properties. $assertBoundParameterParameters = @{ BoundParameterList = $properties MutuallyExclusiveList1 = @( 'LogType' ) MutuallyExclusiveList2 = @( 'Path' 'MaximumFiles' 'MaximumFileSize' 'MaximumFileSizeUnit' 'MaximumRolloverFiles' 'ReserveDiskSpace' ) } Assert-BoundParameter @assertBoundParameterParameters # Get all assigned *FileSize properties. $assignedSizeProperty = $properties.Keys.Where({ $_ -in @( 'MaximumFileSize', 'MaximumFileSizeUnit' ) }) <# Neither or both of the properties MaximumFileSize and MaximumFileSizeUnit must be assigned. #> if ($assignedSizeProperty.Count -eq 1) { $errorMessage = $this.localizedData.BothFileSizePropertiesMustBeSet New-InvalidArgumentException -ArgumentName 'MaximumFileSize, MaximumFileSizeUnit' -Message $errorMessage } <# Since we cannot use [ValidateScript()], and it is no possible to exclude a value in the [ValidateRange()], evaluate so 1 is not assigned. #> if ($properties.Keys -contains 'MaximumFileSize' -and $properties.MaximumFileSize -eq 1) { $errorMessage = $this.localizedData.MaximumFileSizeValueInvalid New-InvalidArgumentException -ArgumentName 'MaximumFileSize' -Message $errorMessage } <# Since we cannot use [ValidateScript()], and it is no possible to exclude a value in the [ValidateRange()], evaluate so 1-999 is not assigned. #> if ($properties.Keys -contains 'QueueDelay' -and $properties.QueueDelay -in 1..999) { $errorMessage = $this.localizedData.QueueDelayValueInvalid New-InvalidArgumentException -ArgumentName 'QueueDelay' -Message $errorMessage } # ReserveDiskSpace can only be used with MaximumFiles. if ($properties.Keys -contains 'ReserveDiskSpace' -and $properties.Keys -notcontains 'MaximumFiles') { $errorMessage = $this.localizedData.BothFileSizePropertiesMustBeSet New-InvalidArgumentException -ArgumentName 'ReserveDiskSpace' -Message $errorMessage } # Test so that the path exist. if ($properties.Keys -contains 'Path' -and -not (Test-Path -Path $properties.Path)) { $errorMessage = $this.localizedData.PathInvalid -f $properties.Path New-InvalidArgumentException -ArgumentName 'Path' -Message $errorMessage } } <# Create and returns the desired audit object. Always created disabled. This should return an object of type [Microsoft.SqlServer.Management.Smo.Audit] but using that type fails the build process currently. See issue https://github.com/dsccommunity/DscResource.DocGenerator/issues/121. #> hidden [System.Object] CreateAudit() { # Get all properties that has an assigned value. $assignedDscProperties = $this | Get-DscProperty -HasValue -Attribute @( 'Key' 'Optional' ) -ExcludeName @( # Remove properties that is not an audit property. 'InstanceName' 'ServerName' 'Ensure' 'Force' 'Credential' # Remove this audit property since it must be handled later. 'Enabled' ) if ($assignedDscProperties.Keys -notcontains 'LogType' -and $assignedDscProperties.Keys -notcontains 'Path') { New-InvalidOperationException -Message $this.localizedData.CannotCreateNewAudit } $serverObject = $this.GetServerObject() $auditObject = $serverObject | New-SqlDscAudit @assignedDscProperties -Force -PassThru return $auditObject } } #EndRegion '.\Classes\020.SqlAudit.ps1' 595 #Region '.\Classes\020.SqlDatabasePermission.ps1' 0 <# .SYNOPSIS The `SqlDatabasePermission` DSC resource is used to grant, deny or revoke permissions for a user in a database. .DESCRIPTION The `SqlDatabasePermission` DSC resource is used to grant, deny or revoke permissions for a user in a database. For more information about permissions, please read the article [Permissions (Database Engine)](https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine). >**Note:** When revoking permission with PermissionState 'GrantWithGrant', both the >grantee and _all the other users the grantee has granted the same permission to_, >will also get their permission revoked. ## Requirements * Target machine must be running Windows Server 2012 or later. * Target machine must be running SQL Server Database Engine 2012 or later. ## Known issues All issues are not listed here, see [here for all open issues](https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlDatabasePermission). ### `PSDscRunAsCredential` not supported The built-in property `PSDscRunAsCredential` does not work with class-based resources that using advanced type like the parameters `Permission` and `Reasons` has. Use the parameter `Credential` instead of `PSDscRunAsCredential`. ### Using `Credential` property. SQL Authentication and Group Managed Service Accounts is not supported as impersonation credentials. Currently only Windows Integrated Security is supported to use as credentials. For Windows Authentication the username must either be provided with the User Principal Name (UPN), e.g. 'username@domain.local' or if using non-domain (for example a local Windows Server account) account the username must be provided without the NetBIOS name, e.g. 'username'. The format 'DOMAIN\username' will not work. See more information in [Credential Overview](https://github.com/dsccommunity/SqlServerDsc/wiki/CredentialOverview). ### Invalid values during compilation The parameter Permission is of type `[DatabasePermission]`. If a property in the type is set to an invalid value an error will occur, correct the values in the properties to valid values. This happens when the values are validated against the `[ValidateSet()]` of the resource. When there is an invalid value the following error will be thrown when the configuration is run (it will not show during compilation): ```plaintext Failed to create an object of PowerShell class SqlDatabasePermission. + CategoryInfo : InvalidOperation: (root/Microsoft/...ConfigurationManager:String) [], CimException + FullyQualifiedErrorId : InstantiatePSClassObjectFailed + PSComputerName : localhost ``` .PARAMETER DatabaseName The name of the database. .PARAMETER Name The name of the user that should be granted or denied the permission. .PARAMETER Permission An array of database permissions to enforce. Any permission that is not part of the desired state will be revoked. Must provide all permission states (`Grant`, `Deny`, `GrantWithGrant`) with at least an empty string array for the advanced type `DatabasePermission`'s property `Permission`. Valid permission names can be found in the article [DatabasePermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databasepermissionset#properties). This is an array of CIM instances of advanced type `DatabasePermission` from the namespace `root/Microsoft/Windows/DesiredStateConfiguration`. .PARAMETER PermissionToInclude An array of database permissions to include to the current state. The current state will not be affected unless the current state contradict the desired state. For example if the desired state specifies a deny permissions but in the current state that permission is granted, that permission will be changed to be denied. Valid permission names can be found in the article [DatabasePermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databasepermissionset#properties). This is an array of CIM instances of advanced type `DatabasePermission` from the namespace `root/Microsoft/Windows/DesiredStateConfiguration`. .PARAMETER PermissionToExclude An array of database permissions to exclude (revoke) from the current state. Valid permission names can be found in the article [DatabasePermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databasepermissionset#properties). This is an array of CIM instances of advanced type `DatabasePermission` from the namespace `root/Microsoft/Windows/DesiredStateConfiguration`. .EXAMPLE Invoke-DscResource -ModuleName SqlServerDsc -Name SqlDatabasePermission -Method Get -Property @{ ServerName = 'localhost' InstanceName = 'SQL2017' DatabaseName = 'AdventureWorks' Credential = (Get-Credential -UserName 'myuser@company.local' -Message 'Password:') Name = 'INSTANCE\SqlUser' Permission = [Microsoft.Management.Infrastructure.CimInstance[]] @( ( New-CimInstance -ClientOnly -Namespace root/Microsoft/Windows/DesiredStateConfiguration -ClassName DatabasePermission -Property @{ State = 'Grant' Permission = @('select') } ) ( New-CimInstance -ClientOnly -Namespace root/Microsoft/Windows/DesiredStateConfiguration -ClassName DatabasePermission -Property @{ State = 'GrantWithGrant' Permission = [System.String[]] @() } ) ( New-CimInstance -ClientOnly -Namespace root/Microsoft/Windows/DesiredStateConfiguration -ClassName DatabasePermission -Property @{ State = 'Deny' Permission = [System.String[]] @() } ) ) } This example shows how to call the resource using Invoke-DscResource. .NOTES The built-in property `PsDscRunAsCredential` is not supported on this DSC resource as it uses a complex type (another class as the type for a DSC property). If the property `PsDscRunAsCredential` would be used, then the complex type will not return any values from Get(). This is most likely an issue (bug) with _PowerShell DSC_. Instead (as a workaround) the property `Credential` must be used to specify how to connect to the _SQL Server_ instance. #> [DscResource(RunAsCredential = 'NotSupported')] class SqlDatabasePermission : SqlResourceBase { [DscProperty(Key)] [System.String] $DatabaseName [DscProperty(Key)] [System.String] $Name [DscProperty()] [DatabasePermission[]] $Permission [DscProperty()] [DatabasePermission[]] $PermissionToInclude [DscProperty()] [DatabasePermission[]] $PermissionToExclude SqlDatabasePermission() : base () { # These properties will not be enforced. $this.ExcludeDscProperties = @( 'ServerName' 'InstanceName' 'DatabaseName' 'Name' 'Credential' ) } [SqlDatabasePermission] Get() { # Call the base method to return the properties. return ([ResourceBase] $this).Get() } [System.Boolean] Test() { # Call the base method to test all of the properties that should be enforced. return ([ResourceBase] $this).Test() } [void] Set() { # Call the base method to enforce the properties. ([ResourceBase] $this).Set() } <# Base method Get() call this method to get the current state as a hashtable. The parameter properties will contain the key properties. #> hidden [System.Collections.Hashtable] GetCurrentState([System.Collections.Hashtable] $properties) { $currentStateCredential = $null if ($this.Credential) { <# This does not work, even if username is set, the method Get() will return an empty PSCredential-object. Kept it here so it at least return a Credential object. #> $currentStateCredential = [PSCredential]::new( $this.Credential.UserName, [SecureString]::new() ) } $currentState = @{ Credential = $currentStateCredential Permission = [DatabasePermission[]] @() } Write-Verbose -Message ( $this.localizedData.EvaluateDatabasePermissionForPrincipal -f @( $properties.Name, $properties.DatabaseName, $properties.InstanceName ) ) $serverObject = $this.GetServerObject() $databasePermissionInfo = $serverObject | Get-SqlDscDatabasePermission -DatabaseName $this.DatabaseName -Name $this.Name -ErrorAction 'SilentlyContinue' # If permissions was returned, build the current permission array of [DatabasePermission]. if ($databasePermissionInfo) { [DatabasePermission[]] $currentState.Permission = $databasePermissionInfo | ConvertTo-SqlDscDatabasePermission } # Always return all State; 'Grant', 'GrantWithGrant', and 'Deny'. foreach ($currentPermissionState in @('Grant', 'GrantWithGrant', 'Deny')) { if ($currentState.Permission.State -notcontains $currentPermissionState) { [DatabasePermission[]] $currentState.Permission += [DatabasePermission] @{ State = $currentPermissionState Permission = @() } } } $isPropertyPermissionToIncludeAssigned = $this | Test-DscProperty -Name 'PermissionToInclude' -HasValue if ($isPropertyPermissionToIncludeAssigned) { $currentState.PermissionToInclude = [DatabasePermission[]] @() # Evaluate so that the desired state is present in the current state. foreach ($desiredIncludePermission in $this.PermissionToInclude) { <# Current state will always have all possible states, so this will always return one item. #> $currentStatePermissionForState = $currentState.Permission | Where-Object -FilterScript { $_.State -eq $desiredIncludePermission.State } $currentStatePermissionToInclude = [DatabasePermission] @{ State = $desiredIncludePermission.State Permission = @() } foreach ($desiredIncludePermissionName in $desiredIncludePermission.Permission) { if ($currentStatePermissionForState.Permission -contains $desiredIncludePermissionName) { <# If the permission exist in the current state, add the permission to $currentState.PermissionToInclude so that the base class's method Compare() sees the property as being in desired state (when the property PermissionToInclude in the current state and desired state are equal). #> $currentStatePermissionToInclude.Permission += $desiredIncludePermissionName } else { Write-Verbose -Message ( $this.localizedData.DesiredPermissionAreAbsent -f @( $desiredIncludePermissionName ) ) } } [DatabasePermission[]] $currentState.PermissionToInclude += $currentStatePermissionToInclude } } $isPropertyPermissionToExcludeAssigned = $this | Test-DscProperty -Name 'PermissionToExclude' -HasValue if ($isPropertyPermissionToExcludeAssigned) { $currentState.PermissionToExclude = [DatabasePermission[]] @() # Evaluate so that the desired state is missing from the current state. foreach ($desiredExcludePermission in $this.PermissionToExclude) { <# Current state will always have all possible states, so this will always return one item. #> $currentStatePermissionForState = $currentState.Permission | Where-Object -FilterScript { $_.State -eq $desiredExcludePermission.State } $currentStatePermissionToExclude = [DatabasePermission] @{ State = $desiredExcludePermission.State Permission = @() } foreach ($desiredExcludedPermissionName in $desiredExcludePermission.Permission) { if ($currentStatePermissionForState.Permission -contains $desiredExcludedPermissionName) { Write-Verbose -Message ( $this.localizedData.DesiredAbsentPermissionArePresent -f @( $desiredExcludedPermissionName ) ) } else { <# If the permission does _not_ exist in the current state, add the permission to $currentState.PermissionToExclude so that the base class's method Compare() sees the property as being in desired state (when the property PermissionToExclude in the current state and desired state are equal). #> $currentStatePermissionToExclude.Permission += $desiredExcludedPermissionName } } [DatabasePermission[]] $currentState.PermissionToExclude += $currentStatePermissionToExclude } } return $currentState } <# Base method Set() call this method with the properties that should be enforced are not in desired state. It is not called if all properties are in desired state. The variable $properties contain the properties that are not in desired state. #> hidden [void] Modify([System.Collections.Hashtable] $properties) { $serverObject = $this.GetServerObject() $testSqlDscIsDatabasePrincipalParameters = @{ ServerObject = $serverObject DatabaseName = $this.DatabaseName Name = $this.Name ExcludeFixedRoles = $true } # This will test wether the database and the principal exist. $isDatabasePrincipal = Test-SqlDscIsDatabasePrincipal @testSqlDscIsDatabasePrincipalParameters if (-not $isDatabasePrincipal) { $missingPrincipalMessage = $this.localizedData.NameIsMissing -f @( $this.Name, $this.DatabaseName, $this.InstanceName ) New-InvalidOperationException -Message $missingPrincipalMessage } # This holds each state and their permissions to be revoked. [DatabasePermission[]] $permissionsToRevoke = @() [DatabasePermission[]] $permissionsToGrantOrDeny = @() if ($properties.ContainsKey('Permission')) { $keyProperty = $this | Get-DscProperty -Attribute 'Key' $currentState = $this.GetCurrentState($keyProperty) <# Evaluate if there are any permissions that should be revoked from the current state. #> foreach ($currentDesiredPermissionState in $properties.Permission) { $currentPermissionsForState = $currentState.Permission | Where-Object -FilterScript { $_.State -eq $currentDesiredPermissionState.State } foreach ($permissionName in $currentPermissionsForState.Permission) { if ($permissionName -notin $currentDesiredPermissionState.Permission) { # Look for an existing object in the array. $updatePermissionToRevoke = $permissionsToRevoke | Where-Object -FilterScript { $_.State -eq $currentDesiredPermissionState.State } # Update the existing object in the array, or create a new object if ($updatePermissionToRevoke) { $updatePermissionToRevoke.Permission += $permissionName } else { [DatabasePermission[]] $permissionsToRevoke += [DatabasePermission] @{ State = $currentPermissionsForState.State Permission = $permissionName } } } } } <# At least one permission were missing or should have not be present in the current state. Grant or Deny all permission assigned to the property Permission regardless if they were already present or not. #> [DatabasePermission[]] $permissionsToGrantOrDeny = $properties.Permission } if ($properties.ContainsKey('PermissionToExclude')) { <# At least one permission were present in the current state. Revoke all permission assigned to the property PermissionToExclude regardless if they were already revoked or not. #> [DatabasePermission[]] $permissionsToRevoke = $properties.PermissionToExclude } if ($properties.ContainsKey('PermissionToInclude')) { <# At least one permission were missing or should have not be present in the current state. Grant or Deny all permission assigned to the property Permission regardless if they were already present or not. #> [DatabasePermission[]] $permissionsToGrantOrDeny = $properties.PermissionToInclude } # Revoke all the permissions set in $permissionsToRevoke if ($permissionsToRevoke) { foreach ($currentStateToRevoke in $permissionsToRevoke) { $revokePermissionSet = $currentStateToRevoke | ConvertFrom-SqlDscDatabasePermission $setSqlDscDatabasePermissionParameters = @{ ServerObject = $serverObject DatabaseName = $this.DatabaseName Name = $this.Name Permission = $revokePermissionSet State = 'Revoke' Force = $true } if ($currentStateToRevoke.State -eq 'GrantWithGrant') { $setSqlDscDatabasePermissionParameters.WithGrant = $true } try { Set-SqlDscDatabasePermission @setSqlDscDatabasePermissionParameters } catch { $errorMessage = $this.localizedData.FailedToRevokePermissionFromCurrentState -f @( $this.Name, $this.DatabaseName ) New-InvalidOperationException -Message $errorMessage -ErrorRecord $_ } } } if ($permissionsToGrantOrDeny) { foreach ($currentDesiredPermissionState in $permissionsToGrantOrDeny) { # If there is not an empty array, change permissions. if (-not [System.String]::IsNullOrEmpty($currentDesiredPermissionState.Permission)) { $permissionSet = $currentDesiredPermissionState | ConvertFrom-SqlDscDatabasePermission $setSqlDscDatabasePermissionParameters = @{ ServerObject = $serverObject DatabaseName = $this.DatabaseName Name = $this.Name Permission = $permissionSet Force = $true } try { switch ($currentDesiredPermissionState.State) { 'GrantWithGrant' { Set-SqlDscDatabasePermission @setSqlDscDatabasePermissionParameters -State 'Grant' -WithGrant } default { Set-SqlDscDatabasePermission @setSqlDscDatabasePermissionParameters -State $currentDesiredPermissionState.State } } } catch { $errorMessage = $this.localizedData.FailedToSetPermission -f @( $this.Name, $this.DatabaseName ) New-InvalidOperationException -Message $errorMessage -ErrorRecord $_ } } } } } <# Base method Assert() call this method with the properties that was assigned a value. #> hidden [void] AssertProperties([System.Collections.Hashtable] $properties) { # PermissionToInclude and PermissionToExclude should be mutually exclusive from Permission $assertBoundParameterParameters = @{ BoundParameterList = $properties MutuallyExclusiveList1 = @( 'Permission' ) MutuallyExclusiveList2 = @( 'PermissionToInclude' 'PermissionToExclude' ) } Assert-BoundParameter @assertBoundParameterParameters # Get all assigned permission properties. $assignedPermissionProperty = $properties.Keys.Where({ $_ -in @( 'Permission', 'PermissionToInclude', 'PermissionToExclude' ) }) # Must include either of the permission properties. if ([System.String]::IsNullOrEmpty($assignedPermissionProperty)) { $errorMessage = $this.localizedData.MustAssignOnePermissionProperty New-InvalidArgumentException -ArgumentName 'Permission, PermissionToInclude, PermissionToExclude' -Message $errorMessage } foreach ($currentAssignedPermissionProperty in $assignedPermissionProperty) { # One State cannot exist several times in the same resource instance. $permissionStateGroupCount = @( $properties.$currentAssignedPermissionProperty | Group-Object -NoElement -Property 'State' -CaseSensitive:$false | Select-Object -ExpandProperty 'Count' ) if ($permissionStateGroupCount -gt 1) { $errorMessage = $this.localizedData.DuplicatePermissionState New-InvalidArgumentException -ArgumentName $currentAssignedPermissionProperty -Message $errorMessage } # A specific permission must only exist in one permission state. $permissionGroupCount = $properties.$currentAssignedPermissionProperty.Permission | Group-Object -NoElement -CaseSensitive:$false | Select-Object -ExpandProperty 'Count' if ($permissionGroupCount -gt 1) { $errorMessage = $this.localizedData.DuplicatePermissionBetweenState New-InvalidArgumentException -ArgumentName $currentAssignedPermissionProperty -Message $errorMessage } } if ($properties.Keys -contains 'Permission') { # Each State must exist once. $missingPermissionState = ( $properties.Permission.State -notcontains 'Grant' -or $properties.Permission.State -notcontains 'GrantWithGrant' -or $properties.Permission.State -notcontains 'Deny' ) if ($missingPermissionState) { $errorMessage = $this.localizedData.MissingPermissionState New-InvalidArgumentException -ArgumentName 'Permission' -Message $errorMessage } } <# Each permission state in the properties PermissionToInclude and PermissionToExclude must have specified at minimum one permission. #> foreach ($currentAssignedPermissionProperty in @('PermissionToInclude', 'PermissionToExclude')) { if ($properties.Keys -contains $currentAssignedPermissionProperty) { foreach ($currentDatabasePermission in $properties.$currentAssignedPermissionProperty) { if ($currentDatabasePermission.Permission.Count -eq 0) { $errorMessage = $this.localizedData.MustHaveMinimumOnePermissionInState -f $currentAssignedPermissionProperty New-InvalidArgumentException -ArgumentName $currentAssignedPermissionProperty -Message $errorMessage } } } } } } #EndRegion '.\Classes\020.SqlDatabasePermission.ps1' 646 #Region '.\Classes\020.SqlPermission.ps1' 0 <# .SYNOPSIS The `SqlPermission` DSC resource is used to grant, deny or revoke server permissions for a login. .DESCRIPTION The `SqlPermission` DSC resource is used to grant, deny or revoke Server permissions for a login. For more information about permissions, please read the article [Permissions (Database Engine)](https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine). >**Note:** When revoking permission with PermissionState 'GrantWithGrant', both the >grantee and _all the other users the grantee has granted the same permission to_, >will also get their permission revoked. ## Requirements * Target machine must be running Windows Server 2012 or later. * Target machine must be running SQL Server Database Engine 2012 or later. * Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module. ## Known issues All issues are not listed here, see [here for all open issues](https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlPermission). ### `PSDscRunAsCredential` not supported The built-in property `PSDscRunAsCredential` does not work with class-based resources that using advanced type like the parameters `Permission` and `Reasons` has. Use the parameter `Credential` instead of `PSDscRunAsCredential`. ### Using `Credential` property. SQL Authentication and Group Managed Service Accounts is not supported as impersonation credentials. Currently only Windows Integrated Security is supported to use as credentials. For Windows Authentication the username must either be provided with the User Principal Name (UPN), e.g. 'username@domain.local' or if using non-domain (for example a local Windows Server account) account the username must be provided without the NetBIOS name, e.g. 'username'. The format 'DOMAIN\username' will not work. See more information in [Credential Overview](https://github.com/dsccommunity/SqlServerDsc/wiki/CredentialOverview). ### Invalid values during compilation The parameter Permission is of type `[ServerPermission]`. If a property in the type is set to an invalid value an error will occur, correct the values in the properties to valid values. This happens when the values are validated against the `[ValidateSet()]` of the resource. When there is an invalid value the following error will be thrown when the configuration is run (it will not show during compilation): ```plaintext Failed to create an object of PowerShell class SqlPermission. + CategoryInfo : InvalidOperation: (root/Microsoft/...ConfigurationManager:String) [], CimException + FullyQualifiedErrorId : InstantiatePSClassObjectFailed + PSComputerName : localhost ``` .PARAMETER Name The name of the user that should be granted or denied the permission. .PARAMETER Permission An array of server permissions to enforce. Any permission that is not part of the desired state will be revoked. Must provide all permission states (`Grant`, `Deny`, `GrantWithGrant`) with at least an empty string array for the advanced type `ServerPermission`'s property `Permission`. Valid permission names can be found in the article [ServerPermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.serverpermissionset#properties). This is an array of CIM instances of advanced type `ServerPermission` from the namespace `root/Microsoft/Windows/DesiredStateConfiguration`. .PARAMETER PermissionToInclude An array of server permissions to include to the current state. The current state will not be affected unless the current state contradict the desired state. For example if the desired state specifies a deny permissions but in the current state that permission is granted, that permission will be changed to be denied. Valid permission names can be found in the article [ServerPermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.serverpermissionset#properties). This is an array of CIM instances of advanced type `ServerPermission` from the namespace `root/Microsoft/Windows/DesiredStateConfiguration`. .PARAMETER PermissionToExclude An array of server permissions to exclude (revoke) from the current state. Valid permission names can be found in the article [ServerPermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.serverpermissionset#properties). This is an array of CIM instances of advanced type `ServerPermission` from the namespace `root/Microsoft/Windows/DesiredStateConfiguration`. .EXAMPLE Invoke-DscResource -ModuleName SqlServerDsc -Name SqlPermission -Method Get -Property @{ ServerName = 'localhost' InstanceName = 'SQL2017' Credential = (Get-Credential -UserName 'myuser@company.local' -Message 'Password:') Name = 'INSTANCE\SqlUser' Permission = [Microsoft.Management.Infrastructure.CimInstance[]] @( ( New-CimInstance -ClientOnly -Namespace root/Microsoft/Windows/DesiredStateConfiguration -ClassName ServerPermission -Property @{ State = 'Grant' Permission = @('select') } ) ( New-CimInstance -ClientOnly -Namespace root/Microsoft/Windows/DesiredStateConfiguration -ClassName ServerPermission -Property @{ State = 'GrantWithGrant' Permission = [System.String[]] @() } ) ( New-CimInstance -ClientOnly -Namespace root/Microsoft/Windows/DesiredStateConfiguration -ClassName ServerPermission -Property @{ State = 'Deny' Permission = [System.String[]] @() } ) ) } This example shows how to call the resource using Invoke-DscResource. .NOTES The built-in property `PsDscRunAsCredential` is not supported on this DSC resource as it uses a complex type (another class as the type for a DSC property). If the property `PsDscRunAsCredential` would be used, then the complex type will not return any values from Get(). This is most likely an issue (bug) with _PowerShell DSC_. Instead (as a workaround) the property `Credential` must be used to specify how to connect to the SQL Server instance. #> [DscResource(RunAsCredential = 'NotSupported')] class SqlPermission : SqlResourceBase { [DscProperty(Key)] [System.String] $Name [DscProperty()] [ServerPermission[]] $Permission [DscProperty()] [ServerPermission[]] $PermissionToInclude [DscProperty()] [ServerPermission[]] $PermissionToExclude SqlPermission() : base () { # These properties will not be enforced. $this.ExcludeDscProperties = @( 'ServerName' 'InstanceName' 'Name' 'Credential' ) } [SqlPermission] Get() { # Call the base method to return the properties. return ([ResourceBase] $this).Get() } [System.Boolean] Test() { # Call the base method to test all of the properties that should be enforced. return ([ResourceBase] $this).Test() } [void] Set() { # Call the base method to enforce the properties. ([ResourceBase] $this).Set() } <# Base method Get() call this method to get the current state as a hashtable. The parameter properties will contain the key properties. #> hidden [System.Collections.Hashtable] GetCurrentState([System.Collections.Hashtable] $properties) { $currentStateCredential = $null if ($this.Credential) { <# This does not work, even if username is set, the method Get() will return an empty PSCredential-object. Kept it here so it at least return a Credential object. #> $currentStateCredential = [PSCredential]::new( $this.Credential.UserName, [SecureString]::new() ) } $currentState = @{ Credential = $currentStateCredential Permission = [ServerPermission[]] @() } Write-Verbose -Message ( $this.localizedData.EvaluateServerPermissionForPrincipal -f @( $properties.Name, $properties.InstanceName ) ) $serverObject = $this.GetServerObject() $serverPermissionInfo = $serverObject | Get-SqlDscServerPermission -Name $this.Name -ErrorAction 'SilentlyContinue' # If permissions was returned, build the current permission array of [ServerPermission]. if ($serverPermissionInfo) { [ServerPermission[]] $currentState.Permission = $serverPermissionInfo | ConvertTo-SqlDscServerPermission } # Always return all State; 'Grant', 'GrantWithGrant', and 'Deny'. foreach ($currentPermissionState in @('Grant', 'GrantWithGrant', 'Deny')) { if ($currentState.Permission.State -notcontains $currentPermissionState) { [ServerPermission[]] $currentState.Permission += [ServerPermission] @{ State = $currentPermissionState Permission = @() } } } $isPropertyPermissionToIncludeAssigned = $this | Test-DscProperty -Name 'PermissionToInclude' -HasValue if ($isPropertyPermissionToIncludeAssigned) { $currentState.PermissionToInclude = [ServerPermission[]] @() # Evaluate so that the desired state is present in the current state. foreach ($desiredIncludePermission in $this.PermissionToInclude) { <# Current state will always have all possible states, so this will always return one item. #> $currentStatePermissionForState = $currentState.Permission | Where-Object -FilterScript { $_.State -eq $desiredIncludePermission.State } $currentStatePermissionToInclude = [ServerPermission] @{ State = $desiredIncludePermission.State Permission = @() } foreach ($desiredIncludePermissionName in $desiredIncludePermission.Permission) { if ($currentStatePermissionForState.Permission -contains $desiredIncludePermissionName) { <# If the permission exist in the current state, add the permission to $currentState.PermissionToInclude so that the base class's method Compare() sees the property as being in desired state (when the property PermissionToInclude in the current state and desired state are equal). #> $currentStatePermissionToInclude.Permission += $desiredIncludePermissionName } else { Write-Verbose -Message ( $this.localizedData.DesiredPermissionAreAbsent -f @( $desiredIncludePermissionName ) ) } } [ServerPermission[]] $currentState.PermissionToInclude += $currentStatePermissionToInclude } } $isPropertyPermissionToExcludeAssigned = $this | Test-DscProperty -Name 'PermissionToExclude' -HasValue if ($isPropertyPermissionToExcludeAssigned) { $currentState.PermissionToExclude = [ServerPermission[]] @() # Evaluate so that the desired state is missing from the current state. foreach ($desiredExcludePermission in $this.PermissionToExclude) { <# Current state will always have all possible states, so this will always return one item. #> $currentStatePermissionForState = $currentState.Permission | Where-Object -FilterScript { $_.State -eq $desiredExcludePermission.State } $currentStatePermissionToExclude = [ServerPermission] @{ State = $desiredExcludePermission.State Permission = @() } foreach ($desiredExcludedPermissionName in $desiredExcludePermission.Permission) { if ($currentStatePermissionForState.Permission -contains $desiredExcludedPermissionName) { Write-Verbose -Message ( $this.localizedData.DesiredAbsentPermissionArePresent -f @( $desiredExcludedPermissionName ) ) } else { <# If the permission does _not_ exist in the current state, add the permission to $currentState.PermissionToExclude so that the base class's method Compare() sees the property as being in desired state (when the property PermissionToExclude in the current state and desired state are equal). #> $currentStatePermissionToExclude.Permission += $desiredExcludedPermissionName } } [ServerPermission[]] $currentState.PermissionToExclude += $currentStatePermissionToExclude } } return $currentState } <# Base method Set() call this method with the properties that should be enforced are not in desired state. It is not called if all properties are in desired state. The variable $properties contain the properties that are not in desired state. #> hidden [void] Modify([System.Collections.Hashtable] $properties) { $serverObject = $this.GetServerObject() $testSqlDscIsLoginParameters = @{ ServerObject = $serverObject Name = $this.Name } # This will test wether the principal exist. $isLogin = Test-SqlDscIsLogin @testSqlDscIsLoginParameters if (-not $isLogin) { $missingPrincipalMessage = $this.localizedData.NameIsMissing -f @( $this.Name, $this.InstanceName ) New-InvalidOperationException -Message $missingPrincipalMessage } # This holds each state and their permissions to be revoked. [ServerPermission[]] $permissionsToRevoke = @() [ServerPermission[]] $permissionsToGrantOrDeny = @() if ($properties.ContainsKey('Permission')) { $keyProperty = $this | Get-DscProperty -Attribute 'Key' $currentState = $this.GetCurrentState($keyProperty) <# Evaluate if there are any permissions that should be revoked from the current state. #> foreach ($currentDesiredPermissionState in $properties.Permission) { $currentPermissionsForState = $currentState.Permission | Where-Object -FilterScript { $_.State -eq $currentDesiredPermissionState.State } foreach ($permissionName in $currentPermissionsForState.Permission) { if ($permissionName -notin $currentDesiredPermissionState.Permission) { # Look for an existing object in the array. $updatePermissionToRevoke = $permissionsToRevoke | Where-Object -FilterScript { $_.State -eq $currentDesiredPermissionState.State } # Update the existing object in the array, or create a new object if ($updatePermissionToRevoke) { $updatePermissionToRevoke.Permission += $permissionName } else { [ServerPermission[]] $permissionsToRevoke += [ServerPermission] @{ State = $currentPermissionsForState.State Permission = $permissionName } } } } } <# At least one permission were missing or should have not be present in the current state. Grant or Deny all permission assigned to the property Permission regardless if they were already present or not. #> [ServerPermission[]] $permissionsToGrantOrDeny = $properties.Permission } if ($properties.ContainsKey('PermissionToExclude')) { <# At least one permission were present in the current state. Revoke all permission assigned to the property PermissionToExclude regardless if they were already revoked or not. #> [ServerPermission[]] $permissionsToRevoke = $properties.PermissionToExclude } if ($properties.ContainsKey('PermissionToInclude')) { <# At least one permission were missing or should have not be present in the current state. Grant or Deny all permission assigned to the property Permission regardless if they were already present or not. #> [ServerPermission[]] $permissionsToGrantOrDeny = $properties.PermissionToInclude } # Revoke all the permissions set in $permissionsToRevoke if ($permissionsToRevoke) { <# TODO: Could verify with $sqlServerObject.EnumServerPermissions($Principal, $desiredPermissionSet) which permissions are not already revoked. #> foreach ($currentStateToRevoke in $permissionsToRevoke) { $revokePermissionSet = $currentStateToRevoke | ConvertFrom-SqlDscServerPermission $setSqlDscServerPermissionParameters = @{ ServerObject = $serverObject Name = $this.Name Permission = $revokePermissionSet State = 'Revoke' Force = $true } if ($currentStateToRevoke.State -eq 'GrantWithGrant') { $setSqlDscServerPermissionParameters.WithGrant = $true } try { Set-SqlDscServerPermission @setSqlDscServerPermissionParameters } catch { $errorMessage = $this.localizedData.FailedToRevokePermissionFromCurrentState -f @( $this.Name ) New-InvalidOperationException -Message $errorMessage -ErrorRecord $_ } } } if ($permissionsToGrantOrDeny) { <# TODO: Could verify with $sqlServerObject.EnumServerPermissions($Principal, $desiredPermissionSet) which permissions are not already set. #> foreach ($currentDesiredPermissionState in $permissionsToGrantOrDeny) { # If there is not an empty array, change permissions. if (-not [System.String]::IsNullOrEmpty($currentDesiredPermissionState.Permission)) { $permissionSet = $currentDesiredPermissionState | ConvertFrom-SqlDscServerPermission $setSqlDscServerPermissionParameters = @{ ServerObject = $serverObject Name = $this.Name Permission = $permissionSet Force = $true } try { switch ($currentDesiredPermissionState.State) { 'GrantWithGrant' { Set-SqlDscServerPermission @setSqlDscServerPermissionParameters -State 'Grant' -WithGrant } default { Set-SqlDscServerPermission @setSqlDscServerPermissionParameters -State $currentDesiredPermissionState.State } } } catch { $errorMessage = $this.localizedData.FailedToSetPermission -f @( $this.Name ) New-InvalidOperationException -Message $errorMessage -ErrorRecord $_ } } } } } <# Base method Assert() call this method with the properties that was assigned a value. #> hidden [void] AssertProperties([System.Collections.Hashtable] $properties) { # PermissionToInclude and PermissionToExclude should be mutually exclusive from Permission $assertBoundParameterParameters = @{ BoundParameterList = $properties MutuallyExclusiveList1 = @( 'Permission' ) MutuallyExclusiveList2 = @( 'PermissionToInclude' 'PermissionToExclude' ) } Assert-BoundParameter @assertBoundParameterParameters # Get all assigned permission properties. $assignedPermissionProperty = $properties.Keys.Where({ $_ -in @( 'Permission', 'PermissionToInclude', 'PermissionToExclude' ) }) # Must include either of the permission properties. if ([System.String]::IsNullOrEmpty($assignedPermissionProperty)) { $errorMessage = $this.localizedData.MustAssignOnePermissionProperty New-InvalidArgumentException -ArgumentName 'Permission, PermissionToInclude, PermissionToExclude' -Message $errorMessage } foreach ($currentAssignedPermissionProperty in $assignedPermissionProperty) { # One State cannot exist several times in the same resource instance. $permissionStateGroupCount = @( $properties.$currentAssignedPermissionProperty | Group-Object -NoElement -Property 'State' -CaseSensitive:$false | Select-Object -ExpandProperty 'Count' ) if ($permissionStateGroupCount -gt 1) { $errorMessage = $this.localizedData.DuplicatePermissionState New-InvalidArgumentException -ArgumentName $currentAssignedPermissionProperty -Message $errorMessage } # A specific permission must only exist in one permission state. $permissionGroupCount = $properties.$currentAssignedPermissionProperty.Permission | Group-Object -NoElement -CaseSensitive:$false | Select-Object -ExpandProperty 'Count' if ($permissionGroupCount -gt 1) { $errorMessage = $this.localizedData.DuplicatePermissionBetweenState New-InvalidArgumentException -ArgumentName $currentAssignedPermissionProperty -Message $errorMessage } } if ($properties.Keys -contains 'Permission') { # Each State must exist once. $missingPermissionState = ( $properties.Permission.State -notcontains 'Grant' -or $properties.Permission.State -notcontains 'GrantWithGrant' -or $properties.Permission.State -notcontains 'Deny' ) if ($missingPermissionState) { $errorMessage = $this.localizedData.MissingPermissionState New-InvalidArgumentException -ArgumentName 'Permission' -Message $errorMessage } } <# Each permission state in the properties PermissionToInclude and PermissionToExclude must have specified at minimum one permission. #> foreach ($currentAssignedPermissionProperty in @('PermissionToInclude', 'PermissionToExclude')) { if ($properties.Keys -contains $currentAssignedPermissionProperty) { foreach ($currentServerPermission in $properties.$currentAssignedPermissionProperty) { if ($currentServerPermission.Permission.Count -eq 0) { $errorMessage = $this.localizedData.MustHaveMinimumOnePermissionInState -f $currentAssignedPermissionProperty New-InvalidArgumentException -ArgumentName $currentAssignedPermissionProperty -Message $errorMessage } } } } } } #EndRegion '.\Classes\020.SqlPermission.ps1' 639 #Region '.\Private\Assert-SetupActionProperties.ps1' 0 <# .SYNOPSIS Assert that the bound parameters are set as required .DESCRIPTION Assert that required parameters has been specified, and throws an exception if not. .PARAMETER Property A hashtable containing the parameters to evaluate. Normally this is set to $PSBoundParameters. .PARAMETER SetupAction A string value representing the setup action that is gonna be executed. .EXAMPLE Assert-SetupActionProperties -Property $PSBoundParameters -SetupAction 'Install' Throws an exception if the bound parameters are not in the correct state. .OUTPUTS None. .NOTES This function is used by the command Invoke-SetupAction to verify that the bound parameters are in the required state. #> function Assert-SetupActionProperties { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseSingularNouns', '', Justification='The command uses plural noun to describe that it contain a collection of asserts.')] [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [System.Collections.Hashtable] $Property, [Parameter(Mandatory = $true)] [System.String] $SetupAction ) # If one of the properties PBStartPortRange and PBEndPortRange are specified, then both must be specified. $assertParameters = @('PBStartPortRange', 'PBEndPortRange') $assertRequiredCommandParameterParameters = @{ BoundParameterList = $Property RequiredParameter = $assertParameters IfParameterPresent = $assertParameters } Assert-BoundParameter @assertRequiredCommandParameterParameters # The parameter UseSqlRecommendedMemoryLimits is mutually exclusive to SqlMinMemory and SqlMaxMemory. Assert-BoundParameter -BoundParameterList $Property -MutuallyExclusiveList1 @( 'UseSqlRecommendedMemoryLimits' ) -MutuallyExclusiveList2 @( 'SqlMinMemory' 'SqlMaxMemory' ) # If Role is set to SPI_AS_NewFarm then the specific parameters are required. if ($Property.ContainsKey('Role') -and $Property.Role -eq 'SPI_AS_NewFarm') { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @( 'FarmAccount' 'FarmPassword' 'Passphrase' 'FarmAdminiPort' # cspell: disable-line ) } # If the parameter SecurityMode is set to 'SQL' then the parameter SAPwd is required. if ($Property.ContainsKey('SecurityMode') -and $Property.SecurityMode -eq 'SQL') { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @('SAPwd') } # If the parameter FileStreamLevel is set and is greater or equal to 2 then the parameter FileStreamShareName is required. if ($Property.ContainsKey('FileStreamLevel') -and $Property.FileStreamLevel -ge 2) { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @('FileStreamShareName') } # If a *SvcAccount is specified then the accompanying *SvcPassword must be set unless it is a (global) managed service account, virtual account, or a built-in account. $accountProperty = @( 'PBEngSvcAccount' 'PBDMSSvcAccount' # cSpell: disable-line 'AgtSvcAccount' 'ASSvcAccount' 'FarmAccount' 'SqlSvcAccount' 'ISSvcAccount' 'RSSvcAccount' ) foreach ($currentAccountProperty in $accountProperty) { if ($currentAccountProperty -in $Property.Keys) { # If not (global) managed service account, virtual account, or a built-in account. if ((Test-AccountRequirePassword -Name $Property.$currentAccountProperty)) { $assertPropertyName = $currentAccountProperty -replace 'Account', 'Password' Assert-BoundParameter -BoundParameterList $Property -RequiredParameter $assertPropertyName } } } # If feature AzureExtension is specified then the all the Azure* parameters must be set (except AzureArcProxy). if ($Property.ContainsKey('Features') -and $Property.Features -contains 'AZUREEXTENSION') # cSpell: disable-line { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @( 'AzureSubscriptionId' 'AzureResourceGroup' 'AzureRegion' 'AzureTenantId' 'AzureServicePrincipal' 'AzureServicePrincipalSecret' 'ProductCoveredBySA' ) } # If feature is SQLENGINE, then for specified setup actions the parameter AgtSvcAccount is mandatory. if ($SetupAction -in ('CompleteImage', 'InstallFailoverCluster', 'PrepareFailoverCluster', 'AddNode')) { if ($Property.ContainsKey('Features') -and $Property.Features -contains 'SQLENGINE') { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @('AgtSvcAccount') } } if ($SetupAction -in ('InstallFailoverCluster', 'PrepareFailoverCluster', 'AddNode')) { # The parameter ASSvcAccount is mandatory if feature AS is installed and setup action is InstallFailoverCluster, PrepareFailoverCluster, or AddNode. if ($Property.ContainsKey('Features') -and $Property.Features -contains 'AS') { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @('ASSvcAccount') } # The parameter SqlSvcAccount is mandatory if feature SQLENGINE is installed and setup action is InstallFailoverCluster, PrepareFailoverCluster, or AddNode. if ($Property.ContainsKey('Features') -and $Property.Features -contains 'SQLENGINE') { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @('SqlSvcAccount') } # The parameter ISSvcAccount is mandatory if feature IS is installed and setup action is InstallFailoverCluster, PrepareFailoverCluster, or AddNode. if ($Property.ContainsKey('Features') -and $Property.Features -contains 'IS') { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @('ISSvcAccount') } if ($Property.ContainsKey('Features') -and $Property.Features -contains 'RS') { Assert-BoundParameter -BoundParameterList $Property -RequiredParameter @('RSSvcAccount') } } # The ASServerMode value PowerPivot is not allowed when parameter set is InstallFailoverCluster or CompleteFailoverCluster. if ($SetupAction -in ('InstallFailoverCluster', 'CompleteFailoverCluster')) { if ($Property.ContainsKey('ASServerMode') -and $Property.ASServerMode -eq 'PowerPivot') { $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( ($script:localizedData.InstallSqlServerProperties_ASServerModeInvalidValue -f $SetupAction), 'ASAP0001', # cSpell: disable-line [System.Management.Automation.ErrorCategory]::InvalidOperation, 'Command parameters' ) ) } } # The ASServerMode value PowerPivot is not allowed when parameter set is InstallFailoverCluster or CompleteFailoverCluster. if ($SetupAction -in ('AddNode')) { if ($Property.ContainsKey('RsInstallMode') -and $Property.RsInstallMode -ne 'FilesOnlyMode') { $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( ($script:localizedData.InstallSqlServerProperties_RsInstallModeInvalidValue -f $SetupAction), 'ASAP0002', # cSpell: disable-line [System.Management.Automation.ErrorCategory]::InvalidOperation, 'Command parameters' ) ) } } } #EndRegion '.\Private\Assert-SetupActionProperties.ps1' 191 #Region '.\Private\Invoke-SetupAction.ps1' 0 <# .SYNOPSIS Executes an setup action using Microsoft SQL Server setup executable. .DESCRIPTION Executes an setup action using Microsoft SQL Server setup executable. See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER Install Specifies the setup action Install. .PARAMETER Uninstall Specifies the setup action Uninstall. .PARAMETER PrepareImage Specifies the setup action PrepareImage. .PARAMETER CompleteImage Specifies the setup action CompleteImage. .PARAMETER Upgrade Specifies the setup action Upgrade. .PARAMETER EditionUpgrade Specifies the setup action EditionUpgrade. .PARAMETER Repair Specifies the setup action Repair. .PARAMETER RebuildDatabase Specifies the setup action RebuildDatabase. .PARAMETER InstallFailoverCluster Specifies the setup action InstallFailoverCluster. .PARAMETER PrepareFailoverCluster Specifies the setup action PrepareFailoverCluster. .PARAMETER CompleteFailoverCluster Specifies the setup action CompleteFailoverCluster. .PARAMETER AddNode Specifies the setup action AddNode. .PARAMETER RemoveNode Specifies the setup action RemoveNode. .PARAMETER ConfigurationFile Specifies an configuration file to use during SQL Server setup. This parameter cannot be used together with any of the setup actions, but instead it is expected that the configuration file specifies what setup action to run. .PARAMETER AcceptLicensingTerms Required parameter to be able to run unattended install. By specifying this parameter you acknowledge the acceptance all license terms and notices for the specified features, the terms and notices that the Microsoft SQL Server setup executable normally ask for. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER SuppressPrivacyStatementNotice See the notes section for more information. .PARAMETER IAcknowledgeEntCalLimits See the notes section for more information. .PARAMETER InstanceName See the notes section for more information. .PARAMETER Enu See the notes section for more information. .PARAMETER UpdateEnabled See the notes section for more information. .PARAMETER UpdateSource See the notes section for more information. .PARAMETER Features See the notes section for more information. .PARAMETER Role See the notes section for more information. .PARAMETER InstallSharedDir See the notes section for more information. .PARAMETER InstallSharedWowDir See the notes section for more information. .PARAMETER InstanceDir See the notes section for more information. .PARAMETER InstanceId See the notes section for more information. .PARAMETER PBEngSvcAccount See the notes section for more information. .PARAMETER PBEngSvcPassword See the notes section for more information. .PARAMETER PBEngSvcStartupType See the notes section for more information. .PARAMETER PBDMSSvcAccount See the notes section for more information. .PARAMETER PBDMSSvcPassword See the notes section for more information. .PARAMETER PBDMSSvcStartupType See the notes section for more information. .PARAMETER PBStartPortRange See the notes section for more information. .PARAMETER PBEndPortRange See the notes section for more information. .PARAMETER PBScaleOut See the notes section for more information. .PARAMETER ProductKey See the notes section for more information. .PARAMETER AgtSvcAccount See the notes section for more information. .PARAMETER AgtSvcPassword See the notes section for more information. .PARAMETER AgtSvcStartupType See the notes section for more information. .PARAMETER ASBackupDir See the notes section for more information. .PARAMETER ASCollation See the notes section for more information. .PARAMETER ASConfigDir See the notes section for more information. .PARAMETER ASDataDir See the notes section for more information. .PARAMETER ASLogDir See the notes section for more information. .PARAMETER ASTempDir See the notes section for more information. .PARAMETER ASServerMode See the notes section for more information. .PARAMETER ASSvcAccount See the notes section for more information. .PARAMETER ASSvcPassword See the notes section for more information. .PARAMETER ASSvcStartupType See the notes section for more information. .PARAMETER ASSysAdminAccounts See the notes section for more information. .PARAMETER ASProviderMSOLAP See the notes section for more information. .PARAMETER FarmAccount See the notes section for more information. .PARAMETER FarmPassword See the notes section for more information. .PARAMETER Passphrase See the notes section for more information. .PARAMETER FarmAdminiPort See the notes section for more information. .PARAMETER BrowserSvcStartupType See the notes section for more information. .PARAMETER FTUpgradeOption See the notes section for more information. .PARAMETER EnableRanU See the notes section for more information. .PARAMETER InstallSqlDataDir See the notes section for more information. .PARAMETER SqlBackupDir See the notes section for more information. .PARAMETER SecurityMode See the notes section for more information. .PARAMETER SAPwd See the notes section for more information. .PARAMETER SqlCollation See the notes section for more information. .PARAMETER AddCurrentUserAsSqlAdmin See the notes section for more information. .PARAMETER SqlSvcAccount See the notes section for more information. .PARAMETER SqlSvcPassword See the notes section for more information. .PARAMETER SqlSvcStartupType See the notes section for more information. .PARAMETER SqlSysAdminAccounts See the notes section for more information. .PARAMETER SqlTempDbDir See the notes section for more information. .PARAMETER SqlTempDbLogDir See the notes section for more information. .PARAMETER SqlTempDbFileCount See the notes section for more information. .PARAMETER SqlTempDbFileSize See the notes section for more information. .PARAMETER SqlTempDbFileGrowth See the notes section for more information. .PARAMETER SqlTempDbLogFileSize See the notes section for more information. .PARAMETER SqlTempDbLogFileGrowth See the notes section for more information. .PARAMETER SqlUserDbDir See the notes section for more information. .PARAMETER SqlSvcInstantFileInit See the notes section for more information. .PARAMETER SqlUserDbLogDir See the notes section for more information. .PARAMETER SqlMaxDop See the notes section for more information. .PARAMETER UseSqlRecommendedMemoryLimits See the notes section for more information. .PARAMETER SqlMinMemory See the notes section for more information. .PARAMETER SqlMaxMemory See the notes section for more information. .PARAMETER FileStreamLevel See the notes section for more information. .PARAMETER FileStreamShareName See the notes section for more information. .PARAMETER ISSvcAccount See the notes section for more information. .PARAMETER ISSvcPassword See the notes section for more information. .PARAMETER ISSvcStartupType See the notes section for more information. .PARAMETER AllowUpgradeForSSRSSharePointMode See the notes section for more information. .PARAMETER NpEnabled See the notes section for more information. .PARAMETER TcpEnabled See the notes section for more information. .PARAMETER RsInstallMode See the notes section for more information. .PARAMETER RSSvcAccount See the notes section for more information. .PARAMETER RSSvcPassword See the notes section for more information. .PARAMETER RSSvcStartupType See the notes section for more information. .PARAMETER MPYCacheDirectory See the notes section for more information. .PARAMETER MRCacheDirectory See the notes section for more information. .PARAMETER SqlInstJava See the notes section for more information. .PARAMETER SqlJavaDir See the notes section for more information. .PARAMETER FailoverClusterGroup See the notes section for more information. .PARAMETER FailoverClusterDisks See the notes section for more information. .PARAMETER FailoverClusterNetworkName See the notes section for more information. .PARAMETER FailoverClusterIPAddresses See the notes section for more information. .PARAMETER ConfirmIPDependencyChange See the notes section for more information. .PARAMETER FailoverClusterRollOwnership See the notes section for more information. .PARAMETER AzureSubscriptionId See the notes section for more information. .PARAMETER AzureResourceGroup See the notes section for more information. .PARAMETER AzureRegion See the notes section for more information. .PARAMETER AzureTenantId See the notes section for more information. .PARAMETER AzureServicePrincipal See the notes section for more information. .PARAMETER AzureServicePrincipalSecret See the notes section for more information. .PARAMETER AzureArcProxy See the notes section for more information. .PARAMETER SkipRules See the notes section for more information. .PARAMETER ProductCoveredBySA See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Invoke-SetupAction -Install -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -SqlSysAdminAccounts @('MyAdminAccount') -MediaPath 'E:\' Installs the database engine for the named instance MyInstance. .EXAMPLE Invoke-SetupAction -Install -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE','ARC' -SqlSysAdminAccounts @('MyAdminAccount') -MediaPath 'E:\' -AzureSubscriptionId 'MySubscriptionId' -AzureResourceGroup 'MyRG' -AzureRegion 'West-US' -AzureTenantId 'MyTenantId' -AzureServicePrincipal 'MyPrincipalName' -AzureServicePrincipalSecret ('MySecret' | ConvertTo-SecureString -AsPlainText -Force) Installs the database engine for the named instance MyInstance and onboard the server to Azure Arc. .EXAMPLE Invoke-SetupAction -Install -AcceptLicensingTerms -MediaPath 'E:\' -AzureSubscriptionId 'MySubscriptionId' -AzureResourceGroup 'MyRG' -AzureRegion 'West-US' -AzureTenantId 'MyTenantId' -AzureServicePrincipal 'MyPrincipalName' -AzureServicePrincipalSecret ('MySecret' | ConvertTo-SecureString -AsPlainText -Force) Installs the Azure Arc Agent on the server. .EXAMPLE Invoke-SetupAction -ConfigurationFile 'MySqlConfig.ini' -MediaPath 'E:\' Installs SQL Server using the configuration file 'MySqlConfig.ini'. .EXAMPLE Invoke-SetupAction -Uninstall -InstanceName 'MyInstance' -Features 'SQLENGINE' -MediaPath 'E:\' Uninstalls the database engine from the named instance MyInstance. .EXAMPLE Invoke-SetupAction -PrepareImage -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -InstanceId 'MyInstance' -MediaPath 'E:\' Prepares the server for using the database engine for an instance named 'MyInstance'. .EXAMPLE Invoke-SetupAction -CompleteImage -AcceptLicensingTerms -MediaPath 'E:\' Completes install on a server that was previously prepared (by using prepare image). .EXAMPLE Invoke-SetupAction -Upgrade -AcceptLicensingTerms -InstanceName 'MyInstance' -MediaPath 'E:\' Upgrades the instance 'MyInstance' with the SQL Server version that is provided by the media path. .EXAMPLE Invoke-SetupAction -EditionUpgrade -AcceptLicensingTerms -ProductKey 'NewEditionProductKey' -InstanceName 'MyInstance' -MediaPath 'E:\' Upgrades the instance 'MyInstance' with the SQL Server edition that is provided by the media path. .EXAMPLE Invoke-SetupAction -Repair -InstanceName 'MyInstance' -Features 'SQLENGINE' -MediaPath 'E:\' Repairs the database engine of the instance 'MyInstance'. .EXAMPLE Invoke-SetupAction -RebuildDatabase -InstanceName 'MyInstance' -SqlSysAdminAccounts @('MyAdminAccount') -MediaPath 'E:\' Rebuilds the database of the instance 'MyInstance'. .EXAMPLE Invoke-SetupAction -InstallFailoverCluster -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -InstallSqlDataDir 'D:\MSSQL\Data' -SqlSysAdminAccounts @('MyAdminAccount') -FailoverClusterNetworkName 'TestCluster01A' -FailoverClusterIPAddresses 'IPv4;192.168.0.46;ClusterNetwork1;255.255.255.0' -MediaPath 'E:\' Installs the database engine in a failover cluster with the instance name 'MyInstance'. .EXAMPLE Invoke-SetupAction -PrepareFailoverCluster -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -MediaPath 'E:\' Prepares to installs the database engine in a failover cluster with the instance name 'MyInstance'. .EXAMPLE Invoke-SetupAction -CompleteFailoverCluster -InstanceName 'MyInstance' -InstallSqlDataDir 'D:\MSSQL\Data' -SqlSysAdminAccounts @('MyAdminAccount') -FailoverClusterNetworkName 'TestCluster01A' -FailoverClusterIPAddresses 'IPv4;192.168.0.46;ClusterNetwork1;255.255.255.0' -MediaPath 'E:\' Completes the install of the database engine in the failover cluster with the instance name 'MyInstance'. .EXAMPLE Invoke-SetupAction -AddNode -AcceptLicensingTerms -InstanceName 'MyInstance' -FailoverClusterIPAddresses 'IPv4;192.168.0.46;ClusterNetwork1;255.255.255.0' -MediaPath 'E:\' Adds the node to the failover cluster for the instance 'MyInstance'. .EXAMPLE Invoke-SetupAction -RemoveNode -InstanceName 'MyInstance' -MediaPath 'E:\' Removes the node from the failover cluster of the instance 'MyInstance'. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. For RebuildDatabase the parameter SAPwd must be set if the instance was installed with SecurityMode = 'SQL'. #> function Invoke-SetupAction { # cSpell: ignore PBDMS Admini AZUREEXTENSION [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $Install, [Parameter(ParameterSetName = 'Uninstall', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $Uninstall, [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $PrepareImage, [Parameter(ParameterSetName = 'CompleteImage', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $CompleteImage, [Parameter(ParameterSetName = 'Upgrade', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $Upgrade, [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $EditionUpgrade, [Parameter(ParameterSetName = 'Repair', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $Repair, [Parameter(ParameterSetName = 'RebuildDatabase', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $RebuildDatabase, [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $InstallFailoverCluster, [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $PrepareFailoverCluster, [Parameter(ParameterSetName = 'CompleteFailoverCluster', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $CompleteFailoverCluster, [Parameter(ParameterSetName = 'AddNode', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $AddNode, [Parameter(ParameterSetName = 'RemoveNode', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $RemoveNode, [Parameter(ParameterSetName = 'UsingConfigurationFile', Mandatory = $true)] [ValidateScript({ if (-not (Test-Path -Path $_)) { throw $script:localizedData.Server_ConfigurationFileNotFound } return $true })] [System.String] $ConfigurationFile, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'Upgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'AddNode', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteImage', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $AcceptLicensingTerms, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $SuppressPrivacyStatementNotice, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Management.Automation.SwitchParameter] $IAcknowledgeEntCalLimits, [Parameter(Mandatory = $true)] [ValidateScript({ if (-not (Test-Path -Path (Join-Path -Path $_ -ChildPath 'setup.exe'))) { throw $script:localizedData.Server_MediaPathNotFound } return $true })] [System.String] $MediaPath, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'Uninstall', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'Upgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'Repair', Mandatory = $true)] [Parameter(ParameterSetName = 'RebuildDatabase', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'AddNode', Mandatory = $true)] [Parameter(ParameterSetName = 'RemoveNode', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $InstanceName, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'Repair')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Management.Automation.SwitchParameter] $Enu, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Management.Automation.SwitchParameter] $UpdateEnabled, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.String] $UpdateSource, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'Repair', Mandatory = $true)] [Parameter(ParameterSetName = 'Uninstall', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole')] [ValidateSet( 'SQL', 'SQLEngine', # Part of parent feature SQL 'Replication', # Part of parent feature SQL 'FullText', # Part of parent feature SQL 'DQ', # Part of parent feature SQL 'PolyBase', # Part of parent feature SQL 'PolyBaseCore', # Part of parent feature SQL 'PolyBaseJava', # Part of parent feature SQL 'AdvancedAnalytics', # Part of parent feature SQL 'SQL_INST_MR', # Part of parent feature SQL 'SQL_INST_MPY', # Part of parent feature SQL 'SQL_INST_JAVA', # Part of parent feature SQL 'AS', 'RS', 'RS_SHP', 'RS_SHPWFE', # cspell: disable-line 'DQC', 'IS', 'IS_Master', # Part of parent feature IS 'IS_Worker', # Part of parent feature IS 'MDS', 'SQL_SHARED_MPY', 'SQL_SHARED_MR', 'Tools', 'BC', # Part of parent feature Tools 'Conn', # Part of parent feature Tools 'DREPLAY_CTLR', # Part of parent feature Tools (cspell: disable-line) 'DREPLAY_CLT', # Part of parent feature Tools (cspell: disable-line) 'SNAC_SDK', # Part of parent feature Tools (cspell: disable-line) 'SDK', # Part of parent feature Tools 'LocalDB', # Part of parent feature Tools 'AZUREEXTENSION' )] [System.String[]] $Features, [Parameter(ParameterSetName = 'InstallRole', Mandatory = $true)] [ValidateSet( 'ALLFeatures_WithDefaults', 'SPI_AS_NewFarm', 'SPI_AS_ExistingFarm' )] [System.String] $Role, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstallSharedDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstallSharedWowDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstanceDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstanceId, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Repair')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.String] $PBEngSvcAccount, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Repair')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Security.SecureString] $PBEngSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Repair')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $PBEngSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $PBDMSSvcAccount, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Security.SecureString] $PBDMSSvcPassword, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $PBDMSSvcStartupType, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Repair')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.UInt16] $PBStartPortRange, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Repair')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.UInt16] $PBEndPortRange, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Repair')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Management.Automation.SwitchParameter] $PBScaleOut, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [System.String] $ProductKey, # This is argument PID but $PID is reserved variable. [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.String] $AgtSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Security.SecureString] $AgtSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $AgtSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $ASBackupDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $ASCollation, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $ASConfigDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $ASDataDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $ASLogDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $ASTempDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [ValidateSet('Multidimensional', 'PowerPivot', 'Tabular')] [System.String] $ASServerMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.String] $ASSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Security.SecureString] $ASSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $ASSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String[]] $ASSysAdminAccounts, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.Management.Automation.SwitchParameter] $ASProviderMSOLAP, [Parameter(ParameterSetName = 'InstallRole')] [System.String] $FarmAccount, [Parameter(ParameterSetName = 'InstallRole')] [System.Security.SecureString] $FarmPassword, [Parameter(ParameterSetName = 'InstallRole')] [System.Security.SecureString] $Passphrase, [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 65536)] [System.UInt16] $FarmAdminiPort, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Upgrade')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $BrowserSvcStartupType, [Parameter(ParameterSetName = 'Upgrade')] [ValidateSet('Rebuild', 'Reset', 'Import')] [System.String] $FTUpgradeOption, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [System.Management.Automation.SwitchParameter] $EnableRanU, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteFailoverCluster', Mandatory = $true)] [System.String] $InstallSqlDataDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $SqlBackupDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [ValidateSet('SQL')] [System.String] $SecurityMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.Security.SecureString] $SAPwd, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $SqlCollation, [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $AddCurrentUserAsSqlAdmin, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.String] $SqlSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Security.SecureString] $SqlSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $SqlSvcStartupType, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole')] [System.String[]] $SqlSysAdminAccounts, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $SqlTempDbDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $SqlTempDbLogDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.UInt16] $SqlTempDbFileCount, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbFileSize, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbFileGrowth, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbLogFileSize, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'RebuildDatabase')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbLogFileGrowth, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $SqlUserDbDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $SqlSvcInstantFileInit, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $SqlUserDbLogDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 32767)] [System.UInt16] $SqlMaxDop, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $UseSqlRecommendedMemoryLimits, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 2147483647)] [System.UInt32] $SqlMinMemory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 2147483647)] [System.UInt32] $SqlMaxMemory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateRange(0, 3)] [System.UInt16] $FileStreamLevel, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $FileStreamShareName, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.String] $ISSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Security.SecureString] $ISSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $ISSvcStartupType, [Parameter(ParameterSetName = 'Upgrade')] [System.Management.Automation.SwitchParameter] $AllowUpgradeForSSRSSharePointMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [System.Management.Automation.SwitchParameter] $NpEnabled, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [System.Management.Automation.SwitchParameter] $TcpEnabled, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [ValidateSet('SharePointFilesOnlyMode', 'DefaultNativeMode', 'FilesOnlyMode')] [System.String] $RsInstallMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.String] $RSSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [System.Security.SecureString] $RSSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $RSSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $MPYCacheDirectory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $MRCacheDirectory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $SqlInstJava, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $SqlJavaDir, [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String] $FailoverClusterGroup, [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [System.String[]] $FailoverClusterDisks, [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteFailoverCluster', Mandatory = $true)] [System.String] $FailoverClusterNetworkName, [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'CompleteFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'AddNode', Mandatory = $true)] [System.String[]] $FailoverClusterIPAddresses, [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [Parameter(ParameterSetName = 'RemoveNode')] [System.Management.Automation.SwitchParameter] $ConfirmIPDependencyChange, [Parameter(ParameterSetName = 'Upgrade')] [ValidateRange(0, 2)] [System.UInt16] $FailoverClusterRollOwnership, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureSubscriptionId, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureResourceGroup, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureRegion, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureTenantId, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureServicePrincipal, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.Security.SecureString] $AzureServicePrincipalSecret, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent')] [System.String] $AzureArcProxy, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'EditionUpgrade')] [System.String[]] $SkipRules, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'CompleteImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'CompleteFailoverCluster')] [Parameter(ParameterSetName = 'AddNode')] [Parameter(ParameterSetName = 'EditionUpgrade')] [System.Management.Automation.SwitchParameter] $ProductCoveredBySA, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) if ($Force.IsPresent) { $ConfirmPreference = 'None' } Assert-ElevatedUser -ErrorAction 'Stop' switch ($PSCmdlet.ParameterSetName) { 'InstallRole' { $setupAction = 'Install' break } 'InstallAzureArcAgent' { $setupAction = 'Install' <# For this setup action the parameter Features is not part of the parameter set, so this can be safely set. #> $PSBoundParameters.Features = @('AZUREEXTENSION') break } default { $setupAction = $PSCmdlet.ParameterSetName break } } Assert-SetupActionProperties -Property $PSBoundParameters -SetupAction $setupAction -ErrorAction 'Stop' $setupArgument = '/QUIET /ACTION={0}' -f $setupAction if ($DebugPreference -in @('Continue', 'Inquire')) { $setupArgument += ' /INDICATEPROGRESS' # cspell: disable-line } if ($AcceptLicensingTerms.IsPresent) { $setupArgument += ' /IACCEPTSQLSERVERLICENSETERMS' # cspell: disable-line if ($PSBoundParameters.ContainsKey('Features')) { if ($PSBoundParameters.Features -contains 'SQL_SHARED_MR' ) { $setupArgument += ' /IACCEPTROPENLICENSETERMS' # cspell: disable-line } if ($PSBoundParameters.Features -contains 'SQL_SHARED_MPY' ) { $setupArgument += ' /IACCEPTPYTHONLICENSETERMS' # cspell: disable-line } } } $ignoreParameters = @( $PSCmdlet.ParameterSetName 'Install' # Must add this exclusively because of parameter set InstallAzureArcAgent 'AcceptLicensingTerms' 'MediaPath' 'Timeout' 'Force' ) $ignoreParameters += [System.Management.Automation.PSCmdlet]::CommonParameters $ignoreParameters += [System.Management.Automation.PSCmdlet]::OptionalCommonParameters $boundParameterName = $PSBoundParameters.Keys.Where({ $_ -notin $ignoreParameters }) $sensitiveValue = @() $pathParameter = @( 'InstallSharedDir' 'InstallSharedWowDir' 'InstanceDir' 'ASBackupDir' 'ASConfigDir' 'ASDataDir' 'ASLogDir' 'ASTempDir' 'InstallSqlDataDir' 'SqlBackupDir' 'SqlTempDbDir' 'SqlTempDbLogDir' 'SqlUserDbDir' 'SqlUserDbLogDir' 'MPYCacheDirectory' 'MRCacheDirectory' 'SqlJavaDir' ) <# Remove trialing backslash from paths so they are not interpreted as escape-characters for a double-quote. See issue https://github.com/dsccommunity/SqlServerDsc/issues/1254. #> $boundParameterName.Where( { $_ -in $pathParameter } ).ForEach({ # Must not change paths that reference a root directory (they are handle differently later) if ($PSBoundParameters.$_ -notmatch '^[a-zA-Z]:\\$') { $PSBoundParameters.$_ = $PSBoundParameters.$_.TrimEnd('\') } }) # Loop through all bound parameters and build arguments for the setup executable. foreach ($parameterName in $boundParameterName) { # Make sure parameter is upper-case. $parameterName = $parameterName.ToUpper() $setupArgument += ' /{0}' -f $parameterName switch ($parameterName) { <# Must be handled differently because it is an array and have a comma separating the values, and the value shall be upper-case. #> { $_ -in @('FEATURES', 'ROLE') } { $setupArgument += '={0}' -f ($PSBoundParameters.$parameterName.ToUpper() -join ',') break } # Must be handled differently because the value MUST be upper-case. 'ASSERVERMODE' # cspell: disable-line { $setupArgument += '={0}' -f $PSBoundParameters.$parameterName.ToUpper() break } # Must be handled differently because the parameter name could not be $PID. 'PRODUCTKEY' # cspell: disable-line { # Remove the argument that was added above. $setupArgument = $setupArgument -replace ' \/{0}' -f $parameterName $sensitiveValue += $PSBoundParameters.$parameterName $setupArgument += ' /PID="{0}"' -f $PSBoundParameters.$parameterName break } # Must be handled differently because the argument name shall have an underscore in the argument. 'SQLINSTJAVA' # cspell: disable-line { # Remove the argument that was added above. $setupArgument = $setupArgument -replace ' \/{0}' -f $parameterName $setupArgument += ' /SQL_INST_JAVA' break } # Must be handled differently because each value shall be separated by a semi-colon. 'FAILOVERCLUSTERDISKS' # cspell: disable-line { $setupArgument += '="{0}"' -f ($PSBoundParameters.$parameterName -join ';') break } # Must be handled differently because two parameters shall become one argument. { $_ -in ('PBSTARTPORTRANGE', 'PBENDPORTRANGE') } # cspell: disable-line { # Remove the argument that was added above. $setupArgument = $setupArgument -replace ' \/{0}' -f $parameterName # Only set argument if it is not present already. if ($setupArgument -notmatch '\/PBPORTRANGE') # cspell: disable-line { # cspell: disable-next $setupArgument += ' /PBPORTRANGE={0}-{1}' -f $PSBoundParameters.PBStartPortRange, $PSBoundParameters.PBEndPortRange } break } { $PSBoundParameters.$parameterName -is [System.Management.Automation.SwitchParameter] } { <# If a switch parameter is not included below then those arguments shall not have any value after argument name, e.g. '/ENU'. #> switch ($parameterName) { # Arguments that shall have the value set to the boolean numeric representation. { $parameterName -in ('ASPROVIDERMSOLAP', 'NPENABLED', 'TCPENABLED', 'CONFIRMIPDEPENDENCYCHANGE') } # cspell: disable-line { $setupArgument += '={0}' -f [System.Byte] $PSBoundParameters.$parameterName.ToBool() break } <# Arguments that shall have the value set to the boolean string representation. Excluding parameter names that shall be handled differently, those arguments shall not have any value after argument name, e.g. '/ENU'. #> { $parameterName -in @('UPDATEENABLED', 'PBSCALEOUT', 'SQLSVCINSTANTFILEINIT', 'ALLOWUPGRADEFORSSRSSHAREPOINTMODE', 'ADDCURRENTUSERASSQLADMIN', 'IACKNOWLEDGEENTCALLIMITS') } # cspell: disable-line { $setupArgument += '={0}' -f $PSBoundParameters.$parameterName.ToString() break } } break } <# Must be handled differently because it is an numeric value and does not need to be surrounded by double-quote. #> { $PSBoundParameters.$parameterName | Test-IsNumericType } { $setupArgument += '={0}' -f ($PSBoundParameters.$parameterName -join '" "') break } <# Must be handled differently because it is an array and have a space separating the values, and each value is surrounded by double-quote. #> { $PSBoundParameters.$parameterName -is [System.Array] } { $setupArgument += '="{0}"' -f ($PSBoundParameters.$parameterName -join '" "') break } { $PSBoundParameters.$parameterName -is [System.Security.SecureString] } { $passwordClearText = $PSBoundParameters.$parameterName | ConvertFrom-SecureString -AsPlainText $sensitiveValue += $passwordClearText $setupArgument += '="{0}"' -f $passwordClearText break } default { <# When there is backslash followed by a double-quote then the backslash is treated as an escape character for the double-quote. For arguments that holds a path and the value references a root directory, e.g. 'E:\', then the value must not be surrounded by double-quotes. Other paths should be surrounded by double-quotes as they can contain spaces. See issue https://github.com/dsccommunity/SqlServerDsc/issues/1254. #> if ($PSBoundParameters.$parameterName -match '^[a-zA-Z]:\\$') { $setupArgument += '={0}' -f $PSBoundParameters.$parameterName } else { $setupArgument += '="{0}"' -f $PSBoundParameters.$parameterName } break } } } $verboseSetupArgument = $setupArgument # Obfuscate sensitive values. foreach ($currentSensitiveValue in $sensitiveValue) { $escapedRegExString = [System.Text.RegularExpressions.Regex]::Escape($currentSensitiveValue) $verboseSetupArgument = $verboseSetupArgument -replace $escapedRegExString, '********' } # Clear sensitive values. $sensitiveValue = $null Write-Verbose -Message ($script:localizedData.Server_SetupArguments -f $verboseSetupArgument) $verboseDescriptionMessage = $script:localizedData.Server_Install_ShouldProcessVerboseDescription -f $PSCmdlet.ParameterSetName $verboseWarningMessage = $script:localizedData.Server_Install_ShouldProcessVerboseWarning -f $PSCmdlet.ParameterSetName $captionMessage = $script:localizedData.Server_Install_ShouldProcessCaption if ($PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { $startProcessParameters = @{ FilePath = Join-Path -Path $MediaPath -ChildPath 'setup.exe' ArgumentList = $setupArgument Timeout = $Timeout } # Clear setupArgument to remove any sensitive values. $setupArgument = $null # Run setup executable. $processExitCode = Start-SqlSetupProcess @startProcessParameters $setupExitMessage = ($script:localizedData.Server_SetupExitMessage -f $processExitCode) if ($processExitCode -eq 3010) { Write-Warning -Message ( '{0} {1}' -f $setupExitMessage, $script:localizedData.Server_SetupSuccessfulRebootRequired ) } elseif ($processExitCode -ne 0) { $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( ('{0} {1}' -f $setupExitMessage, $script:localizedData.Server_SetupFailed), 'ISA0001', # cspell: disable-line [System.Management.Automation.ErrorCategory]::InvalidOperation, $InstanceName ) ) } else { Write-Verbose -Message ( '{0} {1}' -f $setupExitMessage, ($script:localizedData.Server_SetupSuccessful) ) } } } #EndRegion '.\Private\Invoke-SetupAction.ps1' 1714 #Region '.\Public\Add-SqlDscNode.ps1' 0 <# .SYNOPSIS Add a SQL Server node to an Failover Cluster instance (FCI). .DESCRIPTION Add a SQL Server node to an Failover Cluster instance (FCI). See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER AcceptLicensingTerms Required parameter to be able to run unattended install. By specifying this parameter you acknowledge the acceptance all license terms and notices for the specified features, the terms and notices that the Microsoft SQL Server setup executable normally ask for. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER IAcknowledgeEntCalLimits See the notes section for more information. .PARAMETER InstanceName See the notes section for more information. .PARAMETER Enu See the notes section for more information. .PARAMETER UpdateEnabled See the notes section for more information. .PARAMETER UpdateSource See the notes section for more information. .PARAMETER PBEngSvcAccount See the notes section for more information. .PARAMETER PBEngSvcPassword See the notes section for more information. .PARAMETER PBEngSvcStartupType See the notes section for more information. .PARAMETER PBStartPortRange See the notes section for more information. .PARAMETER PBEndPortRange See the notes section for more information. .PARAMETER PBScaleOut See the notes section for more information. .PARAMETER ProductKey See the notes section for more information. .PARAMETER AgtSvcAccount See the notes section for more information. .PARAMETER AgtSvcPassword See the notes section for more information. .PARAMETER ASSvcAccount See the notes section for more information. .PARAMETER ASSvcPassword See the notes section for more information. .PARAMETER SqlSvcAccount See the notes section for more information. .PARAMETER SqlSvcPassword See the notes section for more information. .PARAMETER ISSvcAccount See the notes section for more information. .PARAMETER ISSvcPassword See the notes section for more information. .PARAMETER RsInstallMode See the notes section for more information. .PARAMETER RSSvcAccount See the notes section for more information. .PARAMETER RSSvcPassword See the notes section for more information. .PARAMETER FailoverClusterIPAddresses See the notes section for more information. .PARAMETER ConfirmIPDependencyChange See the notes section for more information. .PARAMETER ProductCoveredBySA See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Add-SqlDscNode -AcceptLicensingTerms -InstanceName 'MyInstance' -FailoverClusterIPAddresses 'IPv4;192.168.0.46;ClusterNetwork1;255.255.255.0' -MediaPath 'E:\' Adds the current node's SQL Server instance 'MyInstance' to the Failover Cluster instance. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. #> function Add-SqlDscNode { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.Management.Automation.SwitchParameter] $AcceptLicensingTerms, [Parameter()] [System.Management.Automation.SwitchParameter] $IAcknowledgeEntCalLimits, [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter()] [System.Management.Automation.SwitchParameter] $Enu, [Parameter()] [System.Management.Automation.SwitchParameter] $UpdateEnabled, [Parameter()] [System.String] $UpdateSource, [Parameter()] [System.String] $PBEngSvcAccount, [Parameter()] [System.Security.SecureString] $PBEngSvcPassword, [Parameter()] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $PBEngSvcStartupType, [Parameter()] [System.UInt16] $PBStartPortRange, [Parameter()] [System.UInt16] $PBEndPortRange, [Parameter()] [System.Management.Automation.SwitchParameter] $PBScaleOut, [Parameter()] [System.String] $ProductKey, # This is argument PID but $PID is reserved variable. [Parameter()] [System.String] $AgtSvcAccount, [Parameter()] [System.Security.SecureString] $AgtSvcPassword, [Parameter()] [System.String] $ASSvcAccount, [Parameter()] [System.Security.SecureString] $ASSvcPassword, [Parameter()] [System.String] $SqlSvcAccount, [Parameter()] [System.Security.SecureString] $SqlSvcPassword, [Parameter()] [System.String] $ISSvcAccount, [Parameter()] [System.Security.SecureString] $ISSvcPassword, [Parameter()] [ValidateSet('SharePointFilesOnlyMode', 'DefaultNativeMode', 'FilesOnlyMode')] [System.String] $RsInstallMode, [Parameter()] [System.String] $RSSvcAccount, [Parameter()] [System.Security.SecureString] $RSSvcPassword, [Parameter(Mandatory = $true)] [System.String[]] $FailoverClusterIPAddresses, [Parameter()] [System.Management.Automation.SwitchParameter] $ConfirmIPDependencyChange, [Parameter()] [System.Management.Automation.SwitchParameter] $ProductCoveredBySA, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction -AddNode @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Add-SqlDscNode.ps1' 257 #Region '.\Public\Complete-SqlDscFailoverCluster.ps1' 0 <# .SYNOPSIS Completes the SQL Server instance installation in the Failover Cluster instance. .DESCRIPTION Completes the SQL Server instance installation in the Failover Cluster instance that was prepared using `Install-SqlDscServer` with the parameter `-PrepareFailoverCluster`. See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER InstanceName See the notes section for more information. .PARAMETER Enu See the notes section for more information. .PARAMETER ProductKey See the notes section for more information. .PARAMETER ASBackupDir See the notes section for more information. .PARAMETER ASCollation See the notes section for more information. .PARAMETER ASConfigDir See the notes section for more information. .PARAMETER ASDataDir See the notes section for more information. .PARAMETER ASLogDir See the notes section for more information. .PARAMETER ASTempDir See the notes section for more information. .PARAMETER ASServerMode See the notes section for more information. .PARAMETER ASSysAdminAccounts See the notes section for more information. .PARAMETER ASProviderMSOLAP See the notes section for more information. .PARAMETER InstallSqlDataDir See the notes section for more information. .PARAMETER SqlBackupDir See the notes section for more information. .PARAMETER SecurityMode See the notes section for more information. .PARAMETER SAPwd See the notes section for more information. .PARAMETER SqlCollation See the notes section for more information. .PARAMETER SqlSysAdminAccounts See the notes section for more information. .PARAMETER SqlTempDbDir See the notes section for more information. .PARAMETER SqlTempDbLogDir See the notes section for more information. .PARAMETER SqlTempDbFileCount See the notes section for more information. .PARAMETER SqlTempDbFileSize See the notes section for more information. .PARAMETER SqlTempDbFileGrowth See the notes section for more information. .PARAMETER SqlTempDbLogFileSize See the notes section for more information. .PARAMETER SqlTempDbLogFileGrowth See the notes section for more information. .PARAMETER SqlUserDbDir See the notes section for more information. .PARAMETER SqlUserDbLogDir See the notes section for more information. .PARAMETER RsInstallMode See the notes section for more information. .PARAMETER FailoverClusterGroup See the notes section for more information. .PARAMETER FailoverClusterDisks See the notes section for more information. .PARAMETER FailoverClusterNetworkName See the notes section for more information. .PARAMETER FailoverClusterIPAddresses See the notes section for more information. .PARAMETER ConfirmIPDependencyChange See the notes section for more information. .PARAMETER ProductCoveredBySA See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Complete-SqlDscFailoverCluster -InstanceName 'MyInstance' -InstallSqlDataDir 'D:\MSSQL\Data' -SqlSysAdminAccounts @('MyAdminAccount') -FailoverClusterNetworkName 'TestCluster01A' -FailoverClusterIPAddresses 'IPv4;192.168.0.46;ClusterNetwork1;255.255.255.0' -MediaPath 'E:\' Completes the installation of the SQL Server instance 'MyInstance' in the Failover Cluster instance. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. #> function Complete-SqlDscFailoverCluster { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter()] [System.Management.Automation.SwitchParameter] $Enu, [Parameter()] [System.String] $ProductKey, # This is argument PID but $PID is reserved variable. [Parameter()] [System.String] $ASBackupDir, [Parameter()] [System.String] $ASCollation, [Parameter()] [System.String] $ASConfigDir, [Parameter()] [System.String] $ASDataDir, [Parameter()] [System.String] $ASLogDir, [Parameter()] [System.String] $ASTempDir, [Parameter()] [ValidateSet('Multidimensional', 'PowerPivot', 'Tabular')] [System.String] $ASServerMode, [Parameter()] [System.String[]] $ASSysAdminAccounts, [Parameter()] [System.Management.Automation.SwitchParameter] $ASProviderMSOLAP, [Parameter(Mandatory = $true)] [System.String] $InstallSqlDataDir, [Parameter()] [System.String] $SqlBackupDir, [Parameter()] [ValidateSet('SQL')] [System.String] $SecurityMode, [Parameter()] [System.Security.SecureString] $SAPwd, [Parameter()] [System.String] $SqlCollation, [Parameter(Mandatory = $true)] [System.String[]] $SqlSysAdminAccounts, [Parameter()] [System.String] $SqlTempDbDir, [Parameter()] [System.String] $SqlTempDbLogDir, [Parameter()] [System.UInt16] $SqlTempDbFileCount, [Parameter()] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbFileSize, [Parameter()] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbFileGrowth, [Parameter()] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbLogFileSize, [Parameter()] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbLogFileGrowth, [Parameter()] [System.String] $SqlUserDbDir, [Parameter()] [System.String] $SqlUserDbLogDir, [Parameter()] [ValidateSet('SharePointFilesOnlyMode', 'DefaultNativeMode', 'FilesOnlyMode')] [System.String] $RsInstallMode, [Parameter()] [System.String] $FailoverClusterGroup, [Parameter()] [System.String[]] $FailoverClusterDisks, [Parameter(Mandatory = $true)] [System.String] $FailoverClusterNetworkName, [Parameter(Mandatory = $true)] [System.String[]] $FailoverClusterIPAddresses, [Parameter()] [System.Management.Automation.SwitchParameter] $ConfirmIPDependencyChange, [Parameter()] [System.Management.Automation.SwitchParameter] $ProductCoveredBySA, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction -CompleteFailoverCluster @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Complete-SqlDscFailoverCluster.ps1' 311 #Region '.\Public\Complete-SqlDscImage.ps1' 0 <# .SYNOPSIS Completes the image installation of an SQL Server instance. .DESCRIPTION Completes the image installation of an SQL Server instance that was prepared using `Install-SqlDscServer` with the parameter `-PrepareImage`. See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER AcceptLicensingTerms Required parameter to be able to run unattended install. By specifying this parameter you acknowledge the acceptance all license terms and notices for the specified features, the terms and notices that the Microsoft SQL Server setup executable normally ask for. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER InstanceName See the notes section for more information. .PARAMETER Enu See the notes section for more information. .PARAMETER InstanceId See the notes section for more information. .PARAMETER PBEngSvcAccount See the notes section for more information. .PARAMETER PBEngSvcPassword See the notes section for more information. .PARAMETER PBEngSvcStartupType See the notes section for more information. .PARAMETER PBStartPortRange See the notes section for more information. .PARAMETER PBEndPortRange See the notes section for more information. .PARAMETER PBScaleOut See the notes section for more information. .PARAMETER ProductKey See the notes section for more information. .PARAMETER AgtSvcAccount See the notes section for more information. .PARAMETER AgtSvcPassword See the notes section for more information. .PARAMETER AgtSvcStartupType See the notes section for more information. .PARAMETER BrowserSvcStartupType See the notes section for more information. .PARAMETER EnableRanU See the notes section for more information. .PARAMETER InstallSqlDataDir See the notes section for more information. .PARAMETER SqlBackupDir See the notes section for more information. .PARAMETER SecurityMode See the notes section for more information. .PARAMETER SAPwd See the notes section for more information. .PARAMETER SqlCollation See the notes section for more information. .PARAMETER SqlSvcAccount See the notes section for more information. .PARAMETER SqlSvcPassword See the notes section for more information. .PARAMETER SqlSvcStartupType See the notes section for more information. .PARAMETER SqlSysAdminAccounts See the notes section for more information. .PARAMETER SqlTempDbDir See the notes section for more information. .PARAMETER SqlTempDbLogDir See the notes section for more information. .PARAMETER SqlTempDbFileCount See the notes section for more information. .PARAMETER SqlTempDbFileSize See the notes section for more information. .PARAMETER SqlTempDbFileGrowth See the notes section for more information. .PARAMETER SqlTempDbLogFileSize See the notes section for more information. .PARAMETER SqlTempDbLogFileGrowth See the notes section for more information. .PARAMETER SqlUserDbDir See the notes section for more information. .PARAMETER SqlUserDbLogDir See the notes section for more information. .PARAMETER FileStreamLevel See the notes section for more information. .PARAMETER FileStreamShareName See the notes section for more information. .PARAMETER NpEnabled See the notes section for more information. .PARAMETER TcpEnabled See the notes section for more information. .PARAMETER RsInstallMode See the notes section for more information. .PARAMETER RSSvcAccount See the notes section for more information. .PARAMETER RSSvcPassword See the notes section for more information. .PARAMETER RSSvcStartupType See the notes section for more information. .PARAMETER ProductCoveredBySA See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Complete-SqlDscImage -AcceptLicensingTerms -MediaPath 'E:\' Completes the image installation of the SQL Server default instance that was prepared using `Install-SqlDscServer` with the parameter `-PrepareImage`. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. #> function Complete-SqlDscImage { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.Management.Automation.SwitchParameter] $AcceptLicensingTerms, [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter()] [System.String] $InstanceName, [Parameter()] [System.Management.Automation.SwitchParameter] $Enu, [Parameter()] [System.String] $InstanceId, [Parameter()] [System.String] $PBEngSvcAccount, [Parameter()] [System.Security.SecureString] $PBEngSvcPassword, [Parameter()] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $PBEngSvcStartupType, [Parameter()] [System.UInt16] $PBStartPortRange, [Parameter()] [System.UInt16] $PBEndPortRange, [Parameter()] [System.Management.Automation.SwitchParameter] $PBScaleOut, [Parameter()] [System.String] $ProductKey, # This is argument PID but $PID is reserved variable. [Parameter()] [System.String] $AgtSvcAccount, [Parameter()] [System.Security.SecureString] $AgtSvcPassword, [Parameter()] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $AgtSvcStartupType, [Parameter()] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $BrowserSvcStartupType, [Parameter()] [System.Management.Automation.SwitchParameter] $EnableRanU, [Parameter()] [System.String] $InstallSqlDataDir, [Parameter()] [System.String] $SqlBackupDir, [Parameter()] [ValidateSet('SQL')] [System.String] $SecurityMode, [Parameter()] [System.Security.SecureString] $SAPwd, [Parameter()] [System.String] $SqlCollation, [Parameter()] [System.String] $SqlSvcAccount, [Parameter()] [System.Security.SecureString] $SqlSvcPassword, [Parameter()] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $SqlSvcStartupType, [Parameter()] [System.String[]] $SqlSysAdminAccounts, [Parameter()] [System.String] $SqlTempDbDir, [Parameter()] [System.String] $SqlTempDbLogDir, [Parameter()] [System.UInt16] $SqlTempDbFileCount, [Parameter()] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbFileSize, [Parameter()] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbFileGrowth, [Parameter()] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbLogFileSize, [Parameter()] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbLogFileGrowth, [Parameter()] [System.String] $SqlUserDbDir, [Parameter()] [System.String] $SqlUserDbLogDir, [Parameter()] [ValidateRange(0, 3)] [System.UInt16] $FileStreamLevel, [Parameter()] [System.String] $FileStreamShareName, [Parameter()] [System.Management.Automation.SwitchParameter] $NpEnabled, [Parameter()] [System.Management.Automation.SwitchParameter] $TcpEnabled, [Parameter()] [ValidateSet('SharePointFilesOnlyMode', 'DefaultNativeMode', 'FilesOnlyMode')] [System.String] $RsInstallMode, [Parameter()] [System.String] $RSSvcAccount, [Parameter()] [System.Security.SecureString] $RSSvcPassword, [Parameter()] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $RSSvcStartupType, [Parameter()] [System.Management.Automation.SwitchParameter] $ProductCoveredBySA, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction -CompleteImage @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Complete-SqlDscImage.ps1' 380 #Region '.\Public\Connect-SqlDscDatabaseEngine.ps1' 0 <# .SYNOPSIS Connect to a SQL Server Database Engine and return the server object. .DESCRIPTION This command connects to a SQL Server Database Engine instance and returns the Server object. .PARAMETER ServerName String containing the host name of the SQL Server to connect to. Default value is the current computer name. .PARAMETER InstanceName String containing the SQL Server Database Engine instance to connect to. Default value is 'MSSQLSERVER'. .PARAMETER Credential The credentials to use to impersonate a user when connecting to the SQL Server Database Engine instance. If this parameter is left out, then the current user will be used to connect to the SQL Server Database Engine instance using Windows Integrated authentication. .PARAMETER LoginType Specifies which type of logon credential should be used. The valid types are 'WindowsUser' or 'SqlLogin'. Default value is 'WindowsUser' If set to 'WindowsUser' then the it will impersonate using the Windows login specified in the parameter Credential. If set to 'WindowsUser' then the it will impersonate using the native SQL login specified in the parameter Credential. .PARAMETER StatementTimeout Set the query StatementTimeout in seconds. Default 600 seconds (10 minutes). .EXAMPLE Connect-SqlDscDatabaseEngine Connects to the default instance on the local server. .EXAMPLE Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' Connects to the instance 'MyInstance' on the local server. .EXAMPLE Connect-SqlDscDatabaseEngine -ServerName 'sql.company.local' -InstanceName 'MyInstance' Connects to the instance 'MyInstance' on the server 'sql.company.local'. .OUTPUTS `[Microsoft.SqlServer.Management.Smo.Server]` #> function Connect-SqlDscDatabaseEngine { [CmdletBinding(DefaultParameterSetName = 'SqlServer')] param ( [Parameter(ParameterSetName = 'SqlServer')] [Parameter(ParameterSetName = 'SqlServerWithCredential')] [ValidateNotNull()] [System.String] $ServerName = (Get-ComputerName), [Parameter(ParameterSetName = 'SqlServer')] [Parameter(ParameterSetName = 'SqlServerWithCredential')] [ValidateNotNull()] [System.String] $InstanceName = 'MSSQLSERVER', [Parameter(ParameterSetName = 'SqlServerWithCredential', Mandatory = $true)] [ValidateNotNull()] [Alias('SetupCredential', 'DatabaseCredential')] [System.Management.Automation.PSCredential] $Credential, [Parameter(ParameterSetName = 'SqlServerWithCredential')] [ValidateSet('WindowsUser', 'SqlLogin')] [System.String] $LoginType = 'WindowsUser', [Parameter()] [ValidateNotNull()] [System.Int32] $StatementTimeout = 600 ) # Call the private function. return (Connect-Sql @PSBoundParameters) } #EndRegion '.\Public\Connect-SqlDscDatabaseEngine.ps1' 89 #Region '.\Public\ConvertFrom-SqlDscDatabasePermission.ps1' 0 <# .SYNOPSIS Converts a DatabasePermission object into an object of the type Microsoft.SqlServer.Management.Smo.DatabasePermissionSet. .DESCRIPTION Converts a DatabasePermission object into an object of the type Microsoft.SqlServer.Management.Smo.DatabasePermissionSet. .PARAMETER Permission Specifies a DatabasePermission object. .EXAMPLE [DatabasePermission] @{ State = 'Grant' Permission = 'Connect' } | ConvertFrom-SqlDscDatabasePermission Returns an object of `[Microsoft.SqlServer.Management.Smo.DatabasePermissionSet]` with all the permissions set to $true that was part of the `[DatabasePermission]`. .OUTPUTS [Microsoft.SqlServer.Management.Smo.DatabasePermissionSet] #> function ConvertFrom-SqlDscDatabasePermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when the output type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [CmdletBinding()] [OutputType([Microsoft.SqlServer.Management.Smo.DatabasePermissionSet])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [DatabasePermission] $Permission ) begin { $permissionSet = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.DatabasePermissionSet' } process { foreach ($permissionName in $Permission.Permission) { $permissionSet.$permissionName = $true } } end { return $permissionSet } } #EndRegion '.\Public\ConvertFrom-SqlDscDatabasePermission.ps1' 55 #Region '.\Public\ConvertFrom-SqlDscServerPermission.ps1' 0 <# .SYNOPSIS Converts a ServerPermission object into an object of the type Microsoft.SqlServer.Management.Smo.ServerPermissionSet. .DESCRIPTION Converts a ServerPermission object into an object of the type Microsoft.SqlServer.Management.Smo.ServerPermissionSet. .PARAMETER Permission Specifies a ServerPermission object. .EXAMPLE [ServerPermission] @{ State = 'Grant' Permission = 'Connect' } | ConvertFrom-SqlDscServerPermission Returns an object of `[Microsoft.SqlServer.Management.Smo.ServerPermissionSet]` with all the permissions set to $true that was part of the `[ServerPermission]`. .OUTPUTS [Microsoft.SqlServer.Management.Smo.ServerPermissionSet] #> function ConvertFrom-SqlDscServerPermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when the output type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [CmdletBinding()] [OutputType([Microsoft.SqlServer.Management.Smo.ServerPermissionSet])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [ServerPermission] $Permission ) begin { $permissionSet = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.ServerPermissionSet' } process { foreach ($permissionName in $Permission.Permission) { $permissionSet.$permissionName = $true } } end { return $permissionSet } } #EndRegion '.\Public\ConvertFrom-SqlDscServerPermission.ps1' 55 #Region '.\Public\ConvertTo-SqlDscDatabasePermission.ps1' 0 <# .SYNOPSIS Converts a collection of Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo objects into an array of DatabasePermission objects. .DESCRIPTION Converts a collection of Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo objects into an array of DatabasePermission objects. .PARAMETER DatabasePermissionInfo Specifies a collection of Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo objects. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine $databasePermissionInfo = Get-SqlDscDatabasePermission -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' ConvertTo-SqlDscDatabasePermission -DatabasePermissionInfo $databasePermissionInfo Get all permissions for the principal 'MyPrincipal' and converts the permissions into an array of `[DatabasePermission[]]`. .OUTPUTS [DatabasePermission[]] #> function ConvertTo-SqlDscDatabasePermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when the output type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [CmdletBinding()] [OutputType([DatabasePermission[]])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [AllowEmptyCollection()] [Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo[]] $DatabasePermissionInfo ) begin { [DatabasePermission[]] $permissions = @() } process { $permissionState = foreach ($currentDatabasePermissionInfo in $DatabasePermissionInfo) { # Convert from the type PermissionState to String. [System.String] $currentDatabasePermissionInfo.PermissionState } $permissionState = $permissionState | Select-Object -Unique foreach ($currentPermissionState in $permissionState) { $filteredDatabasePermission = $DatabasePermissionInfo | Where-Object -FilterScript { $_.PermissionState -eq $currentPermissionState } $databasePermissionStateExist = $permissions.Where({ $_.State -contains $currentPermissionState }) | Select-Object -First 1 if ($databasePermissionStateExist) { $databasePermission = $databasePermissionStateExist } else { $databasePermission = [DatabasePermission] @{ State = $currentPermissionState Permission = [System.String[]] @() } } foreach ($currentPermission in $filteredDatabasePermission) { # Get the permission names that is set to $true $permissionProperty = $currentPermission.PermissionType | Get-Member -MemberType 'Property' | Select-Object -ExpandProperty 'Name' -Unique | Where-Object -FilterScript { $currentPermission.PermissionType.$_ } foreach ($currentPermissionProperty in $permissionProperty) { $databasePermission.Permission += $currentPermissionProperty } } # Only add the object if it was created. if (-not $databasePermissionStateExist) { $permissions += $databasePermission } } } end { return $permissions } } #EndRegion '.\Public\ConvertTo-SqlDscDatabasePermission.ps1' 107 #Region '.\Public\ConvertTo-SqlDscServerPermission.ps1' 0 <# .SYNOPSIS Converts a collection of Microsoft.SqlServer.Management.Smo.ServerPermissionInfo objects into an array of ServerPermission objects. .DESCRIPTION Converts a collection of Microsoft.SqlServer.Management.Smo.ServerPermissionInfo objects into an array of ServerPermission objects. .PARAMETER ServerPermissionInfo Specifies a collection of Microsoft.SqlServer.Management.Smo.ServerPermissionInfo objects. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine $serverPermissionInfo = Get-SqlDscServerPermission -ServerObject $serverInstance -Name 'MyPrincipal' ConvertTo-SqlDscServerPermission -ServerPermissionInfo $serverPermissionInfo Get all permissions for the principal 'MyPrincipal' and converts the permissions into an array of `[ServerPermission[]]`. .OUTPUTS [ServerPermission[]] #> function ConvertTo-SqlDscServerPermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when the output type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [CmdletBinding()] [OutputType([ServerPermission[]])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [AllowEmptyCollection()] [Microsoft.SqlServer.Management.Smo.ServerPermissionInfo[]] $ServerPermissionInfo ) begin { [ServerPermission[]] $permissions = @() } process { $permissionState = foreach ($currentServerPermissionInfo in $ServerPermissionInfo) { # Convert from the type PermissionState to String. [System.String] $currentServerPermissionInfo.PermissionState } $permissionState = $permissionState | Select-Object -Unique foreach ($currentPermissionState in $permissionState) { $filteredServerPermission = $ServerPermissionInfo | Where-Object -FilterScript { $_.PermissionState -eq $currentPermissionState } $serverPermissionStateExist = $permissions.Where({ $_.State -contains $currentPermissionState }) | Select-Object -First 1 if ($serverPermissionStateExist) { $serverPermission = $serverPermissionStateExist } else { $serverPermission = [ServerPermission] @{ State = $currentPermissionState Permission = [System.String[]] @() } } foreach ($currentPermission in $filteredServerPermission) { # Get the permission names that is set to $true $permissionProperty = $currentPermission.PermissionType | Get-Member -MemberType 'Property' | Select-Object -ExpandProperty 'Name' -Unique | Where-Object -FilterScript { $currentPermission.PermissionType.$_ } foreach ($currentPermissionProperty in $permissionProperty) { $serverPermission.Permission += $currentPermissionProperty } } # Only add the object if it was created. if (-not $serverPermissionStateExist) { $permissions += $serverPermission } } } end { return $permissions } } #EndRegion '.\Public\ConvertTo-SqlDscServerPermission.ps1' 107 #Region '.\Public\Disable-SqlDscAudit.ps1' 0 <# .SYNOPSIS Disables a server audit. .DESCRIPTION This command disables a server audit in a SQL Server Database Engine instance. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER AuditObject Specifies an audit object to disable. .PARAMETER Name Specifies the name of the server audit to be disabled. .PARAMETER Force Specifies that the audit should be disabled with out any confirmation. .PARAMETER Refresh Specifies that the **ServerObject**'s audits should be refreshed before trying to disable the audit object. This is helpful when audits could have been modified outside of the **ServerObject**, for example through T-SQL. But on instances with a large amount of audits it might be better to make sure the **ServerObject** is recent enough, or pass in **AuditObject**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $auditObject = $sqlServerObject | Get-SqlDscAudit -Name 'MyFileAudit' $auditObject | Disable-SqlDscAudit Disables the audit named **MyFileAudit**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | Disable-SqlDscAudit -Name 'MyFileAudit' Disables the audit named **MyFileAudit**. .OUTPUTS None. #> function Disable-SqlDscAudit { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [OutputType()] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] param ( [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(ParameterSetName = 'AuditObject', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Audit] $AuditObject, [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true)] [System.String] $Name, [Parameter()] [System.Management.Automation.SwitchParameter] $Force, [Parameter(ParameterSetName = 'ServerObject')] [System.Management.Automation.SwitchParameter] $Refresh ) process { if ($Force.IsPresent) { $ConfirmPreference = 'None' } if ($PSCmdlet.ParameterSetName -eq 'ServerObject') { $getSqlDscAuditParameters = @{ ServerObject = $ServerObject Name = $Name Refresh = $Refresh ErrorAction = 'Stop' } # If this command does not find the audit it will throw an exception. $AuditObject = Get-SqlDscAudit @getSqlDscAuditParameters } $verboseDescriptionMessage = $script:localizedData.Audit_Disable_ShouldProcessVerboseDescription -f $AuditObject.Name, $AuditObject.Parent.InstanceName $verboseWarningMessage = $script:localizedData.Audit_Disable_ShouldProcessVerboseWarning -f $AuditObject.Name $captionMessage = $script:localizedData.Audit_Disable_ShouldProcessCaption if ($PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { $AuditObject.Disable() } } } #EndRegion '.\Public\Disable-SqlDscAudit.ps1' 101 #Region '.\Public\Enable-SqlDscAudit.ps1' 0 <# .SYNOPSIS Enables a server audit. .DESCRIPTION This command enables a server audit in a SQL Server Database Engine instance. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER AuditObject Specifies an audit object to enable. .PARAMETER Name Specifies the name of the server audit to be enabled. .PARAMETER Force Specifies that the audit should be enabled with out any confirmation. .PARAMETER Refresh Specifies that the **ServerObject**'s audits should be refreshed before trying to enable the audit object. This is helpful when audits could have been modified outside of the **ServerObject**, for example through T-SQL. But on instances with a large amount of audits it might be better to make sure the **ServerObject** is recent enough, or pass in **AuditObject**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $auditObject = $sqlServerObject | Get-SqlDscAudit -Name 'MyFileAudit' $auditObject | Enable-SqlDscAudit Enables the audit named **MyFileAudit**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | Enable-SqlDscAudit -Name 'MyFileAudit' Enables the audit named **MyFileAudit**. .OUTPUTS None. #> function Enable-SqlDscAudit { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [OutputType()] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] param ( [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(ParameterSetName = 'AuditObject', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Audit] $AuditObject, [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true)] [System.String] $Name, [Parameter()] [System.Management.Automation.SwitchParameter] $Force, [Parameter(ParameterSetName = 'ServerObject')] [System.Management.Automation.SwitchParameter] $Refresh ) process { if ($Force.IsPresent) { $ConfirmPreference = 'None' } if ($PSCmdlet.ParameterSetName -eq 'ServerObject') { $getSqlDscAuditParameters = @{ ServerObject = $ServerObject Name = $Name Refresh = $Refresh ErrorAction = 'Stop' } # If this command does not find the audit it will throw an exception. $AuditObject = Get-SqlDscAudit @getSqlDscAuditParameters } $verboseDescriptionMessage = $script:localizedData.Audit_Enable_ShouldProcessVerboseDescription -f $AuditObject.Name, $AuditObject.Parent.InstanceName $verboseWarningMessage = $script:localizedData.Audit_Enable_ShouldProcessVerboseWarning -f $AuditObject.Name $captionMessage = $script:localizedData.Audit_Enable_ShouldProcessCaption if ($PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { $AuditObject.Enable() } } } #EndRegion '.\Public\Enable-SqlDscAudit.ps1' 101 #Region '.\Public\Get-SqlDscAudit.ps1' 0 <# .SYNOPSIS Get server audit. .DESCRIPTION This command gets a server audit from a SQL Server Database Engine instance. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER Name Specifies the name of the server audit to get. .PARAMETER Refresh Specifies that the **ServerObject**'s audits should be refreshed before trying get the audit object. This is helpful when audits could have been modified outside of the **ServerObject**, for example through T-SQL. But on instances with a large amount of audits it might be better to make sure the **ServerObject** is recent enough, or pass in **AuditObject**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | Get-SqlDscAudit -Name 'MyFileAudit' Get the audit named **MyFileAudit**. .OUTPUTS `[Microsoft.SqlServer.Management.Smo.Audit]` #> function Get-SqlDscAudit { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [OutputType([Microsoft.SqlServer.Management.Smo.Audit])] [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $Name, [Parameter()] [System.Management.Automation.SwitchParameter] $Refresh ) process { if ($Refresh.IsPresent) { # Make sure the audits are up-to-date to get any newly created audits. $ServerObject.Audits.Refresh() } $auditObject = $ServerObject.Audits[$Name] if (-not $AuditObject) { $missingAuditMessage = $script:localizedData.Audit_Missing -f $Name $writeErrorParameters = @{ Message = $missingAuditMessage Category = 'InvalidOperation' ErrorId = 'GSDA0001' # cspell: disable-line TargetObject = $Name } Write-Error @writeErrorParameters } return $auditObject } } #EndRegion '.\Public\Get-SqlDscAudit.ps1' 77 #Region '.\Public\Get-SqlDscDatabasePermission.ps1' 0 <# .SYNOPSIS Returns the current permissions for the database principal. .DESCRIPTION Returns the current permissions for the database principal. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER DatabaseName Specifies the database name. .PARAMETER Name Specifies the name of the database principal for which the permissions are returned. .OUTPUTS [Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo[]] .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Get-SqlDscDatabasePermission -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' Get the permissions for the principal 'MyPrincipal'. .NOTES This command excludes fixed roles like _db_datareader_ by default, and will always return `$null` if a fixed role is specified as **Name**. If specifying `-ErrorAction 'SilentlyContinue'` then the command will silently ignore if the database (parameter **DatabaseName**) is not present or the database principal is not present. In such case the command will return `$null`. If specifying `-ErrorAction 'Stop'` the command will throw an error if the database or database principal is missing. #> function Get-SqlDscDatabasePermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseOutputTypeCorrectly', '', Justification = 'Because the rule does not understands that the command returns [System.String[]] when using , (comma) in the return statement')] [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('AvoidThrowOutsideOfTry', '', Justification = 'Because the code throws based on an prior expression')] [CmdletBinding()] [OutputType([Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo[]])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $Name ) # cSpell: ignore GSDDP process { $getSqlDscDatabasePermissionResult = $null $sqlDatabaseObject = $null if ($ServerObject.Databases) { $sqlDatabaseObject = $ServerObject.Databases[$DatabaseName] } if ($sqlDatabaseObject) { $testSqlDscIsDatabasePrincipalParameters = @{ ServerObject = $ServerObject DatabaseName = $DatabaseName Name = $Name ExcludeFixedRoles = $true } $isDatabasePrincipal = Test-SqlDscIsDatabasePrincipal @testSqlDscIsDatabasePrincipalParameters if ($isDatabasePrincipal) { $getSqlDscDatabasePermissionResult = $sqlDatabaseObject.EnumDatabasePermissions($Name) } else { $missingPrincipalMessage = $script:localizedData.DatabasePermission_MissingPrincipal -f $Name, $DatabaseName Write-Error -Message $missingPrincipalMessage -Category 'InvalidOperation' -ErrorId 'GSDDP0001' -TargetObject $Name } } else { $missingDatabaseMessage = $script:localizedData.DatabasePermission_MissingDatabase -f $DatabaseName Write-Error -Message $missingDatabaseMessage -Category 'InvalidOperation' -ErrorId 'GSDDP0002' -TargetObject $DatabaseName } return , [Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo[]] $getSqlDscDatabasePermissionResult } } #EndRegion '.\Public\Get-SqlDscDatabasePermission.ps1' 103 #Region '.\Public\Get-SqlDscServerPermission.ps1' 0 <# .SYNOPSIS Returns the current permissions for the principal. .DESCRIPTION Returns the current permissions for the principal. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER Name Specifies the name of the principal for which the permissions are returned. .OUTPUTS [Microsoft.SqlServer.Management.Smo.ServerPermissionInfo[]] .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Get-SqlDscServerPermission -ServerObject $serverInstance -Name 'MyPrincipal' Get the permissions for the principal 'MyPrincipal'. .NOTES If specifying `-ErrorAction 'SilentlyContinue'` then the command will silently ignore if the principal (parameter **Name**) is not present. In such case the command will return `$null`. If specifying `-ErrorAction 'Stop'` the command will throw an error if the principal is missing. #> function Get-SqlDscServerPermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseOutputTypeCorrectly', '', Justification = 'Because the rule does not understands that the command returns [System.String[]] when using , (comma) in the return statement')] [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('AvoidThrowOutsideOfTry', '', Justification = 'Because the code throws based on an prior expression')] [CmdletBinding()] [OutputType([Microsoft.SqlServer.Management.Smo.ServerPermissionInfo[]])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $Name ) # cSpell: ignore GSDSP process { $getSqlDscServerPermissionResult = $null $testSqlDscIsLoginParameters = @{ ServerObject = $ServerObject Name = $Name } $isLogin = Test-SqlDscIsLogin @testSqlDscIsLoginParameters if ($isLogin) { $getSqlDscServerPermissionResult = $ServerObject.EnumServerPermissions($Name) } else { $missingPrincipalMessage = $script:localizedData.ServerPermission_MissingPrincipal -f $Name, $ServerObject.InstanceName Write-Error -Message $missingPrincipalMessage -Category 'InvalidOperation' -ErrorId 'GSDSP0001' -TargetObject $Name } return , [Microsoft.SqlServer.Management.Smo.ServerPermissionInfo[]] $getSqlDscServerPermissionResult } } #EndRegion '.\Public\Get-SqlDscServerPermission.ps1' 74 #Region '.\Public\Initialize-SqlDscRebuildDatabase.ps1' 0 <# .SYNOPSIS Rebuilds the system databases for an SQL Server instance. .DESCRIPTION Rebuilds the system databases for an SQL Server instance. See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER InstanceName See the notes section for more information. .PARAMETER SAPwd See the notes section for more information. .PARAMETER SqlCollation See the notes section for more information. .PARAMETER SqlSysAdminAccounts See the notes section for more information. .PARAMETER SqlTempDbDir See the notes section for more information. .PARAMETER SqlTempDbLogDir See the notes section for more information. .PARAMETER SqlTempDbFileCount See the notes section for more information. .PARAMETER SqlTempDbFileSize See the notes section for more information. .PARAMETER SqlTempDbFileGrowth See the notes section for more information. .PARAMETER SqlTempDbLogFileSize See the notes section for more information. .PARAMETER SqlTempDbLogFileGrowth See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Initialize-SqlDscRebuildDatabase -InstanceName 'MyInstance' -SqlSysAdminAccounts @('MyAdminAccount') -MediaPath 'E:\' Rebuilds the database of the instance 'MyInstance'. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. For RebuildDatabase the parameter SAPwd must be set if the instance was installed with SecurityMode = 'SQL'. #> function Initialize-SqlDscRebuildDatabase { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter()] [System.Security.SecureString] $SAPwd, [Parameter()] [System.String] $SqlCollation, [Parameter(Mandatory = $true)] [System.String[]] $SqlSysAdminAccounts, [Parameter()] [System.String] $SqlTempDbDir, [Parameter()] [System.String] $SqlTempDbLogDir, [Parameter()] [System.UInt16] $SqlTempDbFileCount, [Parameter()] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbFileSize, [Parameter()] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbFileGrowth, [Parameter()] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbLogFileSize, [Parameter()] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbLogFileGrowth, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction -RebuildDatabase @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Initialize-SqlDscRebuildDatabase.ps1' 146 #Region '.\Public\Install-SqlDscServer.ps1' 0 <# .SYNOPSIS Executes an setup action using Microsoft SQL Server setup executable. .DESCRIPTION Executes an setup action using Microsoft SQL Server setup executable. See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER Install Specifies the setup action Install. .PARAMETER Uninstall Specifies the setup action Uninstall. .PARAMETER PrepareImage Specifies the setup action PrepareImage. .PARAMETER Upgrade Specifies the setup action Upgrade. .PARAMETER EditionUpgrade Specifies the setup action EditionUpgrade. .PARAMETER InstallFailoverCluster Specifies the setup action InstallFailoverCluster. .PARAMETER PrepareFailoverCluster Specifies the setup action PrepareFailoverCluster. .PARAMETER ConfigurationFile Specifies an configuration file to use during SQL Server setup. This parameter cannot be used together with any of the setup actions, but instead it is expected that the configuration file specifies what setup action to run. .PARAMETER AcceptLicensingTerms Required parameter to be able to run unattended install. By specifying this parameter you acknowledge the acceptance all license terms and notices for the specified features, the terms and notices that the Microsoft SQL Server setup executable normally ask for. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER SuppressPrivacyStatementNotice See the notes section for more information. .PARAMETER IAcknowledgeEntCalLimits See the notes section for more information. .PARAMETER InstanceName See the notes section for more information. .PARAMETER Enu See the notes section for more information. .PARAMETER UpdateEnabled See the notes section for more information. .PARAMETER UpdateSource See the notes section for more information. .PARAMETER Features See the notes section for more information. .PARAMETER Role See the notes section for more information. .PARAMETER InstallSharedDir See the notes section for more information. .PARAMETER InstallSharedWowDir See the notes section for more information. .PARAMETER InstanceDir See the notes section for more information. .PARAMETER InstanceId See the notes section for more information. .PARAMETER PBEngSvcAccount See the notes section for more information. .PARAMETER PBEngSvcPassword See the notes section for more information. .PARAMETER PBEngSvcStartupType See the notes section for more information. .PARAMETER PBDMSSvcAccount See the notes section for more information. .PARAMETER PBDMSSvcPassword See the notes section for more information. .PARAMETER PBDMSSvcStartupType See the notes section for more information. .PARAMETER PBStartPortRange See the notes section for more information. .PARAMETER PBEndPortRange See the notes section for more information. .PARAMETER PBScaleOut See the notes section for more information. .PARAMETER ProductKey See the notes section for more information. .PARAMETER AgtSvcAccount See the notes section for more information. .PARAMETER AgtSvcPassword See the notes section for more information. .PARAMETER AgtSvcStartupType See the notes section for more information. .PARAMETER ASBackupDir See the notes section for more information. .PARAMETER ASCollation See the notes section for more information. .PARAMETER ASConfigDir See the notes section for more information. .PARAMETER ASDataDir See the notes section for more information. .PARAMETER ASLogDir See the notes section for more information. .PARAMETER ASTempDir See the notes section for more information. .PARAMETER ASServerMode See the notes section for more information. .PARAMETER ASSvcAccount See the notes section for more information. .PARAMETER ASSvcPassword See the notes section for more information. .PARAMETER ASSvcStartupType See the notes section for more information. .PARAMETER ASSysAdminAccounts See the notes section for more information. .PARAMETER ASProviderMSOLAP See the notes section for more information. .PARAMETER FarmAccount See the notes section for more information. .PARAMETER FarmPassword See the notes section for more information. .PARAMETER Passphrase See the notes section for more information. .PARAMETER FarmAdminiPort See the notes section for more information. .PARAMETER BrowserSvcStartupType See the notes section for more information. .PARAMETER FTUpgradeOption See the notes section for more information. .PARAMETER EnableRanU See the notes section for more information. .PARAMETER InstallSqlDataDir See the notes section for more information. .PARAMETER SqlBackupDir See the notes section for more information. .PARAMETER SecurityMode See the notes section for more information. .PARAMETER SAPwd See the notes section for more information. .PARAMETER SqlCollation See the notes section for more information. .PARAMETER AddCurrentUserAsSqlAdmin See the notes section for more information. .PARAMETER SqlSvcAccount See the notes section for more information. .PARAMETER SqlSvcPassword See the notes section for more information. .PARAMETER SqlSvcStartupType See the notes section for more information. .PARAMETER SqlSysAdminAccounts See the notes section for more information. .PARAMETER SqlTempDbDir See the notes section for more information. .PARAMETER SqlTempDbLogDir See the notes section for more information. .PARAMETER SqlTempDbFileCount See the notes section for more information. .PARAMETER SqlTempDbFileSize See the notes section for more information. .PARAMETER SqlTempDbFileGrowth See the notes section for more information. .PARAMETER SqlTempDbLogFileSize See the notes section for more information. .PARAMETER SqlTempDbLogFileGrowth See the notes section for more information. .PARAMETER SqlUserDbDir See the notes section for more information. .PARAMETER SqlSvcInstantFileInit See the notes section for more information. .PARAMETER SqlUserDbLogDir See the notes section for more information. .PARAMETER SqlMaxDop See the notes section for more information. .PARAMETER UseSqlRecommendedMemoryLimits See the notes section for more information. .PARAMETER SqlMinMemory See the notes section for more information. .PARAMETER SqlMaxMemory See the notes section for more information. .PARAMETER FileStreamLevel See the notes section for more information. .PARAMETER FileStreamShareName See the notes section for more information. .PARAMETER ISSvcAccount See the notes section for more information. .PARAMETER ISSvcPassword See the notes section for more information. .PARAMETER ISSvcStartupType See the notes section for more information. .PARAMETER AllowUpgradeForSSRSSharePointMode See the notes section for more information. .PARAMETER NpEnabled See the notes section for more information. .PARAMETER TcpEnabled See the notes section for more information. .PARAMETER RsInstallMode See the notes section for more information. .PARAMETER RSSvcAccount See the notes section for more information. .PARAMETER RSSvcPassword See the notes section for more information. .PARAMETER RSSvcStartupType See the notes section for more information. .PARAMETER MPYCacheDirectory See the notes section for more information. .PARAMETER MRCacheDirectory See the notes section for more information. .PARAMETER SqlInstJava See the notes section for more information. .PARAMETER SqlJavaDir See the notes section for more information. .PARAMETER FailoverClusterGroup See the notes section for more information. .PARAMETER FailoverClusterDisks See the notes section for more information. .PARAMETER FailoverClusterNetworkName See the notes section for more information. .PARAMETER FailoverClusterIPAddresses See the notes section for more information. .PARAMETER FailoverClusterRollOwnership See the notes section for more information. .PARAMETER AzureSubscriptionId See the notes section for more information. .PARAMETER AzureResourceGroup See the notes section for more information. .PARAMETER AzureRegion See the notes section for more information. .PARAMETER AzureTenantId See the notes section for more information. .PARAMETER AzureServicePrincipal See the notes section for more information. .PARAMETER AzureServicePrincipalSecret See the notes section for more information. .PARAMETER AzureArcProxy See the notes section for more information. .PARAMETER SkipRules See the notes section for more information. .PARAMETER ProductCoveredBySA See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Install-SqlDscServer -Install -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -SqlSysAdminAccounts @('MyAdminAccount') -MediaPath 'E:\' Installs the database engine for the named instance MyInstance. .EXAMPLE Install-SqlDscServer -Install -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE','ARC' -SqlSysAdminAccounts @('MyAdminAccount') -MediaPath 'E:\' -AzureSubscriptionId 'MySubscriptionId' -AzureResourceGroup 'MyRG' -AzureRegion 'West-US' -AzureTenantId 'MyTenantId' -AzureServicePrincipal 'MyPrincipalName' -AzureServicePrincipalSecret ('MySecret' | ConvertTo-SecureString -AsPlainText -Force) Installs the database engine for the named instance MyInstance and onboard the server to Azure Arc. .EXAMPLE Install-SqlDscServer -Install -AcceptLicensingTerms -MediaPath 'E:\' -AzureSubscriptionId 'MySubscriptionId' -AzureResourceGroup 'MyRG' -AzureRegion 'West-US' -AzureTenantId 'MyTenantId' -AzureServicePrincipal 'MyPrincipalName' -AzureServicePrincipalSecret ('MySecret' | ConvertTo-SecureString -AsPlainText -Force) Installs the Azure Arc Agent on the server. .EXAMPLE Install-SqlDscServer -ConfigurationFile 'MySqlConfig.ini' -MediaPath 'E:\' Installs SQL Server using the configuration file 'MySqlConfig.ini'. .EXAMPLE Install-SqlDscServer -PrepareImage -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -InstanceId 'MyInstance' -MediaPath 'E:\' Prepares the server for using the database engine for an instance named 'MyInstance'. .EXAMPLE Install-SqlDscServer -Upgrade -AcceptLicensingTerms -InstanceName 'MyInstance' -MediaPath 'E:\' Upgrades the instance 'MyInstance' with the SQL Server version that is provided by the media path. .EXAMPLE Install-SqlDscServer -EditionUpgrade -AcceptLicensingTerms -ProductKey 'NewEditionProductKey' -InstanceName 'MyInstance' -MediaPath 'E:\' Upgrades the instance 'MyInstance' with the SQL Server edition that is provided by the media path. .EXAMPLE Install-SqlDscServer -InstallFailoverCluster -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -InstallSqlDataDir 'D:\MSSQL\Data' -SqlSysAdminAccounts @('MyAdminAccount') -FailoverClusterNetworkName 'TestCluster01A' -FailoverClusterIPAddresses 'IPv4;192.168.0.46;ClusterNetwork1;255.255.255.0' -MediaPath 'E:\' Installs the database engine in a failover cluster with the instance name 'MyInstance'. .EXAMPLE Install-SqlDscServer -PrepareFailoverCluster -AcceptLicensingTerms -InstanceName 'MyInstance' -Features 'SQLENGINE' -MediaPath 'E:\' Prepares to installs the database engine in a failover cluster with the instance name 'MyInstance'. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. #> function Install-SqlDscServer { # cSpell: ignore PBDMS Admini AZUREEXTENSION [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $Install, [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $PrepareImage, [Parameter(ParameterSetName = 'Upgrade', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $Upgrade, [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $EditionUpgrade, [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $InstallFailoverCluster, [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $PrepareFailoverCluster, [Parameter(ParameterSetName = 'UsingConfigurationFile', Mandatory = $true)] [System.String] $ConfigurationFile, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'Upgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [System.Management.Automation.SwitchParameter] $AcceptLicensingTerms, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $SuppressPrivacyStatementNotice, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Management.Automation.SwitchParameter] $IAcknowledgeEntCalLimits, [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'Upgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $InstanceName, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Management.Automation.SwitchParameter] $Enu, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Management.Automation.SwitchParameter] $UpdateEnabled, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $UpdateSource, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'PrepareFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole')] [ValidateSet( 'SQL', 'SQLEngine', # Part of parent feature SQL 'Replication', # Part of parent feature SQL 'FullText', # Part of parent feature SQL 'DQ', # Part of parent feature SQL 'PolyBase', # Part of parent feature SQL 'PolyBaseCore', # Part of parent feature SQL 'PolyBaseJava', # Part of parent feature SQL 'AdvancedAnalytics', # Part of parent feature SQL 'SQL_INST_MR', # Part of parent feature SQL 'SQL_INST_MPY', # Part of parent feature SQL 'SQL_INST_JAVA', # Part of parent feature SQL 'AS', 'RS', 'RS_SHP', 'RS_SHPWFE', # cspell: disable-line 'DQC', 'IS', 'IS_Master', # Part of parent feature IS 'IS_Worker', # Part of parent feature IS 'MDS', 'SQL_SHARED_MPY', 'SQL_SHARED_MR', 'Tools', 'BC', # Part of parent feature Tools 'Conn', # Part of parent feature Tools 'DREPLAY_CTLR', # Part of parent feature Tools (cspell: disable-line) 'DREPLAY_CLT', # Part of parent feature Tools (cspell: disable-line) 'SNAC_SDK', # Part of parent feature Tools (cspell: disable-line) 'SDK', # Part of parent feature Tools 'LocalDB', # Part of parent feature Tools 'AZUREEXTENSION' )] [System.String[]] $Features, [Parameter(ParameterSetName = 'InstallRole', Mandatory = $true)] [ValidateSet( 'ALLFeatures_WithDefaults', 'SPI_AS_NewFarm', 'SPI_AS_ExistingFarm' )] [System.String] $Role, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstallSharedDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstallSharedWowDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstanceDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage', Mandatory = $true)] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $InstanceId, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $PBEngSvcAccount, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Security.SecureString] $PBEngSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $PBEngSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $PBDMSSvcAccount, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Security.SecureString] $PBDMSSvcPassword, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $PBDMSSvcStartupType, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.UInt16] $PBStartPortRange, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.UInt16] $PBEndPortRange, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'PrepareImage')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Management.Automation.SwitchParameter] $PBScaleOut, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'EditionUpgrade', Mandatory = $true)] [System.String] $ProductKey, # This is argument PID but $PID is reserved variable. [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $AgtSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Security.SecureString] $AgtSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $AgtSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $ASBackupDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $ASCollation, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $ASConfigDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $ASDataDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $ASLogDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $ASTempDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateSet('Multidimensional', 'PowerPivot', 'Tabular')] [System.String] $ASServerMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $ASSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Security.SecureString] $ASSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $ASSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String[]] $ASSysAdminAccounts, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.Management.Automation.SwitchParameter] $ASProviderMSOLAP, [Parameter(ParameterSetName = 'InstallRole')] [System.String] $FarmAccount, [Parameter(ParameterSetName = 'InstallRole')] [System.Security.SecureString] $FarmPassword, [Parameter(ParameterSetName = 'InstallRole')] [System.Security.SecureString] $Passphrase, [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 65536)] [System.UInt16] $FarmAdminiPort, # cspell: disable-line [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $BrowserSvcStartupType, [Parameter(ParameterSetName = 'Upgrade')] [ValidateSet('Rebuild', 'Reset', 'Import')] [System.String] $FTUpgradeOption, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $EnableRanU, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [System.String] $InstallSqlDataDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $SqlBackupDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateSet('SQL')] [System.String] $SecurityMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.Security.SecureString] $SAPwd, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $SqlCollation, [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $AddCurrentUserAsSqlAdmin, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $SqlSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Security.SecureString] $SqlSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $SqlSvcStartupType, [Parameter(ParameterSetName = 'Install', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [Parameter(ParameterSetName = 'InstallRole')] [System.String[]] $SqlSysAdminAccounts, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $SqlTempDbDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $SqlTempDbLogDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.UInt16] $SqlTempDbFileCount, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbFileSize, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbFileGrowth, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateRange(4, 262144)] [System.UInt16] $SqlTempDbLogFileSize, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [ValidateRange(0, 1024)] [System.UInt16] $SqlTempDbLogFileGrowth, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $SqlUserDbDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $SqlSvcInstantFileInit, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $SqlUserDbLogDir, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 32767)] [System.UInt16] $SqlMaxDop, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $UseSqlRecommendedMemoryLimits, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 2147483647)] [System.UInt32] $SqlMinMemory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [ValidateRange(0, 2147483647)] [System.UInt32] $SqlMaxMemory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateRange(0, 3)] [System.UInt16] $FileStreamLevel, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $FileStreamShareName, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $ISSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Security.SecureString] $ISSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $ISSvcStartupType, [Parameter(ParameterSetName = 'Upgrade')] [System.Management.Automation.SwitchParameter] $AllowUpgradeForSSRSSharePointMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $NpEnabled, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $TcpEnabled, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateSet('SharePointFilesOnlyMode', 'DefaultNativeMode', 'FilesOnlyMode')] [System.String] $RsInstallMode, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.String] $RSSvcAccount, [Parameter(ParameterSetName = 'UsingConfigurationFile')] [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [System.Security.SecureString] $RSSvcPassword, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $RSSvcStartupType, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $MPYCacheDirectory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $MRCacheDirectory, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.Management.Automation.SwitchParameter] $SqlInstJava, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [System.String] $SqlJavaDir, [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String] $FailoverClusterGroup, [Parameter(ParameterSetName = 'InstallFailoverCluster')] [System.String[]] $FailoverClusterDisks, [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [System.String] $FailoverClusterNetworkName, [Parameter(ParameterSetName = 'InstallFailoverCluster', Mandatory = $true)] [System.String[]] $FailoverClusterIPAddresses, [Parameter(ParameterSetName = 'Upgrade')] [ValidateRange(0, 2)] [System.UInt16] $FailoverClusterRollOwnership, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureSubscriptionId, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureResourceGroup, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureRegion, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureTenantId, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.String] $AzureServicePrincipal, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent', Mandatory = $true)] [System.Security.SecureString] $AzureServicePrincipalSecret, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallAzureArcAgent')] [System.String] $AzureArcProxy, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'EditionUpgrade')] [System.String[]] $SkipRules, [Parameter(ParameterSetName = 'Install')] [Parameter(ParameterSetName = 'InstallRole')] [Parameter(ParameterSetName = 'Upgrade')] [Parameter(ParameterSetName = 'InstallFailoverCluster')] [Parameter(ParameterSetName = 'PrepareFailoverCluster')] [Parameter(ParameterSetName = 'EditionUpgrade')] [System.Management.Automation.SwitchParameter] $ProductCoveredBySA, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Install-SqlDscServer.ps1' 1145 #Region '.\Public\Invoke-SqlDscQuery.ps1' 0 <# .SYNOPSIS Executes a query on the specified database. .DESCRIPTION Executes a query on the specified database. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER DatabaseName Specify the name of the database to execute the query on. .PARAMETER Query The query string to execute. .PARAMETER PassThru Specifies if the command should return any result the query might return. .PARAMETER StatementTimeout Set the query StatementTimeout in seconds. Default 600 seconds (10 minutes). .PARAMETER RedactText One or more strings to redact from the query when verbose messages are written to the console. Strings here will be escaped so they will not be interpreted as regular expressions (RegEx). .OUTPUTS `[System.Data.DataSet]` when passing parameter **PassThru**, otherwise outputs none. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Invoke-SqlDscQuery -ServerObject $serverInstance -Database master ` -Query 'SELECT name FROM sys.databases' -PassThru Runs the query and returns all the database names in the instance. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Invoke-SqlDscQuery -ServerObject $serverInstance -Database master ` -Query 'RESTORE DATABASE [NorthWinds] WITH RECOVERY' Runs the query to restores the database NorthWinds. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Invoke-SqlDscQuery -ServerObject $serverInstance -Database master ` -Query "select * from MyTable where password = 'PlaceholderPa\ssw0rd1' and password = 'placeholder secret passphrase'" ` -PassThru -RedactText @('PlaceholderPa\sSw0rd1','Placeholder Secret PassPhrase') ` -Verbose Shows how to redact sensitive information in the query when the query string is output as verbose information when the parameter Verbose is used. .NOTES This is a wrapper for private function Invoke-Query, until it move into this public function. #> function Invoke-SqlDscQuery { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [OutputType([System.Data.DataSet])] [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $Query, [Parameter()] [Switch] $PassThru, [Parameter()] [ValidateNotNull()] [System.Int32] $StatementTimeout = 600, [Parameter()] [System.String[]] $RedactText ) process { $invokeQueryParameters = @{ SqlServerObject = $ServerObject Database = $DatabaseName Query = $Query } if ($PSBoundParameters.ContainsKey('PassThru')) { $invokeQueryParameters.WithResults = $PassThru } if ($PSBoundParameters.ContainsKey('StatementTimeout')) { $invokeQueryParameters.StatementTimeout = $StatementTimeout } if ($PSBoundParameters.ContainsKey('RedactText')) { $invokeQueryParameters.RedactText = $RedactText } return (Invoke-Query @invokeQueryParameters) } } #EndRegion '.\Public\Invoke-SqlDscQuery.ps1' 119 #Region '.\Public\New-SqlDscAudit.ps1' 0 <# .SYNOPSIS Creates a server audit. .DESCRIPTION This command creates a server audit on a SQL Server Database Engine instance. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER Name Specifies the name of the server audit to be added. .PARAMETER AuditFilter Specifies the filter that should be used on the audit. See [predicate expression](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql) how to write the syntax for the filter. .PARAMETER OnFailure Specifies what should happen when writing events to the store fails. This can be 'Continue', 'FailOperation', or 'Shutdown'. .PARAMETER QueueDelay Specifies the maximum delay before a event is written to the store. When set to low this could impact server performance. When set to high events could be missing when a server crashes. .PARAMETER AuditGuid Specifies the GUID found in the mirrored database. To support scenarios such as database mirroring an audit needs a specific GUID. .PARAMETER Force Specifies that the audit should be created with out any confirmation. .PARAMETER Refresh Specifies that the **ServerObject**'s audits should be refreshed before creating the audit object. This is helpful when audits could have been modified outside of the **ServerObject**, for example through T-SQL. But on instances with a large amount of audits it might be better to make sure the ServerObject is recent enough. .PARAMETER LogType Specifies the log location where the audit should write to. This can be SecurityLog or ApplicationLog. .PARAMETER Path Specifies the location where te log files wil be placed. .PARAMETER ReserveDiskSpace Specifies if the needed file space should be reserved. To use this parameter the parameter **MaximumFiles** must also be used. .PARAMETER MaximumFiles Specifies the number of files on disk. .PARAMETER MaximumFileSize Specifies the maximum file size in units by parameter MaximumFileSizeUnit. .PARAMETER MaximumFileSizeUnit Specifies the unit that is used for the file size. This can be set to `Megabyte`, `Gigabyte`, or `Terabyte`. .PARAMETER MaximumRolloverFiles Specifies the amount of files on disk before SQL Server starts reusing the files. If not specified then it is set to unlimited. .PARAMETER PassThru If specified the created audit object will be returned. .OUTPUTS `[Microsoft.SqlServer.Management.Smo.Audit]` is passing parameter **PassThru**, otherwise none. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | New-SqlDscAudit -Name 'MyFileAudit' -Path 'E:\auditFolder' Create a new file audit named **MyFileAudit**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | New-SqlDscAudit -Name 'MyAppLogAudit' -LogType 'ApplicationLog' Create a new application log audit named **MyAppLogAudit**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | New-SqlDscAudit -Name 'MyFileAudit' -Path 'E:\auditFolder' -PassThru Create a new file audit named **MyFileAudit** and returns the Audit object. .NOTES This command has the confirm impact level set to medium since an audit is created but by default is is not enabled. See the SQL Server documentation for more information for the possible parameter values to pass to this command: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql #> function New-SqlDscAudit { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [OutputType([Microsoft.SqlServer.Management.Smo.Audit])] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')] param ( [Parameter(ParameterSetName = 'Log', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'File', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'FileWithSize', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'FileWithMaxFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'FileWithMaxRolloverFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxRolloverFiles', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $Name, [Parameter()] [System.String] $AuditFilter, [Parameter()] [ValidateSet('Continue', 'FailOperation', 'Shutdown')] [System.String] $OnFailure, [Parameter()] [ValidateRange(1000, 2147483647)] [System.UInt32] $QueueDelay, [Parameter()] [ValidatePattern('^[0-9a-fA-F]{8}-(?:[0-9a-fA-F]{4}-){3}[0-9a-fA-F]{12}$')] [System.String] $AuditGuid, [Parameter()] [System.Management.Automation.SwitchParameter] $Force, [Parameter()] [System.Management.Automation.SwitchParameter] $Refresh, [Parameter()] [System.Management.Automation.SwitchParameter] $PassThru, [Parameter(ParameterSetName = 'Log', Mandatory = $true)] [ValidateSet('SecurityLog', 'ApplicationLog')] [System.String] $LogType, [Parameter(ParameterSetName = 'File', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxRolloverFiles', Mandatory = $true)] [ValidateScript({ if (-not (Test-Path -Path $_)) { throw ($script:localizedData.Audit_PathParameterValueInvalid -f $_) } return $true })] [System.String] $Path, [Parameter(ParameterSetName = 'FileWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxRolloverFiles', Mandatory = $true)] [ValidateRange(2, 2147483647)] [System.UInt32] $MaximumFileSize, [Parameter(ParameterSetName = 'FileWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithSizeAndMaxRolloverFiles', Mandatory = $true)] [ValidateSet('Megabyte', 'Gigabyte', 'Terabyte')] [System.String] $MaximumFileSizeUnit, [Parameter(ParameterSetName = 'FileWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithMaxFiles', Mandatory = $true)] [System.UInt32] $MaximumFiles, [Parameter(ParameterSetName = 'FileWithMaxFiles')] [Parameter(ParameterSetName = 'FileWithSizeAndMaxFiles')] [System.Management.Automation.SwitchParameter] $ReserveDiskSpace, [Parameter(ParameterSetName = 'FileWithSizeAndMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'FileWithMaxRolloverFiles', Mandatory = $true)] [ValidateRange(0, 2147483647)] [System.UInt32] $MaximumRolloverFiles ) process { if ($Force.IsPresent) { $ConfirmPreference = 'None' } $getSqlDscAuditParameters = @{ ServerObject = $ServerObject Name = $Name Refresh = $Refresh ErrorAction = 'SilentlyContinue' } $auditObject = Get-SqlDscAudit @getSqlDscAuditParameters if ($auditObject) { $auditAlreadyPresentMessage = $script:localizedData.Audit_AlreadyPresent -f $Name $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( $auditAlreadyPresentMessage, 'NSDA0001', # cspell: disable-line [System.Management.Automation.ErrorCategory]::InvalidOperation, $DatabaseName ) ) } $auditObject = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Audit' -ArgumentList @($ServerObject, $Name) $queryType = switch ($PSCmdlet.ParameterSetName) { 'Log' { $LogType } default { 'File' } } $auditObject.DestinationType = $queryType if ($PSCmdlet.ParameterSetName -match 'File') { $auditObject.FilePath = $Path if ($PSCmdlet.ParameterSetName -match 'FileWithSize') { $convertedMaximumFileSizeUnit = ( @{ Megabyte = 'MB' Gigabyte = 'GB' Terabyte = 'TB' } ).$MaximumFileSizeUnit $auditObject.MaximumFileSize = $MaximumFileSize $auditObject.MaximumFileSizeUnit = $convertedMaximumFileSizeUnit } if ($PSCmdlet.ParameterSetName -in @('FileWithMaxFiles', 'FileWithSizeAndMaxFiles')) { $auditObject.MaximumFiles = $MaximumFiles if ($PSBoundParameters.ContainsKey('ReserveDiskSpace')) { $auditObject.ReserveDiskSpace = $ReserveDiskSpace.IsPresent } } if ($PSCmdlet.ParameterSetName -in @('FileWithMaxRolloverFiles', 'FileWithSizeAndMaxRolloverFiles')) { $auditObject.MaximumRolloverFiles = $MaximumRolloverFiles } } if ($PSBoundParameters.ContainsKey('OnFailure')) { $auditObject.OnFailure = $OnFailure } if ($PSBoundParameters.ContainsKey('QueueDelay')) { $auditObject.QueueDelay = $QueueDelay } if ($PSBoundParameters.ContainsKey('AuditGuid')) { $auditObject.Guid = $AuditGuid } if ($PSBoundParameters.ContainsKey('AuditFilter')) { $auditObject.Filter = $AuditFilter } $verboseDescriptionMessage = $script:localizedData.Audit_Add_ShouldProcessVerboseDescription -f $Name, $ServerObject.InstanceName $verboseWarningMessage = $script:localizedData.Audit_Add_ShouldProcessVerboseWarning -f $Name $captionMessage = $script:localizedData.Audit_Add_ShouldProcessCaption if ($PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { $auditObject.Create() if ($PassThru.IsPresent) { return $auditObject } } } } #EndRegion '.\Public\New-SqlDscAudit.ps1' 319 #Region '.\Public\Remove-SqlDscAudit.ps1' 0 <# .SYNOPSIS Removes a server audit. .DESCRIPTION This command removes a server audit from a SQL Server Database Engine instance. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER AuditObject Specifies an audit object to remove. .PARAMETER Name Specifies the name of the server audit to be removed. .PARAMETER Force Specifies that the audit should be removed with out any confirmation. .PARAMETER Refresh Specifies that the **ServerObject**'s audits should be refreshed before trying removing the audit object. This is helpful when audits could have been modified outside of the **ServerObject**, for example through T-SQL. But on instances with a large amount of audits it might be better to make sure the **ServerObject** is recent enough, or pass in **AuditObject**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $auditObject = $sqlServerObject | Get-SqlDscAudit -Name 'MyFileAudit' $auditObject | Remove-SqlDscAudit Removes the audit named **MyFileAudit**. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | Remove-SqlDscAudit -Name 'MyFileAudit' Removes the audit named **MyFileAudit**. .OUTPUTS None. #> function Remove-SqlDscAudit { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [OutputType()] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] param ( [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(ParameterSetName = 'AuditObject', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Audit] $AuditObject, [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true)] [System.String] $Name, [Parameter()] [System.Management.Automation.SwitchParameter] $Force, [Parameter(ParameterSetName = 'ServerObject')] [System.Management.Automation.SwitchParameter] $Refresh ) process { if ($Force.IsPresent) { $ConfirmPreference = 'None' } if ($PSCmdlet.ParameterSetName -eq 'ServerObject') { $getSqlDscAuditParameters = @{ ServerObject = $ServerObject Name = $Name Refresh = $Refresh ErrorAction = 'Stop' } # If this command does not find the audit it will throw an exception. $AuditObject = Get-SqlDscAudit @getSqlDscAuditParameters } $verboseDescriptionMessage = $script:localizedData.Audit_Remove_ShouldProcessVerboseDescription -f $AuditObject.Name, $AuditObject.Parent.InstanceName $verboseWarningMessage = $script:localizedData.Audit_Remove_ShouldProcessVerboseWarning -f $AuditObject.Name $captionMessage = $script:localizedData.Audit_Remove_ShouldProcessCaption if ($PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { <# If the passed audit object has already been dropped, then we silently do nothing, using the method DropIfExist(), since the job is done. #> $AuditObject.DropIfExists() } } } #EndRegion '.\Public\Remove-SqlDscAudit.ps1' 105 #Region '.\Public\Remove-SqlDscNode.ps1' 0 <# .SYNOPSIS Removes a SQL Server node from an Failover Cluster instance (FCI). .DESCRIPTION Removes a SQL Server node from an Failover Cluster instance (FCI). See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER InstanceName See the notes section for more information. .PARAMETER ConfirmIPDependencyChange See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Remove-SqlDscNode -InstanceName 'MyInstance' -MediaPath 'E:\' Removes the current node's SQL Server instance 'MyInstance' from the Failover Cluster instance. .NOTES All parameters has intentionally not been added to this comment-based help since it would take a lot of effort to keep them up to date. Instead there is a link in the comment-based help that points to the SQL Server command line setup documentation which will stay relevant. #> function Remove-SqlDscNode { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter()] [System.Management.Automation.SwitchParameter] $ConfirmIPDependencyChange, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction -RemoveNode @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Remove-SqlDscNode.ps1' 78 #Region '.\Public\Repair-SqlDscServer.ps1' 0 <# .SYNOPSIS Executes an setup action using Microsoft SQL Server setup executable. .DESCRIPTION Executes an setup action using Microsoft SQL Server setup executable. See the link in the commands help for information on each parameter. The link points to SQL Server command line setup documentation. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER InstanceName See the notes section for more information. .PARAMETER Enu See the notes section for more information. .PARAMETER Features See the notes section for more information. .PARAMETER PBEngSvcAccount See the notes section for more information. .PARAMETER PBEngSvcPassword See the notes section for more information. .PARAMETER PBEngSvcStartupType See the notes section for more information. .PARAMETER PBStartPortRange See the notes section for more information. .PARAMETER PBEndPortRange See the notes section for more information. .PARAMETER PBScaleOut See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Repair-SqlDscServer -InstanceName 'MyInstance' -Features 'SQLENGINE' -MediaPath 'E:\' Repairs the database engine of the instance 'MyInstance'. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. #> function Repair-SqlDscServer { # cSpell: ignore AZUREEXTENSION [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter()] [System.Management.Automation.SwitchParameter] $Enu, [Parameter(Mandatory = $true)] [ValidateSet( 'SQL', 'SQLEngine', # Part of parent feature SQL 'Replication', # Part of parent feature SQL 'FullText', # Part of parent feature SQL 'DQ', # Part of parent feature SQL 'PolyBase', # Part of parent feature SQL 'PolyBaseCore', # Part of parent feature SQL 'PolyBaseJava', # Part of parent feature SQL 'AdvancedAnalytics', # Part of parent feature SQL 'SQL_INST_MR', # Part of parent feature SQL 'SQL_INST_MPY', # Part of parent feature SQL 'SQL_INST_JAVA', # Part of parent feature SQL 'AS', 'RS', 'RS_SHP', 'RS_SHPWFE', # cspell: disable-line 'DQC', 'IS', 'IS_Master', # Part of parent feature IS 'IS_Worker', # Part of parent feature IS 'MDS', 'SQL_SHARED_MPY', 'SQL_SHARED_MR', 'Tools', 'BC', # Part of parent feature Tools 'Conn', # Part of parent feature Tools 'DREPLAY_CTLR', # Part of parent feature Tools (cspell: disable-line) 'DREPLAY_CLT', # Part of parent feature Tools (cspell: disable-line) 'SNAC_SDK', # Part of parent feature Tools (cspell: disable-line) 'SDK', # Part of parent feature Tools 'LocalDB', # Part of parent feature Tools 'AZUREEXTENSION' )] [System.String[]] $Features, [Parameter()] [System.String] $PBEngSvcAccount, [Parameter()] [System.Security.SecureString] $PBEngSvcPassword, [Parameter()] [ValidateSet('Automatic', 'Disabled', 'Manual')] [System.String] $PBEngSvcStartupType, [Parameter()] [System.UInt16] $PBStartPortRange, [Parameter()] [System.UInt16] $PBEndPortRange, [Parameter()] [System.Management.Automation.SwitchParameter] $PBScaleOut, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction -Repair @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Repair-SqlDscServer.ps1' 161 #Region '.\Public\Set-SqlDscAudit.ps1' 0 <# .SYNOPSIS Updates a server audit. .DESCRIPTION This command updates and existing server audit on a SQL Server Database Engine instance. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER AuditObject Specifies an audit object to update. .PARAMETER Name Specifies the name of the server audit to be updated. .PARAMETER AuditFilter Specifies the filter that should be used on the audit. See [predicate expression](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql) how to write the syntax for the filter. .PARAMETER OnFailure Specifies what should happen when writing events to the store fails. This can be 'Continue', 'FailOperation', or 'Shutdown'. .PARAMETER QueueDelay Specifies the maximum delay before a event is written to the store. When set to low this could impact server performance. When set to high events could be missing when a server crashes. .PARAMETER AuditGuid Specifies the GUID found in the mirrored database. To support scenarios such as database mirroring an audit needs a specific GUID. .PARAMETER Force Specifies that the audit should be updated with out any confirmation. .PARAMETER Refresh Specifies that the audit object should be refreshed before updating. This is helpful when audits could have been modified outside of the **ServerObject**, for example through T-SQL. But on instances with a large amount of audits it might be better to make sure the ServerObject is recent enough. .PARAMETER Path Specifies the location where te log files wil be placed. .PARAMETER ReserveDiskSpace Specifies if the needed file space should be reserved. To use this parameter the parameter **MaximumFiles** must also be used. .PARAMETER MaximumFiles Specifies the number of files on disk. .PARAMETER MaximumFileSize Specifies the maximum file size in units by parameter MaximumFileSizeUnit. Minimum allowed value is 2 (MB). It also allowed to set the value to 0 which mean unlimited file size. .PARAMETER MaximumFileSizeUnit Specifies the unit that is used for the file size. this can be KB, MB or GB. .PARAMETER MaximumRolloverFiles Specifies the amount of files on disk before SQL Server starts reusing the files. If not specified then it is set to unlimited. .PARAMETER PassThru If specified the changed audit object will be returned. .OUTPUTS `[Microsoft.SqlServer.Management.Smo.Audit]` is passing parameter **PassThru**, otherwise none. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | Set-SqlDscAudit -Name 'MyFileAudit' -Path 'E:\auditFolder' -QueueDelay 1000 Updates the file audit named **MyFileAudit** by setting the path to ''E:\auditFolder' and the queue delay to 1000. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | New-SqlDscAudit -Name 'MyAppLogAudit' -QueueDelay 1000 Updates the application log audit named **MyAppLogAudit** by setting the queue delay to 1000. .EXAMPLE $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance' $sqlServerObject | Set-SqlDscAudit -Name 'MyFileAudit' -Path 'E:\auditFolder' -QueueDelay 1000 -PassThru Updates the file audit named **MyFileAudit** by setting the path to ''E:\auditFolder' and the queue delay to 1000, and returns the Audit object. .NOTES This command has the confirm impact level set to high since an audit is unknown to be enable at the point when the command is issued. See the SQL Server documentation for more information for the possible parameter values to pass to this command: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql #> function Set-SqlDscAudit { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [OutputType([Microsoft.SqlServer.Management.Smo.Audit])] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] param ( [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSize', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'ServerObjectWithMaxFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'ServerObjectWithMaxRolloverFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxRolloverFiles', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(ParameterSetName = 'AuditObject', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSize', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'AuditObjectWithMaxFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'AuditObjectWithMaxRolloverFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxFiles', Mandatory = $true, ValueFromPipeline = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxRolloverFiles', Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Audit] $AuditObject, [Parameter(ParameterSetName = 'ServerObject', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxRolloverFiles', Mandatory = $true)] [System.String] $Name, [Parameter()] [System.String] $AuditFilter, [Parameter()] [ValidateSet('Continue', 'FailOperation', 'Shutdown')] [System.String] $OnFailure, [Parameter()] [ValidateScript({ if ($_ -in 1..999 -or $_ -gt 2147483647) { throw ($script:localizedData.Audit_QueueDelayParameterValueInvalid -f $_) } return $true })] [System.UInt32] $QueueDelay, [Parameter()] [ValidatePattern('^[0-9a-fA-F]{8}-(?:[0-9a-fA-F]{4}-){3}[0-9a-fA-F]{12}$')] [System.String] $AuditGuid, [Parameter()] [System.Management.Automation.SwitchParameter] $Force, [Parameter()] [System.Management.Automation.SwitchParameter] $Refresh, [Parameter()] [System.Management.Automation.SwitchParameter] $PassThru, [Parameter(ParameterSetName = 'ServerObject')] [Parameter(ParameterSetName = 'ServerObjectWithSize')] [Parameter(ParameterSetName = 'ServerObjectWithMaxFiles')] [Parameter(ParameterSetName = 'ServerObjectWithMaxRolloverFiles')] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxFiles')] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxRolloverFiles')] [Parameter(ParameterSetName = 'AuditObject')] [Parameter(ParameterSetName = 'AuditObjectWithSize')] [Parameter(ParameterSetName = 'AuditObjectWithMaxFiles')] [Parameter(ParameterSetName = 'AuditObjectWithMaxRolloverFiles')] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxFiles')] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxRolloverFiles')] [ValidateScript({ if (-not (Test-Path -Path $_)) { throw ($script:localizedData.Audit_PathParameterValueInvalid -f $_) } return $true })] [System.String] $Path, [Parameter(ParameterSetName = 'ServerObjectWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxRolloverFiles', Mandatory = $true)] [ValidateScript({ if ($_ -eq 1 -or $_ -gt 2147483647) { throw ($script:localizedData.Audit_MaximumFileSizeParameterValueInvalid -f $_) } return $true })] [System.UInt32] $MaximumFileSize, [Parameter(ParameterSetName = 'ServerObjectWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSize', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxRolloverFiles', Mandatory = $true)] [ValidateSet('Megabyte', 'Gigabyte', 'Terabyte')] [System.String] $MaximumFileSizeUnit, [Parameter(ParameterSetName = 'ServerObjectWithMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithMaxFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxFiles', Mandatory = $true)] [System.UInt32] $MaximumFiles, [Parameter(ParameterSetName = 'ServerObjectWithMaxFiles')] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxFiles')] [Parameter(ParameterSetName = 'AuditObjectWithMaxFiles')] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxFiles')] [System.Management.Automation.SwitchParameter] $ReserveDiskSpace, [Parameter(ParameterSetName = 'ServerObjectWithMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'ServerObjectWithSizeAndMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithMaxRolloverFiles', Mandatory = $true)] [Parameter(ParameterSetName = 'AuditObjectWithSizeAndMaxRolloverFiles', Mandatory = $true)] [ValidateRange(0, 2147483647)] [System.UInt32] $MaximumRolloverFiles ) process { if ($Force.IsPresent) { $ConfirmPreference = 'None' } if ($PSCmdlet.ParameterSetName -eq 'ServerObject') { $getSqlDscAuditParameters = @{ ServerObject = $ServerObject Name = $Name Refresh = $Refresh ErrorAction = 'Stop' } $AuditObject = Get-SqlDscAudit @getSqlDscAuditParameters } if ($Refresh.IsPresent) { $AuditObject.Refresh() } $verboseDescriptionMessage = $script:localizedData.Audit_Update_ShouldProcessVerboseDescription -f $AuditObject.Name, $AuditObject.Parent.InstanceName $verboseWarningMessage = $script:localizedData.Audit_Update_ShouldProcessVerboseWarning -f $AuditObject.Name $captionMessage = $script:localizedData.Audit_Update_ShouldProcessCaption if ($PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { if ($PSBoundParameters.ContainsKey('Path')) { $AuditObject.FilePath = $Path } if ($PSCmdlet.ParameterSetName -match 'WithSize') { $queryMaximumFileSizeUnit = ( @{ Megabyte = 'MB' Gigabyte = 'GB' Terabyte = 'TB' } ).$MaximumFileSizeUnit $AuditObject.MaximumFileSize = $MaximumFileSize $AuditObject.MaximumFileSizeUnit = $queryMaximumFileSizeUnit } if ($PSCmdlet.ParameterSetName -match 'MaxFiles') { if ($AuditObject.MaximumRolloverFiles) { # Switching to MaximumFiles instead of MaximumRolloverFiles. $AuditObject.MaximumRolloverFiles = 0 # Must run method Alter() before setting MaximumFiles. $AuditObject.Alter() } $AuditObject.MaximumFiles = $MaximumFiles if ($PSBoundParameters.ContainsKey('ReserveDiskSpace')) { $AuditObject.ReserveDiskSpace = $ReserveDiskSpace.IsPresent } } if ($PSCmdlet.ParameterSetName -match 'MaxRolloverFiles') { if ($AuditObject.MaximumFiles) { # Switching to MaximumRolloverFiles instead of MaximumFiles. $AuditObject.MaximumFiles = 0 # Must run method Alter() before setting MaximumRolloverFiles. $AuditObject.Alter() } $AuditObject.MaximumRolloverFiles = $MaximumRolloverFiles } if ($PSBoundParameters.ContainsKey('OnFailure')) { $AuditObject.OnFailure = $OnFailure } if ($PSBoundParameters.ContainsKey('QueueDelay')) { $AuditObject.QueueDelay = $QueueDelay } if ($PSBoundParameters.ContainsKey('AuditGuid')) { $AuditObject.Guid = $AuditGuid } if ($PSBoundParameters.ContainsKey('AuditFilter')) { $AuditObject.Filter = $AuditFilter } $AuditObject.Alter() if ($PassThru.IsPresent) { return $AuditObject } } } } #EndRegion '.\Public\Set-SqlDscAudit.ps1' 357 #Region '.\Public\Set-SqlDscDatabasePermission.ps1' 0 <# .SYNOPSIS Set permission for a database principal. .DESCRIPTION Set permission for a database principal. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER DatabaseName Specifies the database name. .PARAMETER Name Specifies the name of the database principal for which the permissions are set. .PARAMETER State Specifies the state of the permission. .PARAMETER Permission Specifies the permissions. .PARAMETER WithGrant Specifies that the principal should also be granted the right to grant other principals the same permission. This parameter is only valid when parameter **State** is set to `Grant` or `Revoke`. When the parameter **State** is set to `Revoke` the right to grant will also be revoked, and the revocation will cascade. .PARAMETER Force Specifies that the permissions should be set with out any confirmation. .OUTPUTS None. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine $setPermission = [Microsoft.SqlServer.Management.Smo.DatabasePermissionSet] @{ Connect = $true Update = $true } Set-SqlDscDatabasePermission -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' -State 'Grant' -Permission $setPermission Sets the permissions for the principal 'MyPrincipal'. .NOTES This command excludes fixed roles like _db_datareader_ by default, and will always throw a non-terminating error if a fixed role is specified as **Name**. If specifying `-ErrorAction 'SilentlyContinue'` then the command will silently ignore if the database (parameter **DatabaseName**) is not present or the database principal is not present. If specifying `-ErrorAction 'Stop'` the command will throw an error if the database or database principal is missing. #> function Set-SqlDscDatabasePermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('AvoidThrowOutsideOfTry', '', Justification = 'Because the code throws based on an prior expression')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $Name, [Parameter(Mandatory = $true)] [ValidateSet('Grant', 'Deny', 'Revoke')] [System.String] $State, [Parameter(Mandatory = $true)] [Microsoft.SqlServer.Management.Smo.DatabasePermissionSet] $Permission, [Parameter()] [System.Management.Automation.SwitchParameter] $WithGrant, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) process { if ($State -eq 'Deny' -and $WithGrant.IsPresent) { Write-Warning -Message $script:localizedData.DatabasePermission_IgnoreWithGrantForStateDeny } if ($Force.IsPresent) { $ConfirmPreference = 'None' } $sqlDatabaseObject = $null if ($ServerObject.Databases) { $sqlDatabaseObject = $ServerObject.Databases[$DatabaseName] } if ($sqlDatabaseObject) { $testSqlDscIsDatabasePrincipalParameters = @{ ServerObject = $ServerObject DatabaseName = $DatabaseName Name = $Name ExcludeFixedRoles = $true } $isDatabasePrincipal = Test-SqlDscIsDatabasePrincipal @testSqlDscIsDatabasePrincipalParameters if ($isDatabasePrincipal) { # Get the permissions names that are set to $true in the DatabasePermissionSet. $permissionName = $Permission | Get-Member -MemberType 'Property' | Select-Object -ExpandProperty 'Name' | Where-Object -FilterScript { $Permission.$_ } $verboseDescriptionMessage = $script:localizedData.DatabasePermission_ChangePermissionShouldProcessVerboseDescription -f $Name, $DatabaseName, $ServerObject.InstanceName $verboseWarningMessage = $script:localizedData.DatabasePermission_ChangePermissionShouldProcessVerboseWarning -f $Name $captionMessage = $script:localizedData.DatabasePermission_ChangePermissionShouldProcessCaption if (-not $PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { # Return without doing anything if the user did not want to continue processing. return } switch ($State) { 'Grant' { Write-Verbose -Message ( $script:localizedData.DatabasePermission_GrantPermission -f ($permissionName -join ','), $Name ) if ($WithGrant.IsPresent) { $sqlDatabaseObject.Grant($Permission, $Name, $true) } else { $sqlDatabaseObject.Grant($Permission, $Name) } } 'Deny' { Write-Verbose -Message ( $script:localizedData.DatabasePermission_DenyPermission -f ($permissionName -join ','), $Name ) $sqlDatabaseObject.Deny($Permission, $Name) } 'Revoke' { Write-Verbose -Message ( $script:localizedData.DatabasePermission_RevokePermission -f ($permissionName -join ','), $Name ) if ($WithGrant.IsPresent) { $sqlDatabaseObject.Revoke($Permission, $Name, $false, $true) } else { $sqlDatabaseObject.Revoke($Permission, $Name) } } } } else { $missingPrincipalMessage = $script:localizedData.DatabasePermission_MissingPrincipal -f $Name, $DatabaseName $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( $missingPrincipalMessage, 'GSDDP0001', [System.Management.Automation.ErrorCategory]::InvalidOperation, $Name ) ) } } else { $missingDatabaseMessage = $script:localizedData.DatabasePermission_MissingDatabase -f $DatabaseName $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( $missingDatabaseMessage, 'GSDDP0002', [System.Management.Automation.ErrorCategory]::InvalidOperation, $DatabaseName ) ) } } } #EndRegion '.\Public\Set-SqlDscDatabasePermission.ps1' 219 #Region '.\Public\Set-SqlDscServerPermission.ps1' 0 <# .SYNOPSIS Set permission for a login. .DESCRIPTION This command sets the permissions for a existing login on a SQL Server Database Engine instance. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER Name Specifies the name of the principal for which the permissions are set. .PARAMETER State Specifies the state of the permission. .PARAMETER Permission Specifies the permissions. .PARAMETER WithGrant Specifies that the principal should also be granted the right to grant other principals the same permission. This parameter is only valid when parameter **State** is set to `Grant` or `Revoke`. When the parameter **State** is set to `Revoke` the right to grant will also be revoked, and the revocation will cascade. .PARAMETER Force Specifies that the permissions should be set with out any confirmation. .OUTPUTS None. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine $setPermission = [Microsoft.SqlServer.Management.Smo.ServerPermissionSet] @{ Connect = $true Update = $true } Set-SqlDscServerPermission -ServerObject $serverInstance -Name 'MyPrincipal' -State 'Grant' -Permission $setPermission Sets the permissions for the principal 'MyPrincipal'. .NOTES If specifying `-ErrorAction 'SilentlyContinue'` then the command will silently ignore if the principal is not present. If specifying `-ErrorAction 'Stop'` the command will throw an error if the principal is missing. #> function Set-SqlDscServerPermission { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('AvoidThrowOutsideOfTry', '', Justification = 'Because the code throws based on an prior expression')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $Name, [Parameter(Mandatory = $true)] [ValidateSet('Grant', 'Deny', 'Revoke')] [System.String] $State, [Parameter(Mandatory = $true)] [Microsoft.SqlServer.Management.Smo.ServerPermissionSet] $Permission, [Parameter()] [System.Management.Automation.SwitchParameter] $WithGrant, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) process { if ($State -eq 'Deny' -and $WithGrant.IsPresent) { Write-Warning -Message $script:localizedData.ServerPermission_IgnoreWithGrantForStateDeny } if ($Force.IsPresent) { $ConfirmPreference = 'None' } $testSqlDscIsLoginParameters = @{ ServerObject = $ServerObject Name = $Name } $isLogin = Test-SqlDscIsLogin @testSqlDscIsLoginParameters if ($isLogin) { # Get the permissions names that are set to $true in the ServerPermissionSet. $permissionName = $Permission | Get-Member -MemberType 'Property' | Select-Object -ExpandProperty 'Name' | Where-Object -FilterScript { $Permission.$_ } $verboseDescriptionMessage = $script:localizedData.ServerPermission_ChangePermissionShouldProcessVerboseDescription -f $Name, $ServerObject.InstanceName $verboseWarningMessage = $script:localizedData.ServerPermission_ChangePermissionShouldProcessVerboseWarning -f $Name $captionMessage = $script:localizedData.ServerPermission_ChangePermissionShouldProcessCaption if (-not $PSCmdlet.ShouldProcess($verboseDescriptionMessage, $verboseWarningMessage, $captionMessage)) { # Return without doing anything if the user did not want to continue processing. return } switch ($State) { 'Grant' { Write-Verbose -Message ( $script:localizedData.ServerPermission_GrantPermission -f ($permissionName -join ','), $Name ) if ($WithGrant.IsPresent) { $ServerObject.Grant($Permission, $Name, $true) } else { $ServerObject.Grant($Permission, $Name) } } 'Deny' { Write-Verbose -Message ( $script:localizedData.ServerPermission_DenyPermission -f ($permissionName -join ','), $Name ) $ServerObject.Deny($Permission, $Name) } 'Revoke' { Write-Verbose -Message ( $script:localizedData.ServerPermission_RevokePermission -f ($permissionName -join ','), $Name ) if ($WithGrant.IsPresent) { $ServerObject.Revoke($Permission, $Name, $false, $true) } else { $ServerObject.Revoke($Permission, $Name) } } } } else { $missingPrincipalMessage = $script:localizedData.ServerPermission_MissingPrincipal -f $Name, $ServerObject.InstanceName $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( $missingPrincipalMessage, 'GSDDP0001', [System.Management.Automation.ErrorCategory]::InvalidOperation, $Name ) ) } } } #EndRegion '.\Public\Set-SqlDscServerPermission.ps1' 183 #Region '.\Public\Test-SqlDscIsDatabasePrincipal.ps1' 0 <# .SYNOPSIS Returns whether the database principal exist. .DESCRIPTION Returns whether the database principal exist. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER DatabaseName Specifies the SQL database name. .PARAMETER Name Specifies the name of the database principal. .PARAMETER ExcludeUsers Specifies that database users should not be evaluated. .PARAMETER ExcludeRoles Specifies that database roles should not be evaluated for the specified name. This will also exclude fixed roles. .PARAMETER ExcludeFixedRoles Specifies that fixed roles should not be evaluated for the specified name. .PARAMETER ExcludeApplicationRoles Specifies that fixed application roles should not be evaluated for the specified name. .OUTPUTS [System.Boolean] .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Test-SqlDscIsDatabasePrincipal -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' Returns $true if the principal exist in the database, if not $false is returned. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Test-SqlDscIsDatabasePrincipal -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' -ExcludeUsers Returns $true if the principal exist in the database and is not a user, if not $false is returned. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Test-SqlDscIsDatabasePrincipal -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' -ExcludeRoles Returns $true if the principal exist in the database and is not a role, if not $false is returned. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Test-SqlDscIsDatabasePrincipal -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' -ExcludeFixedRoles Returns $true if the principal exist in the database and is not a fixed role, if not $false is returned. .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Test-SqlDscIsDatabasePrincipal -ServerObject $serverInstance -DatabaseName 'MyDatabase' -Name 'MyPrincipal' -ExcludeApplicationRoles Returns $true if the principal exist in the database and is not a application role, if not $false is returned. #> function Test-SqlDscIsDatabasePrincipal { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $Name, [Parameter()] [System.Management.Automation.SwitchParameter] $ExcludeUsers, [Parameter()] [System.Management.Automation.SwitchParameter] $ExcludeRoles, [Parameter()] [System.Management.Automation.SwitchParameter] $ExcludeFixedRoles, [Parameter()] [System.Management.Automation.SwitchParameter] $ExcludeApplicationRoles ) process { $principalExist = $false $sqlDatabaseObject = $ServerObject.Databases[$DatabaseName] if (-not $sqlDatabaseObject) { $PSCmdlet.ThrowTerminatingError( [System.Management.Automation.ErrorRecord]::new( ($script:localizedData.IsDatabasePrincipal_DatabaseMissing -f $DatabaseName), 'TSDISO0001', [System.Management.Automation.ErrorCategory]::InvalidOperation, $DatabaseName ) ) } if (-not $ExcludeUsers.IsPresent -and $sqlDatabaseObject.Users[$Name]) { $principalExist = $true } if (-not $ExcludeRoles.IsPresent) { $userDefinedRole = if ($ExcludeFixedRoles.IsPresent) { # Skip fixed roles like db_datareader. $sqlDatabaseObject.Roles | Where-Object -FilterScript { -not $_.IsFixedRole -and $_.Name -eq $Name } } else { $sqlDatabaseObject.Roles[$Name] } if ($userDefinedRole) { $principalExist = $true } } if (-not $ExcludeApplicationRoles.IsPresent -and $sqlDatabaseObject.ApplicationRoles[$Name]) { $principalExist = $true } return $principalExist } } #EndRegion '.\Public\Test-SqlDscIsDatabasePrincipal.ps1' 151 #Region '.\Public\Test-SqlDscIsLogin.ps1' 0 <# .SYNOPSIS Returns whether the database principal exist. .DESCRIPTION Returns whether the database principal exist. .PARAMETER ServerObject Specifies current server connection object. .PARAMETER Name Specifies the name of the database principal. .OUTPUTS [System.Boolean] .EXAMPLE $serverInstance = Connect-SqlDscDatabaseEngine Test-SqlDscIsLogin -ServerObject $serverInstance -Name 'MyPrincipal' Returns $true if the principal exist as a login, if not $false is returned. #> function Test-SqlDscIsLogin { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('UseSyntacticallyCorrectExamples', '', Justification = 'Because the rule does not yet support parsing the code when a parameter type is not available. The ScriptAnalyzer rule UseSyntacticallyCorrectExamples will always error in the editor due to https://github.com/indented-automation/Indented.ScriptAnalyzerRules/issues/8.')] [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [System.String] $Name ) process { $loginExist = $false if ($ServerObject.Logins[$Name]) { $loginExist = $true } return $loginExist } } #EndRegion '.\Public\Test-SqlDscIsLogin.ps1' 51 #Region '.\Public\Uninstall-SqlDscServer.ps1' 0 <# .SYNOPSIS Uninstall features from a Microsoft SQL Server instance. .DESCRIPTION Uninstall features from a Microsoft SQL Server instance. See the link in the commands help for information on each parameter for the setup action Uninstall. The link points to SQL Server command line setup documentation. .PARAMETER MediaPath Specifies the path where to find the SQL Server installation media. On this path the SQL Server setup executable must be found. .PARAMETER Timeout Specifies how long to wait for the setup process to finish. Default value is `7200` seconds (2 hours). If the setup process does not finish before this time, an exception will be thrown. .PARAMETER Force If specified the command will not ask for confirmation. Same as if Confirm:$false is used. .PARAMETER InstanceName See the notes section for more information. .PARAMETER Features See the notes section for more information. .LINK https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt .OUTPUTS None. .EXAMPLE Uninstall-SqlDscServer -InstanceName 'MyInstance' -Features 'SQLENGINE' -MediaPath 'E:\' Uninstalls the database engine from the named instance MyInstance. .NOTES The parameters are intentionally not described since it would take a lot of effort to keep them up to date. Instead there is a link that points to the SQL Server command line setup documentation which will stay relevant. #> function Uninstall-SqlDscServer { # cSpell: ignore AZUREEXTENSION [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSShouldProcess', '', Justification = 'Because ShouldProcess is used in Invoke-SetupAction')] [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] [OutputType()] param ( [Parameter(Mandatory = $true)] [System.String] $MediaPath, [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter()] [ValidateSet( 'SQL', 'SQLEngine', # Part of parent feature SQL 'Replication', # Part of parent feature SQL 'FullText', # Part of parent feature SQL 'DQ', # Part of parent feature SQL 'PolyBase', # Part of parent feature SQL 'PolyBaseCore', # Part of parent feature SQL 'PolyBaseJava', # Part of parent feature SQL 'AdvancedAnalytics', # Part of parent feature SQL 'SQL_INST_MR', # Part of parent feature SQL 'SQL_INST_MPY', # Part of parent feature SQL 'SQL_INST_JAVA', # Part of parent feature SQL 'AS', 'RS', 'RS_SHP', 'RS_SHPWFE', # cspell: disable-line 'DQC', 'IS', 'IS_Master', # Part of parent feature IS 'IS_Worker', # Part of parent feature IS 'MDS', 'SQL_SHARED_MPY', 'SQL_SHARED_MR', 'Tools', 'BC', # Part of parent feature Tools 'Conn', # Part of parent feature Tools 'DREPLAY_CTLR', # Part of parent feature Tools (cspell: disable-line) 'DREPLAY_CLT', # Part of parent feature Tools (cspell: disable-line) 'SNAC_SDK', # Part of parent feature Tools (cspell: disable-line) 'SDK', # Part of parent feature Tools 'LocalDB', # Part of parent feature Tools 'AZUREEXTENSION' )] [System.String[]] $Features, [Parameter()] [System.UInt32] $Timeout = 7200, [Parameter()] [System.Management.Automation.SwitchParameter] $Force ) Invoke-SetupAction -Uninstall @PSBoundParameters -ErrorAction 'Stop' } #EndRegion '.\Public\Uninstall-SqlDscServer.ps1' 112 |