Skip to content

SqlServerSecureConnection

johlju edited this page Jun 9, 2020 · 1 revision

SqlServerSecureConnection

Parameters

Parameter Attribute DataType Description Allowed Values
InstanceName Key String Name of the SQL Server instance to be configured.
Thumbprint Required String Thumbprint of the certificate being used for encryption. If parameter Ensure is set to 'Absent', then the parameter Certificate can be set to an empty string.
ForceEncryption Write Boolean 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.
ServiceAccount Required String 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.
SuppressRestart Write Boolean 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.
Ensure Write String If Encryption should be Enabled (Present) or Disabled (Absent). Present, Absent

Description

The SqlSecureConnection DSC resource Configures SQL connections to be encrypted. Read more about encrypted connections in this article Enable Encrypted Connections.

Note: The 'LocalSystem' service account will return a connection error, even though the connection has been successful. In that case, the 'SYSTEM' service account can be used.

Requirements

  • Target machine must be running Windows Server 2012 or later.
  • You must have a Certificate that is trusted and issued for ServerAuthentication.
  • The name of the Certificate must be the fully qualified domain name (FQDN) of the computer.
  • The Certificate must be installed in the LocalMachine Personal store.
  • If PsDscRunAsCredential common parameter is used to run the resource, the specified credential must have permissions to connect to the SQL Server instance specified in InstanceName.

Known issues

All issues are not listed here, see here for all open issues.

Examples

Example 1

This example performs a standard Sql encryption setup. Forcing all connections to be encrypted.

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlServerSecureConnection 'ForceSecureConnection'
        {
            InstanceName    = 'MSSQLSERVER'
            Thumbprint      = 'fb0b82c94b80da26cf0b86f10ec0c50ae7864a2c'
            ForceEncryption = $true
            Ensure          = 'Present'
            ServiceAccount  = 'SqlSvc'
        }
    }
}

Example 2

This example performs a standard Sql encryption setup. All connections are not forced to be encrypted.

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlServerSecureConnection 'SecureConnectionNotForced'
        {
            InstanceName    = 'MSSQLSERVER'
            Thumbprint      = 'fb0b82c94b80da26cf0b86f10ec0c50ae7864a2c'
            ForceEncryption = $false
            Ensure          = 'Present'
            ServiceAccount  = 'SqlSvc'
        }
    }
}

Example 3

This example performs a standard Sql encryption setup. Forcing all connections to be encrypted.

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlServerSecureConnection 'SecureConnectionAbsent'
        {
            InstanceName    = 'MSSQLSERVER'
            Thumbprint      = ''
            Ensure          = 'Absent'
            ServiceAccount  = 'SqlSvc'
        }
    }
}

Example 4

This example performs a standard Sql encryption setup using the "SYSTEM" account. Note that the "LocalSystem" account should not be used because it returns a connection error, even though it inherits the "SYSTEM" account's privileges.

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlServerSecureConnection 'SecureConnectionUsingSYSTEMAccount'
        {
            InstanceName    = 'MSSQLSERVER'
            Thumbprint      = 'fb0b82c94b80da26cf0b86f10ec0c50ae7864a2c'
            ForceEncryption = $false
            Ensure          = 'Present'
            ServiceAccount  = 'SYSTEM'
        }
    }
}
Clone this wiki locally