Skip to content

REFERENCE.md

Latest commit

 

History

History
1445 lines (832 loc) · 43.7 KB

REFERENCE.md

File metadata and controls

1445 lines (832 loc) · 43.7 KB

Reference

Table of Contents

Defined types

Resource types

Functions

Tasks

  • get_sql_logins: Retrieve information about the logins configured for a SQL Server instance.
  • get_sqlagent_jobs: Return information about SQL Agent jobs and job steps.
  • set_sql_logins: Set IsDisabled, and Password properties of a SQL Login
  • start_sql_agent_job: Start SQL Agent jobs on a server. You can start at a specified job step number (zero based indexes), and you can either wait on the job to co

Defined types

sqlserver::config

Define Resource Type: sqlserver::config

Examples

sqlserver::config{'MSSQLSERVER':
  admin_user => 'sa',
  admin_pass => 'PuppetP@ssword1',
}

Parameters

The following parameters are available in the sqlserver::config defined type:

instance_name

Data type: String[1,16]

The instance name you want to manage. Defaults to the $title when not defined explicitly.

Default value: $title

admin_user

Data type: Optional[Variant[Sensitive[String], String]]

Only required for SQL_LOGIN type. A user/login who has sysadmin rights on the server Can be passed as a sensitive value

Default value: undef

admin_pass

Data type: Optional[Variant[Sensitive[String], String]]

Only required for SQL_LOGIN type. The password in order to access the server to be managed. Can be passed as a sensitive value

Default value: undef

admin_login_type

Data type: Enum['SQL_LOGIN', 'WINDOWS_LOGIN']

The type of account use to configure the server. Valid values are SQL_LOGIN and WINDOWS_LOGIN, with a default of SQL_LOGIN The SQL_LOGIN requires the admin_user and admin_pass to be set The WINDOWS_LOGIN requires the adm_user and admin_pass to be empty or undefined

Default value: 'SQL_LOGIN'

sqlserver::database

Requirement/Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

Parameters

The following parameters are available in the sqlserver::database defined type:

db_name

Data type: String[1,128]

The database you would like to manage

Default value: $title

instance

Data type: String[1,16]

The name of the instance which to connect to, instance names can not be longer than 16 characters

Default value: 'MSSQLSERVER'

ensure

Data type: Enum['present', 'absent']

Defaults to 'present', valid values are 'present' | 'absent'

Default value: 'present'

compatibility

Data type: Integer

Numberic representation of what SQL Server version you want the database to be compatabible with.

Default value: 100

collation_name

Data type: Optional[String[1]]

Default value: undef

filestream_non_transacted_access

Data type: Optional[Enum['OFF', 'READ_ONLY', 'FULL']]

Value should be { OFF | READ_ONLY | FULL } Specifies the level of non-transactional FILESTREAM access to the database.

Default value: undef

filestream_directory_name

Data type: Optional[Pattern[/^[\w|\s]+$/]]

A windows-compatible directory name. This name should be unique among all the Database_Directory names in the SQL Server instance. Uniqueness comparison is case-insensitive, regardless of SQL Server collation settings. This option should be set before creating a FileTable in this database.

Default value: undef

filespec_name

Data type: Optional[String[1,128]]

Specifies the logical name for the file. NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses. A FILESTREAM filegroup cannot be named PRIMARY.

Default value: undef

filespec_filename

Data type: Optional[Stdlib::Absolutepath]

Specifies the operating system (physical) file name.

Default value: undef

filespec_size

Data type: Optional[String[1]]

Specifies the size of the file. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Values can not be greater than 2147483647

Default value: undef

filespec_maxsize

Data type: Optional[String[1]]

Specifies the maximum size to which the file can grow. MAXSIZE cannot be specified when the os_file_name is specified as a UNC path

Default value: undef

filespec_filegrowth

Data type: Optional[String[1]]

Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH cannot be specified when the os_file_name is specified as a UNC path. FILEGROWTH does not apply to a FILESTREAM filegroup.

Default value: undef

log_name

Data type: Optional[String[1,128]]

Specifies the logical name for the file. NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses. A FILESTREAM filegroup cannot be named PRIMARY.

Default value: undef

log_filename

Data type: Optional[Stdlib::Absolutepath]

Specifies the operating system (physical) file name.

Default value: undef

log_size

Data type: Optional[String[1]]

Specifies the size of the file. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Values can not be greater than 2147483647

Default value: undef

log_maxsize

Data type: Optional[String[1]]

Specifies the maximum size to which the file can grow. MAXSIZE cannot be specified when the os_file_name is specified as a UNC path

Default value: undef

log_filegrowth

Data type: Optional[String[1]]

Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH cannot be specified when the os_file_name is specified as a UNC path. FILEGROWTH does not apply to a FILESTREAM filegroup.

Default value: undef

containment

Data type: Enum['PARTIAL', 'NONE']

Defaults to 'NONE'. Other possible values are 'PARTIAL', see http://msdn.microsoft.com/en-us/library/ff929071.aspx

Default value: 'NONE'

default_fulltext_language

Data type: String[1]

Language name i.e. us_english which are documented at http://msdn.microsoft.com/en-us/library/ms190303.aspx

Default value: 'English'

default_language

Data type: String[1]

Language name i.e. us_english which are documented at http://msdn.microsoft.com/en-us/library/ms190303.aspx

Default value: 'us_english'

nested_triggers

Data type: Optional[Enum['ON', 'OFF']]

On | Off see http://msdn.microsoft.com/en-us/library/ms178101.aspx

Default value: undef

transform_noise_words

Data type: Optional[Enum['ON', 'OFF']]

ON | OFF

Default value: undef

two_digit_year_cutoff

Data type: Integer[1753, 9999]

Defaults to 2049 | <any year between 1753 and 9999>

Default value: 2049

db_chaining

Data type: Enum['ON', 'OFF']

ON | OFF When ON is specified, the database can be the source or target of a cross-database ownership chain. When OFF, the database cannot participate in cross-database ownership chaining. The default is OFF.

Default value: 'OFF'

trustworthy

Data type: Enum['ON', 'OFF']

When ON is specified, database modules that use an impersonation context can access resources outside the database. For example, views, user-defined functions, or stored procedures. When OFF, database modules in an impersonation context cannot access resources outside the database. The default is OFF.

Default value: 'OFF'

sqlserver::login

Requirement/Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

Parameters

The following parameters are available in the sqlserver::login defined type:

login

Data type: String[1, 128]

The SQL or Windows login you would like to manage

Default value: $title

instance

Data type: String[1,16]

The name of the instance which to connect to, instance names can not be longer than 16 characters

Default value: 'MSSQLSERVER'

ensure

Data type: Enum['present', 'absent']

Defaults to 'present', valid values are 'present' | 'absent'

Default value: 'present'

password

Data type: Optional[Variant[Sensitive[String], String]]

Plain text password. Only applicable when Login_Type = 'SQL_LOGIN'. Can be passed through as a sensitive value.

Default value: undef

svrroles

Data type: Hash

A hash of preinstalled server roles that you want assigned to this login. sample usage would be { 'diskadmin' => 1, 'dbcreator' => 1, 'sysadmin' => 0, }

Default value: {}

login_type

Data type: Enum['SQL_LOGIN', 'WINDOWS_LOGIN']

Defaults to 'SQL_LOGIN', possible values are 'SQL_LOGIN' or 'WINDOWS_LOGIN'

Default value: 'SQL_LOGIN'

default_database

Data type: String

The database that when connecting the login should default to, the default value is 'master'

Default value: 'master'

default_language

Data type: String

The default language is 'us_english', a list of possible

Default value: 'us_english'

check_expiration

Data type: Boolean

Default value is false, possible values of true | false. Only applicable when Login_Type = 'SQL_LOGIN'.

Default value: false

check_policy

Data type: Boolean

Default value is false, possible values are true | false. Only applicable when Login_Type = 'SQL_LOGIN'.

Default value: true

disabled

Data type: Boolean

Default value is false. Accepts [Boolean] values of true or false.

Default value: false

permissions

Data type: Hash

A hash of permissions that should be managed for the login. Valid keys are 'GRANT', 'GRANT_WITH_OPTION', 'DENY' or 'REVOKE'. Valid values must be an array of Strings i.e. {'GRANT' => ['CONNECT SQL', 'CREATE ANY DATABASE'] }

Default value: {}

sqlserver::login::permissions

Requirement/Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

Parameters

The following parameters are available in the sqlserver::login::permissions defined type:

login

Data type: String[1,128]

The login for which the permission will be manage.

permissions

Data type: Array[String[4,128]]

An array of permissions you would like managed. i.e. ['SELECT', 'INSERT', 'UPDATE', 'DELETE']

state

Data type: Pattern[/(?i)^(GRANT|REVOKE|DENY)$/]

The state you would like the permission in. Accepts 'GRANT', 'DENY', 'REVOKE'. Please note that REVOKE equates to absent and will default to database and system level permissions.

Default value: 'GRANT'

instance

Data type: String[1,16]

The name of the instance where the user and database exists. Defaults to 'MSSQLSERVER'

Default value: 'MSSQLSERVER'

with_grant_option

Data type: Boolean

Bolean value that allows user to grant options.

Default value: false

sqlserver::role

Requirement/Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

Parameters

The following parameters are available in the sqlserver::role defined type:

ensure

Data type: Enum['present', 'absent']

Whether the role should be absent or present

Default value: 'present'

role

Data type: String[1,128]

The name of the role for which the permissions will be manage.

Default value: $title

instance

Data type: String[1,16]

The name of the instance where the role and database exists. Defaults to 'MSSQLSERVER'

Default value: 'MSSQLSERVER'

authorization

Data type: Optional[String]

The database principal that should own the role

Default value: undef

type

Data type: Enum['SERVER', 'DATABASE']

Whether the Role is SERVER or DATABASE

Default value: 'SERVER'

database

Data type: String[1,128]

The name of the database the role exists on when specifying type => 'DATABASE'. Defaults to 'master'

Default value: 'master'

permissions

Data type: Hash

A hash of permissions that should be managed for the role. Valid keys are 'GRANT', 'GRANT_WITH_OPTION', 'DENY' or 'REVOKE'. Valid values must be an array of Strings i.e. {'GRANT' => ['CONNECT', 'CREATE ANY DATABASE'] }

Default value: {}

members

Data type: Array[String]

An array of users/logins that should be a member of the role

Default value: []

members_purge

Data type: Boolean

Whether we should purge any members not listed in the members parameter. Default: false

Default value: false

sqlserver::role::permissions

Requirement/Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

Parameters

The following parameters are available in the sqlserver::role::permissions defined type:

role

Data type: String[1,128]

The name of the role for which the permissions will be manage.

permissions

Data type: Array[String[4,128]]

An array of permissions you want manged for the given role

state

Data type: Pattern[/(?i)^(GRANT|REVOKE|DENY)$/]

The state you would like the permission in. Accepts 'GRANT', 'DENY', 'REVOKE'. Please note that REVOKE equates to absent and will default to database and system level permissions.

Default value: 'GRANT'

with_grant_option

Data type: Boolean

Whether to give the role the option to grant this permission to other principal objects, accepts true or false, defaults to false

Default value: false

type

Data type: Enum['SERVER','DATABASE']

Whether the Role is SERVER or DATABASE

Default value: 'SERVER'

database

Data type: String[1,128]

The name of the database the role exists on when specifying type => 'DATABASE'. Defaults to 'master'

Default value: 'master'

instance

Data type: String[1,16]

The name of the instance where the role and database exists. Defaults to 'MSSQLSERVER'

Default value: 'MSSQLSERVER'

sqlserver::sp_configure

Required Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

@see http://msdn.microsoft.com/en-us/library/ms176069.aspx Reconfigure Explanation @see http://msdn.microsoft.com/en-us/library/ms189631.aspx Server Configuration Options

Parameters

The following parameters are available in the sqlserver::sp_configure defined type:

config_name

Data type: Pattern['^\w+']

The config name found within sys.configurations that you would like to update

Default value: $title

value

Data type: Integer

The value you would like to change to for the given config_name, must be an integer value

instance

Data type: String[1,16]

The name of the instance you would like to manage against

Default value: 'MSSQLSERVER'

reconfigure

Data type: Boolean

If you would like to run RECONFIGURE against the server after updating the value, defaults to true

Default value: true

with_override

Data type: Boolean

This pertains tot he reconfigure in which you would want to override or force the reconfigure, defaults to false

Default value: false

restart

Data type: Boolean

Will ensure service resource and notify if changes occur for a restart

Default value: false

sqlserver::user

Requirement/Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

Examples

sqlserver::user{'myUser':
  database  => 'loggingDatabase',
  login     => 'myUser',
}

Parameters

The following parameters are available in the sqlserver::user defined type:

user

Data type: String[1]

The username you want to manage, defaults to the title

Default value: $title

database

Data type: String[1,128]

The database you want the user to be created as

ensure

Data type: Enum['present', 'absent']

Ensure present or absent

Default value: 'present'

default_schema

Data type: Optional[String]

SQL schema you would like to default to, typically 'dbo'

Default value: undef

instance

Data type: String[1,16]

The named instance you want to manage against

Default value: 'MSSQLSERVER'

login

Data type: Optional[String[1]]

The login to associate the user with, by default SQL Server will assume user and login match if left empty

Default value: undef

password

Data type: Optional[String[1,128]]

The password for the user, can only be used when the database is a contained database.

Default value: undef

permissions

Data type: Hash

A hash of permissions that should be managed for the user. Valid keys are 'GRANT', 'GRANT_WITH_OPTION', 'DENY' or 'REVOKE'. Valid values must be an array of Strings i.e. {'GRANT' => ['SELECT', 'INSERT'] }

Default value: {}

sqlserver::user::permissions

Requirement/Dependencies: Requires defined type {sqlserver::config} in order to execute against the SQL Server instance

Parameters

The following parameters are available in the sqlserver::user::permissions defined type:

user

Data type: String[1,128]

The username for which the permission will be manage.

database

Data type: String[1,128]

The databaser you would like the permission managed on.

Default value: 'master'

permissions

Data type: Array[String[4,128]]

An array of permissions you would like managed. i.e. ['SELECT', 'INSERT', 'UPDATE', 'DELETE']

state

Data type: Pattern[/(?i)^(GRANT|REVOKE|DENY)$/]

The state you would like the permission in. Accepts 'GRANT', 'DENY', 'REVOKE'. Please note that REVOKE equates to absent and will default to database and system level permissions.

Default value: 'GRANT'

with_grant_option

Data type: Boolean

Whether to give the user the option to grant this permission to other users, accepts true or false, defaults to false

Default value: false

instance

Data type: String[1,16]

The name of the instance where the user and database exists. Defaults to 'MSSQLSERVER'

Default value: 'MSSQLSERVER'

Resource types

sqlserver_features

Due to our prefetch and unaware of what name the user will provide we munge the value to meet our expecations.

Properties

The following properties are available in the sqlserver_features type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

features

Valid values: Tools, BC, Conn, SSMS, ADV_SSMS, SDK, IS, MDS, BOL, DREPLAY_CTLR, DREPLAY_CLT, DQC

Specifies features to install, uninstall, or upgrade. The list of top-level features include BC, Conn, SSMS, ADV_SSMS, SDK, IS and MDS. The 'Tools' feature is deprecated. Instead specify 'BC', 'SSMS', 'ADV_SSMS', 'Conn', and 'SDK' explicitly.

Parameters

The following parameters are available in the sqlserver_features type.

install_switches

A hash of switches you want to pass to the installer

is_svc_account

Either domain user name or system account. Defaults to "NT AUTHORITY\NETWORK SERVICE"

is_svc_password

Password for domain user.

name

namevar

Due to our prefetch and unaware of what name the user will provide we munge the value to meet our expecations.

pid

Specify the SQL Server product key to configure which edition you would like to use.

provider

The specific backend to use for this sqlserver_features resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

source

Location of the source files.

windows_feature_source

Specify the location of the Windows Feature source files. This may be needed to install the .Net Framework. See https://support.microsoft.com/en-us/kb/2734782 for more information.

sqlserver_instance

Namevar

Properties

The following properties are available in the sqlserver_instance type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

features

Valid values: SQL, SQLEngine, Replication, FullText, DQ, AS, RS, POLYBASE, ADVANCEDANALYTICS

Specifies features to install, uninstall, or upgrade. The list of top-level features include SQLEngine, Replication, FullText, DQ AS, and RS. The 'SQL' feature is deprecated. Instead specify 'DQ', 'FullText', 'Replication', and 'SQLEngine' explicitly.

Parameters

The following parameters are available in the sqlserver_instance type.

agt_svc_account

Either domain user name or system account

agt_svc_password

Password for domain user name. Not required for system account

as_svc_account

The account used by the Analysis Services service.

as_svc_password

The password for the Analysis Services service account.

as_sysadmin_accounts

Specifies the list of administrator accounts to provision.

install_switches

A hash of switches you want to pass to the installer

name

namevar

Namevar

pid

Specify the SQL Server product key to configure which edition you would like to use.

polybase_svc_account

The account used by the Polybase Engine service. Only applicable for SQL Server 2016+.

polybase_svc_password

The password for the Polybase Engine service account. Only applicable for SQL Server 2016+.

provider

The specific backend to use for this sqlserver_instance resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

rs_svc_account

Specify the service account of the report server. This value is required. If you omit this value, Setup will use the default built-in account for the current operating system (either NetworkService or LocalSystem). If you specify a domain user account, the domain must be under 254 characters and the user name must be under 20 characters. The account name cannot contain the following characters: " / \ [ ] : ; | = , + * ? < >

rs_svc_password

Specify a strong password for the account. A strong password is at least 8 characters and includes a combination of upper and lower case alphanumeric characters and at least one symbol character. Avoid spelling an actual word or name that might be listed in a dictionary.

sa_pwd

Required when :security_mode => "SQL"

security_mode

Valid values: SQL

Specifies the security mode for SQL Server. If this parameter is not supplied, then Windows-only authentication mode is supported. Supported value: SQL

source

Location of source files.

sql_svc_account

Account for SQL Server service: Domain\User or system account.

sql_svc_password

A SQL Server service password is required only for a domain account.

sql_sysadmin_accounts

Windows account(s) to provision as SQL Server system administrators.

windows_feature_source

Specify the location of the Windows Feature source files. This may be needed to install the .Net Framework. See https://support.microsoft.com/en-us/kb/2734782 for more information.

sqlserver_tsql

Namevar

Properties

The following properties are available in the sqlserver_tsql type.

onlyif

SQL Query to run and only run if exits with non-zero

returns

Returns the result of the executed command

Default value: 0

Parameters

The following parameters are available in the sqlserver_tsql type.

command

command to run against an instance with the authenticated credentials used in sqlserver::config

database

initial database to connect to during query execution

Default value: master

instance

requires the usage of sqlserver::config with the user and password

name

namevar

Namevar

provider

The specific backend to use for this sqlserver_tsql resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

Functions

sqlserver::partial_params_args

Type: Ruby 4.x API

arguments that return string holds is conditional and decided by the the input given to function.

sqlserver::partial_params_args(Hash $args)

arguments that return string holds is conditional and decided by the the input given to function.

Returns: Variant[String] String Generated on the basis of provided values.

args

Data type: Hash

contains Enum['ON', 'OFF'] $db_chaining Enum['ON', 'OFF'] $trustworthy String[1] $default_fulltext_language String[1] $default_language Optional[Enum['ON', 'OFF']] $nested_triggers Optional[Enum['ON', 'OFF']] $transform_noise_words Integer[1753, 9999] $two_digit_year_cutoff

sqlserver_is_domain_or_local_user

Type: Ruby 3.x API

The sqlserver_is_domain_or_local_user function.

sqlserver_is_domain_or_local_user()

The sqlserver_is_domain_or_local_user function.

Returns: Boolean Returns true is the username is for local/domain.

sqlserver_upcase

Type: Ruby 3.x API

The sqlserver_upcase function.

sqlserver_upcase()

The sqlserver_upcase function.

Returns: Any Upcase values

sqlserver_validate_hash_uniq_values

Type: Ruby 3.x API

The sqlserver_validate_hash_uniq_values function.

sqlserver_validate_hash_uniq_values()

The sqlserver_validate_hash_uniq_values function.

Returns: String Returns the arguments or a message with the duplicate values.

sqlserver_validate_instance_name

Type: Ruby 3.x API

The sqlserver_validate_instance_name function.

sqlserver_validate_instance_name()

The sqlserver_validate_instance_name function.

Returns: Any Error if not a valid instance name.

sqlserver_validate_range

Type: Ruby 3.x API

The sqlserver_validate_range function.

sqlserver_validate_range()

The sqlserver_validate_range function.

Returns: Any Error if value is not between range

sqlserver_validate_size

Type: Ruby 3.x API

The sqlserver_validate_size function.

sqlserver_validate_size()

The sqlserver_validate_size function.

Returns: Any Error if not a valid size value

Tasks

get_sql_logins

Retrieve information about the logins configured for a SQL Server instance.

Supports noop? false

Parameters

instance_name

Data type: Optional[Variant[Array[String], String]]

The name of the SQL Instance running on the machine to connect to. Leave blank for the default instance of MSSQLSERVER

login_name

Data type: Optional[Variant[Array[String], String]]

The name of a particular login to search for. You can use partial names and any pattern that will work with the PowerShell '-match' operator.

exact_match

Data type: Optional[Boolean]

If set to true it will force names passed to the LoginName parameter to be an exact match to a SQL Login to pass the filter.

detailed

Data type: Optional[Boolean]

Return more detailed information from the server instead of the default summary information

get_sqlagent_jobs

Return information about SQL Agent jobs and job steps.

Supports noop? false

Parameters

instance_name

Data type: Optional[Variant[Array[String],String]]

The instance to get job information from

job_name

Data type: Optional[Variant[Array[String],String]]

The name or the pattern to match of the job to search for.

exact_match

Data type: Optional[Boolean]

Use only exact name matches for the job_name parameter instead of the default fuzzy matching.

set_sql_logins

Set IsDisabled, and Password properties of a SQL Login

Supports noop? true

Parameters

instance_name

Data type: Optional[Variant[Array[String],String]]

Instance that has the login to be modified. Leave blank for default instance.

login_name

Data type: Variant[Array[String],String]

Name of the login to modify. Matches are exact only by default.

fuzzy_match

Data type: Optional[Boolean]

Allow -match operator matches on $login_name so that inputs like 'sql' will match any login with 'sql' anywhere in the name.

enabled

Data type: Optional[Boolean]

Enable or disable an account. Set this to false to disable the account.

password

Data type: Optional[String]

The password to set for an account.

start_sql_agent_job

Start SQL Agent jobs on a server. You can start at a specified job step number (zero based indexes), and you can either wait on the job to complete, or return immediately.

Supports noop? false

Parameters

instance_name

Data type: Optional[Variant[Array[String],String]]

The instance to start a job on.

job_name

Data type: Variant[Array[String],String]

The name of the job to start.

fuzzy_match

Data type: Optional[Boolean]

Turn the job_name parameter into a pattern to match using the PowerShell -match operator.

step

Data type: Optional[Integer]

The zero based index number of the jop step to start from. Defaults to zero.

wait

Data type: Optional[Boolean]

Wait for all jobs started to complete before returning data. Defaults to false such that the task will return immediately indicating only that the job was started.
⚡