SqlEndpointPermission

SqlEndpointPermission

Parameters

| Parameter | Attribute | DataType | Description | Allowed Values | | | | --- | | InstanceName | Key | String | The name of the SQL Server instance to be configured. | | | Principal | Key | String | The login to which permission will be set. | | | Name | Required | String | The name of the endpoint. | | | ServerName | Write | String | The host name of the SQL Server to be configured. Default value is the current computer name. | | | Ensure | Write | String | If the permission should be present or absent. Default value is 'Present'. | Present, Absent | | Permission | Write | String | The permission to set for the login. Valid value for permission is only 'CONNECT'. | CONNECT |

Description

The SqlEndpointPermission DSC resource is used to give connect permission to an endpoint for a user (login).

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.

Examples

Example 1

This example will add connect permission to the credentials provided in $SqlServiceCredential to the endpoint named 'DefaultMirrorEndpoint'.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential,

        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlServiceCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlEndpointPermission 'SQLConfigureEndpointPermission'
        {
            Ensure               = 'Present'
            ServerName           = 'SQLTEST'
            InstanceName         = 'DSCINSTANCE'
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceCredential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example will add connect permission to the credentials provided in $SqlServiceCredential to the endpoint named 'DefaultMirrorEndpoint'.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential,

        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlServiceCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlEndpointPermission 'SQLConfigureEndpointPermission'
        {
            Ensure               = 'Absent'
            ServerName           = 'SQLTEST'
            InstanceName         = 'DSCINSTANCE'
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceCredential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 3

This example will add connect permission to both an Always On primary replica and an Always On secondary replica, and where each replica has a different SQL service account.

[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification='The variable $ConfigurationData is used by the HQRM test')]
param ()

$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName        = '*'
            InstanceName = 'MSSQLSERVER'

            # Not recommended for production. Only set here to pass CI.
            PsDscAllowPlainTextPassword = $true
        },

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

        @{
            NodeName = 'SQLNODE02.company.local'
            Role     = 'SecondaryReplica'
        }
    )
}

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential,

        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlServiceNode1Credential,

        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlServiceNode2Credential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node $AllNodes.Where{ $_.Role -eq 'PrimaryReplica' }.NodeName
    {
        SqlEndpointPermission 'SQLConfigureEndpointPermissionPrimary'
        {
            Ensure               = 'Present'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode1Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlEndpointPermission 'SQLConfigureEndpointPermissionSecondary'
        {
            Ensure               = 'Present'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode2Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }

    Node $AllNodes.Where{ $_.Role -eq 'SecondaryReplica' }.NodeName
    {
        SqlEndpointPermission 'SQLConfigureEndpointPermissionPrimary'
        {
            Ensure               = 'Present'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode1Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlEndpointPermission 'SQLConfigureEndpointPermissionSecondary'
        {
            Ensure               = 'Present'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode2Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 4

This example will remove connect permission to both an Always On primary replica and an Always On secondary replica, and where each replica has a different SQL service account.

[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification='The variable $ConfigurationData is used by the HQRM test')]
param ()

$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName        = '*'
            InstanceName = 'MSSQLSERVER'

            # Not recommended for production. Only set here to pass CI.
            PsDscAllowPlainTextPassword = $true
        },

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

        @{
            NodeName = 'SQLNODE02.company.local'
            Role     = 'SecondaryReplica'
        }
    )
}

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential,

        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlServiceNode1Credential,

        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlServiceNode2Credential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node $AllNodes.Where{ $_.Role -eq 'PrimaryReplica' }.NodeName
    {
        SqlEndpointPermission 'RemoveSQLConfigureEndpointPermissionPrimary'
        {
            Ensure               = 'Absent'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode1Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlEndpointPermission 'RemoveSQLConfigureEndpointPermissionSecondary'
        {
            Ensure               = 'Absent'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode2Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }

    Node $AllNodes.Where{ $_.Role -eq 'SecondaryReplica' }.NodeName
    {
        SqlEndpointPermission 'RemoveSQLConfigureEndpointPermissionPrimary'
        {
            Ensure               = 'Absent'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode1Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlEndpointPermission 'RemoveSQLConfigureEndpointPermissionSecondary'
        {
            Ensure               = 'Absent'
            ServerName           = $Node.NodeName
            InstanceName         = $Node.InstanceName
            Name                 = 'DefaultMirrorEndpoint'
            Principal            = $SqlServiceNode2Credential.UserName
            Permission           = 'CONNECT'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}