DSCResources/DSC_SqlSecureConnection/DSC_SqlSecureConnection.psm1
|
$script:sqlServerDscHelperModulePath = Join-Path -Path $PSScriptRoot -ChildPath '..\..\Modules\SqlServerDsc.Common' $script:resourceHelperModulePath = Join-Path -Path $PSScriptRoot -ChildPath '..\..\Modules\DscResource.Common' Import-Module -Name $script:sqlServerDscHelperModulePath Import-Module -Name $script:resourceHelperModulePath $script:localizedData = Get-LocalizedData -DefaultUICulture 'en-US' <# .SYNOPSIS Gets the SQL Server Encryption status. .PARAMETER InstanceName Name of the SQL Server instance to be configured. .PARAMETER Thumbprint Thumbprint of the certificate being used for encryption. If parameter Ensure is set to 'Absent', then the parameter Thumbprint can be set to an empty string. .PARAMETER ForceEncryption If all connections to the SQL instance should be encrypted. If this parameter is not assigned a value, the default is that all connections must be encrypted. .PARAMETER Ensure If Encryption should be Enabled (Present) or Disabled (Absent). .PARAMETER ServiceAccount Name of the account running the SQL Server service. If parameter is set to "LocalSystem", then a connection error is displayed. Use "SYSTEM" instead, in that case. .PARAMETER SuppressRestart If set to $true then the required restart will be suppressed. You will need to restart the service before changes will take effect. The default value is $false. .PARAMETER ServerName Specifies the host name that will be used when restarting the SQL Server instance. If the SQL Server belongs to a cluster or availability group specify the host name for the listener or cluster group. The specified name must match the name that is used by the certificate specified for the parameter `Thumbprint`. Default value is `localhost`. #> function Get-TargetResource { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('SqlServerDsc.AnalyzerRules\Measure-CommandsNeededToLoadSMO', '', Justification='Neither command is needed for this resource')] [CmdletBinding()] [OutputType([System.Collections.Hashtable])] param ( [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter(Mandatory = $true)] [System.String] [AllowEmptyString()] $Thumbprint, [Parameter()] [System.Boolean] $ForceEncryption = $true, [Parameter()] [ValidateSet('Present', 'Absent')] [System.String] $Ensure = 'Present', [Parameter(Mandatory = $true)] [System.String] $ServiceAccount, [Parameter()] [System.Boolean] $SuppressRestart = $false, [Parameter()] [System.String] $ServerName = 'localhost' ) Write-Verbose -Message ( $script:localizedData.GetEncryptionSettings ` -f $InstanceName ) $encryptionSettings = Get-EncryptedConnectionSetting -InstanceName $InstanceName Write-Verbose -Message ( $script:localizedData.EncryptedSettings ` -f $encryptionSettings.Certificate, $encryptionSettings.ForceEncryption ) if ($Ensure -eq 'Present') { # Configuration manager requires thumbprint to be lowercase or it won't display the configured certificate. if (-not [string]::IsNullOrEmpty($Thumbprint)) { $Thumbprint = $Thumbprint.ToLower() } $ensureValue = 'Present' $certificateSettings = Test-CertificatePermission -Thumbprint $Thumbprint -ServiceAccount $ServiceAccount if ($encryptionSettings.Certificate -ine $Thumbprint) { Write-Verbose -Message ( $script:localizedData.ThumbprintResult ` -f $encryptionSettings.Certificate, $Thumbprint ) $ensureValue = 'Absent' } if ($encryptionSettings.ForceEncryption -ne $ForceEncryption) { Write-Verbose -Message ( $script:localizedData.ForceEncryptionResult ` -f $encryptionSettings.ForceEncryption, $ForceEncryption ) $ensureValue = 'Absent' } if (-not $certificateSettings) { Write-Verbose -Message ( $script:localizedData.CertificateSettings ` -f 'Configured' ) $ensureValue = 'Absent' } else { Write-Verbose -Message ( $script:localizedData.CertificateSettings ` -f 'Not Configured' ) } } else { $ensureValue = 'Absent' if ($encryptionSettings.ForceEncryption -eq $false) { Write-Verbose -Message ( $script:localizedData.EncryptionOff ) } else { $ensureValue = 'Present' Write-Verbose -Message ( $script:localizedData.ForceEncryptionResult ` -f $encryptionSettings.ForceEncryption, $false ) } if ($encryptionSettings.Certificate -eq '') { $encryptionSettings.Certificate = 'Empty' } else { $ensureValue = 'Present' Write-Verbose -Message ( $script:localizedData.ThumbprintResult ` -f $encryptionSettings.Certificate, 'Empty' ) } Write-Verbose -Message ( $script:localizedData.EncryptedSettings ` -f $encryptionSettings.Certificate, $encryptionSettings.ForceEncryption ) } return @{ InstanceName = [System.String] $InstanceName Thumbprint = [System.String] $encryptionSettings.Certificate ForceEncryption = [System.Boolean] $encryptionSettings.ForceEncryption Ensure = [System.String] $ensureValue ServiceAccount = [System.String] $ServiceAccount SuppressRestart = [System.Boolean] $SuppressRestart ServerName = [System.String] $ServerName } } <# .SYNOPSIS Enables SQL Server Encryption Connection. .PARAMETER InstanceName Name of the SQL Server instance to be configured. .PARAMETER Thumbprint Thumbprint of the certificate being used for encryption. If parameter Ensure is set to 'Absent', then the parameter Thumbprint can be set to an empty string. .PARAMETER ForceEncryption If all connections to the SQL instance should be encrypted. If this parameter is not assigned a value, the default is that all connections must be encrypted. .PARAMETER Ensure If Encryption should be Enabled (Present) or Disabled (Absent). .PARAMETER ServiceAccount Name of the account running the SQL Server service. .PARAMETER SuppressRestart If set to $true then the required restart will be suppressed. You will need to restart the service before changes will take effect. The default value is $false. .PARAMETER ServerName Specifies the host name that will be used when restarting the SQL Server instance. If the SQL Server belongs to a cluster or availability group specify the host name for the listener or cluster group. The specified name must match the name that is used by the certificate specified for the parameter `Thumbprint`. Default value is `localhost`. #> function Set-TargetResource { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('SqlServerDsc.AnalyzerRules\Measure-CommandsNeededToLoadSMO', '', Justification='Neither command is needed for this resource')] [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter(Mandatory = $true)] [System.String] [AllowEmptyString()] $Thumbprint, [Parameter()] [System.Boolean] $ForceEncryption = $true, [Parameter()] [ValidateSet('Present', 'Absent')] [System.String] $Ensure = 'Present', [Parameter(Mandatory = $true)] [System.String] $ServiceAccount, [Parameter()] [System.Boolean] $SuppressRestart = $false, [Parameter()] [System.String] $ServerName = 'localhost' ) # Configuration manager requires thumbprint to be lowercase or it won't display the configured certificate. if (-not [string]::IsNullOrEmpty($Thumbprint)) { $Thumbprint = $Thumbprint.ToLower() } $parameters = @{ InstanceName = $InstanceName Thumbprint = $Thumbprint ForceEncryption = $ForceEncryption Ensure = $Ensure ServiceAccount = $ServiceAccount } $encryptionState = Get-TargetResource @parameters if ($Ensure -eq 'Present') { if ($ForceEncryption -ne $encryptionState.ForceEncryption -or $Thumbprint -ne $encryptionState.Thumbprint) { Write-Verbose -Message ( $script:localizedData.SetEncryptionSetting -f $InstanceName, $Thumbprint, $ForceEncryption ) Set-EncryptedConnectionSetting -InstanceName $InstanceName -Thumbprint $Thumbprint -ForceEncryption $ForceEncryption } if ((Test-CertificatePermission -Thumbprint $Thumbprint -ServiceAccount $ServiceAccount) -eq $false) { Write-Verbose -Message ( $script:localizedData.SetCertificatePermission -f $Thumbprint, $ServiceAccount ) Set-CertificatePermission -Thumbprint $Thumbprint -ServiceAccount $ServiceAccount } } else { Write-Verbose -Message ( $script:localizedData.RemoveEncryptionSetting -f $InstanceName ) Set-EncryptedConnectionSetting -InstanceName $InstanceName -Thumbprint '' -ForceEncryption $false } if ($SuppressRestart) { Write-Verbose -Message ( $script:localizedData.SuppressRequiredRestart -f $InstanceName ) } else { Write-Verbose -Message ( $script:localizedData.RestartingService -f $InstanceName ) Restart-SqlService -ServerName $ServerName -InstanceName $InstanceName } } <# .SYNOPSIS Tests the SQL Server Encryption configuration. .PARAMETER InstanceName Name of the SQL Server instance to be configured. .PARAMETER Thumbprint Thumbprint of the certificate being used for encryption. If parameter Ensure is set to 'Absent', then the parameter Thumbprint can be set to an empty string. .PARAMETER ForceEncryption If all connections to the SQL instance should be encrypted. If this parameter is not assigned a value, the default is, set to true, that all connections must be encrypted. .PARAMETER Ensure If Encryption should be Enabled (Present) or Disabled (Absent). .PARAMETER ServiceAccount Name of the account running the SQL Server service. .PARAMETER SuppressRestart If set to $true then the required restart will be suppressed. You will need to restart the service before changes will take effect. The default value is $false. Not used in Test-TargetResource. .PARAMETER ServerName Specifies the host name that will be used when restarting the SQL Server instance. If the SQL Server belongs to a cluster or availability group specify the host name for the listener or cluster group. The specified name must match the name that is used by the certificate specified for the parameter `Thumbprint`. Default value is `localhost`. Not used in Test-TargetResource. #> function Test-TargetResource { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('SqlServerDsc.AnalyzerRules\Measure-CommandsNeededToLoadSMO', '', Justification='Neither command is needed for this resource')] [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true)] [System.String] $InstanceName, [Parameter(Mandatory = $true)] [System.String] [AllowEmptyString()] $Thumbprint, [Parameter()] [System.Boolean] $ForceEncryption = $true, [Parameter()] [ValidateSet('Present', 'Absent')] [System.String] $Ensure = 'Present', [Parameter(Mandatory = $true)] [System.String] $ServiceAccount, [Parameter()] [System.Boolean] $SuppressRestart = $false, [Parameter()] [System.String] $ServerName = 'localhost' ) $parameters = @{ InstanceName = $InstanceName Thumbprint = $Thumbprint ForceEncryption = $ForceEncryption Ensure = $Ensure ServiceAccount = $ServiceAccount } Write-Verbose -Message ( $script:localizedData.TestingConfiguration ` -f $InstanceName ) $encryptionState = Get-TargetResource @parameters return $Ensure -eq $encryptionState.Ensure } <# .SYNOPSIS Gets the SQL Server Encryption settings. Returns Certificate thumbprint and ForceEncryption setting. .PARAMETER InstanceName Name of the SQL Server Instance to be configured. #> function Get-SqlEncryptionValue { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $InstanceName ) $sqlInstance = Get-Item 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' if ($sqlInstance) { try { $sqlInstanceId = (Get-ItemProperty -Path $sqlInstance.PSPath -Name $InstanceName).$InstanceName } catch { throw ($script:localizedData.InstanceNotFound -f $InstanceName) } return Get-Item "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$sqlInstanceId\MSSQLServer\SuperSocketNetLib" } } <# .SYNOPSIS Gets the SQL Server Encryption settings. Returns Certificate thumbprint and ForceEncryption setting. .PARAMETER InstanceName Name of the SQL Server Instance to be configured. #> function Get-EncryptedConnectionSetting { [CmdletBinding()] [OutputType([Hashtable])] param ( [Parameter(Mandatory = $true)] [string] $InstanceName ) $superSocketNetLib = Get-SqlEncryptionValue -InstanceName $InstanceName if ($superSocketNetLib) { return @{ ForceEncryption = [System.Boolean](Get-ItemProperty -Path $superSocketNetLib.PSPath -Name 'ForceEncryption').ForceEncryption Certificate = (Get-ItemProperty -Path $superSocketNetLib.PSPath -Name 'Certificate').Certificate } } return $null } <# .SYNOPSIS Sets the SQL Server Encryption settings. .PARAMETER InstanceName Name of the SQL Server Instance to be configured. .PARAMETER Thumbprint Thumbprint of the certificate being used for encryption. .PARAMETER ForceEncryption If all connections to the SQL instance should be encrypted. #> function Set-EncryptedConnectionSetting { [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('AvoidThrowOutsideOfTry', '', Justification='Because the code throws based on an prior expression')] [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $InstanceName, [Parameter(Mandatory = $true)] [AllowEmptyString()] [string] $Thumbprint, [Parameter(Mandatory = $true)] [System.Boolean] $ForceEncryption ) $superSocketNetLib = Get-SqlEncryptionValue -InstanceName $InstanceName if ($superSocketNetLib) { Set-ItemProperty -Path $superSocketNetLib.PSPath -Name 'Certificate' -Value $Thumbprint Set-ItemProperty -Path $superSocketNetLib.PSPath -Name 'ForceEncryption' -Value $([int]$ForceEncryption) } else { throw ($script:localizedData.CouldNotFindEncryptionValues -f $InstanceName) } } <# .SYNOPSIS Gets the permissions of the private key on the certificate. .PARAMETER Thumbprint Thumbprint of the certificate being used for encryption. #> function Get-CertificateAcl { [CmdletBinding()] [OutputType([System.Collections.Hashtable])] param ( [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [string] $Thumbprint ) $cert = Get-ChildItem -Path cert:\LocalMachine\My | Where-Object -FilterScript { $PSItem.Thumbprint -eq $Thumbprint } # Location of the machine related keys $keyPath = $env:ProgramData + '\Microsoft\Crypto\RSA\MachineKeys\' $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName $keyFullPath = $keyPath + $keyName Write-Verbose -Message ( $script:localizedData.PrivateKeyPath ` -f $keyFullPath ) try { # Get the current acl of the private key return @{ ACL = (Get-Item $keyFullPath).GetAccessControl() Path = $keyFullPath } } catch { throw $_ } } <# .SYNOPSIS Gives the service account read permissions to the private key on the certificate. .PARAMETER Thumbprint Thumbprint of the certificate being used for encryption. .PARAMETER ServiceAccount The service account running SQL Server service. #> function Set-CertificatePermission { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [string] $Thumbprint, [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [string] $ServiceAccount ) # Specify the user, the permissions and the permission type $permission = "$($ServiceAccount)", 'Read', 'Allow' $accessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $permission try { # Get the current acl of the private key $acl = Get-CertificateAcl -Thumbprint $Thumbprint # Add the new ace to the acl of the private key $acl.ACL.AddAccessRule($accessRule) # Write back the new acl Set-Acl -Path $acl.Path -AclObject $acl.ACL } catch { throw $_ } } <# .SYNOPSIS Test if the service account has read permissions to the private key on the certificate. .PARAMETER Thumbprint Thumbprint of the certificate being used for encryption. .PARAMETER ServiceAccount The service account running SQL Server service. #> function Test-CertificatePermission { [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [string] $Thumbprint, [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [string] $ServiceAccount ) # Specify the user, the permissions and the permission type $permission = "$($ServiceAccount)", 'Read', 'Allow' $accessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $permission try { # Get the current acl of the private key $acl = Get-CertificateAcl -Thumbprint $Thumbprint [array] $permissions = $acl.ACL.Access.Where( { $_.IdentityReference -eq $accessRule.IdentityReference }) if ($permissions.Count -eq 0) { return $false } $rights = $permissions[0].FileSystemRights.value__ #check if the rights contains Read permission, 131209 is the bitwise number for read. This allows the permissions to be higher then read. if (($rights -bor 131209) -ne $rights) { return $false } return $true } catch { return $false } } |