Examples/xSQLServerEndpointPermission.ps1

$ConfigData = @{
    AllNodes = @(
        @{
            NodeName= "*"
            CertificateFile = "C:\Certificates\dsc-public.cer" 
            Thumbprint = "D6F57B6BE46A7162138687FB74DBAA1D4EB1A59B" 
            SqlInstanceName = "MSSQLSERVER"
            PSDscAllowDomainUser = $true
        },

        @{ 
            NodeName = 'SQLNODE01.company.local'
            Role = "PrimaryReplica"
        },

        @{
            NodeName = 'SQLNODE02.company.local' 
            Role = "SecondaryReplica" 
        }
    )
}
 
Configuration SQLAlwaysOnNodeConfig 
{
    param
    (
        [Parameter(Mandatory=$false)] 
        [ValidateNotNullorEmpty()] 
        [PsCredential] $SqlAdministratorCredential,

        [Parameter(Mandatory=$true)] 
        [ValidateNotNullorEmpty()] 
        [PsCredential] $SqlServiceCredentialNode1, 

        [Parameter(Mandatory=$true)] 
        [ValidateNotNullorEmpty()] 
        [PsCredential] $SqlServiceCredentialNode2
    )

    Import-DscResource -ModuleName PSDesiredStateConfiguration
    Import-DscResource -ModuleName xSqlServer

    Node $AllNodes.Where{$_.Role -eq "PrimaryReplica" }.NodeName
    {
        #region Remove endpoint permissions
        xSQLServerEndpointPermission SQLConfigureEndpointPermissionPrimary
        {
            Ensure = "Present"
            NodeName = $Node.NodeName
            InstanceName = $Node.SqlInstanceName
            Name = "DefaultMirrorEndpoint"
            Principal = $SqlServiceCredentialNode1.UserName 
            Permission = "CONNECT"

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        xSQLServerEndpointPermission SQLConfigureEndpointPermissionSecondary
        {
           Ensure = "Present"
           NodeName = $Node.NodeName
           InstanceName = $Node.SqlInstanceName
           Name = "DefaultMirrorEndpoint"
           Principal = $SqlServiceCredentialNode2.UserName 
           Permission = "CONNECT"
        
           PsDscRunAsCredential = $SqlAdministratorCredential
        }
        #endregion
        
        #region Remove endpoint permissions
        xSQLServerEndpointPermission RemoveSQLConfigureEndpointPermissionPrimary
        {
           Ensure = "Absent"
           NodeName = $Node.NodeName
           InstanceName = $Node.SqlInstanceName
           Name = "DefaultMirrorEndpoint"
           Principal = $SqlServiceCredentialNode2.UserName 
           Permission = "CONNECT"
        
           PsDscRunAsCredential = $SqlAdministratorCredential
        
           DependsOn = "[xSQLServerEndpointPermission]SQLConfigureEndpointPermissionPrimary"
        }

        xSQLServerEndpointPermission RemoveSQLConfigureEndpointPermissionSecondary
        {
           Ensure = "Absent"
           NodeName = $Node.NodeName
           InstanceName = $Node.SqlInstanceName
           Name = "DefaultMirrorEndpoint"
           Principal = $SqlServiceCredentialNode2.UserName 
           Permission = "CONNECT"
        
           PsDscRunAsCredential = $SqlAdministratorCredential
        
           DependsOn = "[xSQLServerEndpointPermission]SQLConfigureEndpointPermissionSecondary"
        }
        #endregion
   }

    Node $AllNodes.Where{ $_.Role -eq "SecondaryReplica" }.NodeName
    {         
        xSQLServerEndpointPermission SQLConfigureEndpointPermissionPrimary
        {
            Ensure = "Present"
            NodeName = $Node.NodeName
            InstanceName = $Node.SqlInstanceName
            Name = "DefaultMirrorEndpoint"
            Principal = $SqlServiceCredentialNode1.UserName 
            Permission = "CONNECT"
        
            PsDscRunAsCredential = $SqlAdministratorCredential
        
            DependsOn = "[xSQLServerEndpoint]SQLConfigureEndpoint"
        }

        xSQLServerEndpointPermission SQLConfigureEndpointPermissionSecondary
        {
            Ensure = "Present"
            NodeName = $Node.NodeName
            InstanceName = $Node.SqlInstanceName
            Name = "DefaultMirrorEndpoint"
            Principal = $SqlServiceCredentialNode2.UserName 
            Permission = "CONNECT"
        
            PsDscRunAsCredential = $SqlAdministratorCredential
        
            DependsOn = "[xSQLServerEndpoint]SQLConfigureEndpoint"
        }

        # Remove endpoint permissions
        xSQLServerEndpointPermission RemoveSQLConfigureEndpointPermissionPrimary
        {
           Ensure = "Absent"
           NodeName = $Node.NodeName
           InstanceName = $Node.SqlInstanceName
           Name = "DefaultMirrorEndpoint"
           Principal = $SqlServiceCredentialNode2.UserName 
           Permission = "CONNECT"
        
           PsDscRunAsCredential = $SqlAdministratorCredential
        
           DependsOn = "[xSQLServerEndpointPermission]SQLConfigureEndpointPermissionPrimary"
        }

        xSQLServerEndpointPermission RemoveSQLConfigureEndpointPermissionSecondary
        {
           Ensure = "Absent"
           NodeName = $Node.NodeName
           InstanceName = $Node.SqlInstanceName
           Name = "DefaultMirrorEndpoint"
           Principal = $SqlServiceCredentialNode2.UserName 
           Permission = "CONNECT"
        
           PsDscRunAsCredential = $SqlAdministratorCredential
        
           DependsOn = "[xSQLServerEndpointPermission]SQLConfigureEndpointPermissionSecondary"
        } 
    }
}

$SqlAdministratorCredential = Get-Credential -Message "Enter credentials for SQL Server administrator account"
$SqlServiceCredentialNode1 = Get-Credential -Message "Enter credentials for SQL Service account for primary replica"
$SqlServiceCredentialNode2 = Get-Credential -Message "Enter credentials for SQL Service account for secondary replica" 

SQLAlwaysOnNodeConfig `
    -SqlAdministratorCredential $SqlAdministratorCredential `
    -SqlServiceCredentialNode1 $SqlServiceCredentialNode1 `
    -SqlServiceCredentialNode2 $SqlServiceCredentialNode2 `
    -ConfigurationData $ConfigData `
    -OutputPath 'C:\Configuration'