# Reference ## Table of Contents ### Defined types * [`sqlserver::config`](#sqlserver--config): Define Resource Type: sqlserver::config * [`sqlserver::database`](#sqlserver--database): Define Resource Type: sqlserver::database * [`sqlserver::login`](#sqlserver--login): Define Resource Type: sqlserver::login * [`sqlserver::login::permissions`](#sqlserver--login--permissions): Define Resource Type: sqlserver::login::permissions# * [`sqlserver::role`](#sqlserver--role): Define Resource Type: sqlserver::role::permissions * [`sqlserver::role::permissions`](#sqlserver--role--permissions): Define Resource Type: sqlserver::role::permissions * [`sqlserver::sp_configure`](#sqlserver--sp_configure): Defined Resource Type: sqlserver::sp_configure * [`sqlserver::user`](#sqlserver--user): Define Resource Type: sqlserver::user * [`sqlserver::user::permissions`](#sqlserver--user--permissions): Define Resource Type: sqlserver::user::permissions ### Resource types * [`sqlserver_features`](#sqlserver_features): Due to our prefetch and unaware of what name the user will provide we munge the value to meet our expecations. * [`sqlserver_instance`](#sqlserver_instance): Namevar * [`sqlserver_tsql`](#sqlserver_tsql): Namevar ### Functions * [`sqlserver::partial_params_args`](#sqlserver--partial_params_args): this function populates and returns the string of arguments which later gets injected in template. * [`sqlserver_is_domain_or_local_user`](#sqlserver_is_domain_or_local_user) * [`sqlserver_upcase`](#sqlserver_upcase) * [`sqlserver_validate_hash_uniq_values`](#sqlserver_validate_hash_uniq_values) * [`sqlserver_validate_instance_name`](#sqlserver_validate_instance_name) * [`sqlserver_validate_range`](#sqlserver_validate_range) * [`sqlserver_validate_size`](#sqlserver_validate_size) ### Tasks * [`get_sql_logins`](#get_sql_logins): Retrieve information about the logins configured for a SQL Server instance. * [`get_sqlagent_jobs`](#get_sqlagent_jobs): Return information about SQL Agent jobs and job steps. * [`set_sql_logins`](#set_sql_logins): Set IsDisabled, and Password properties of a SQL Login * [`start_sql_agent_job`](#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 ##### ```puppet sqlserver::config{'MSSQLSERVER': admin_user => 'sa', admin_pass => 'PuppetP@ssword1', } ``` #### Parameters The following parameters are available in the `sqlserver::config` defined type: * [`instance_name`](#-sqlserver--config--instance_name) * [`admin_user`](#-sqlserver--config--admin_user) * [`admin_pass`](#-sqlserver--config--admin_pass) * [`admin_login_type`](#-sqlserver--config--admin_login_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 * **See also** * http://msdn.microsoft.com/en-us/library/ff929071.aspx * Contained Databases * http://msdn.microsoft.com/en-us/library/ms176061.aspx * CREATE DATABASE TSQL * http://msdn.microsoft.com/en-us/library/ms174269.aspx * ALTER DATABASE TSQL * http://msdn.microsoft.com/en-us/library/ms190303.aspx * System Languages #### Parameters The following parameters are available in the `sqlserver::database` defined type: * [`db_name`](#-sqlserver--database--db_name) * [`instance`](#-sqlserver--database--instance) * [`ensure`](#-sqlserver--database--ensure) * [`compatibility`](#-sqlserver--database--compatibility) * [`collation_name`](#-sqlserver--database--collation_name) * [`filestream_non_transacted_access`](#-sqlserver--database--filestream_non_transacted_access) * [`filestream_directory_name`](#-sqlserver--database--filestream_directory_name) * [`filespec_name`](#-sqlserver--database--filespec_name) * [`filespec_filename`](#-sqlserver--database--filespec_filename) * [`filespec_size`](#-sqlserver--database--filespec_size) * [`filespec_maxsize`](#-sqlserver--database--filespec_maxsize) * [`filespec_filegrowth`](#-sqlserver--database--filespec_filegrowth) * [`log_name`](#-sqlserver--database--log_name) * [`log_filename`](#-sqlserver--database--log_filename) * [`log_size`](#-sqlserver--database--log_size) * [`log_maxsize`](#-sqlserver--database--log_maxsize) * [`log_filegrowth`](#-sqlserver--database--log_filegrowth) * [`containment`](#-sqlserver--database--containment) * [`default_fulltext_language`](#-sqlserver--database--default_fulltext_language) * [`default_language`](#-sqlserver--database--default_language) * [`nested_triggers`](#-sqlserver--database--nested_triggers) * [`transform_noise_words`](#-sqlserver--database--transform_noise_words) * [`two_digit_year_cutoff`](#-sqlserver--database--two_digit_year_cutoff) * [`db_chaining`](#-sqlserver--database--db_chaining) * [`trustworthy`](#-sqlserver--database--trustworthy) ##### `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 | 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 * **See also** * Puppet::Parser::Fucntions#sqlserver_validate_instance_name * http://msdn.microsoft.com/en-us/library/ms186320(v=sql.110).aspx * Server Role Members * http://technet.microsoft.com/en-us/library/ms189751(v=sql.110).aspx * Create Login * http://technet.microsoft.com/en-us/library/ms189828(v=sql.110).aspx * Alter Login #### Parameters The following parameters are available in the `sqlserver::login` defined type: * [`login`](#-sqlserver--login--login) * [`instance`](#-sqlserver--login--instance) * [`ensure`](#-sqlserver--login--ensure) * [`password`](#-sqlserver--login--password) * [`svrroles`](#-sqlserver--login--svrroles) * [`login_type`](#-sqlserver--login--login_type) * [`default_database`](#-sqlserver--login--default_database) * [`default_language`](#-sqlserver--login--default_language) * [`check_expiration`](#-sqlserver--login--check_expiration) * [`check_policy`](#-sqlserver--login--check_policy) * [`disabled`](#-sqlserver--login--disabled) * [`permissions`](#-sqlserver--login--permissions) ##### `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`](#-sqlserver--login--permissions--login) * [`permissions`](#-sqlserver--login--permissions--permissions) * [`state`](#-sqlserver--login--permissions--state) * [`instance`](#-sqlserver--login--permissions--instance) * [`with_grant_option`](#-sqlserver--login--permissions--with_grant_option) ##### `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`](#-sqlserver--role--ensure) * [`role`](#-sqlserver--role--role) * [`instance`](#-sqlserver--role--instance) * [`authorization`](#-sqlserver--role--authorization) * [`type`](#-sqlserver--role--type) * [`database`](#-sqlserver--role--database) * [`permissions`](#-sqlserver--role--permissions) * [`members`](#-sqlserver--role--members) * [`members_purge`](#-sqlserver--role--members_purge) ##### `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`](#-sqlserver--role--permissions--role) * [`permissions`](#-sqlserver--role--permissions--permissions) * [`state`](#-sqlserver--role--permissions--state) * [`with_grant_option`](#-sqlserver--role--permissions--with_grant_option) * [`type`](#-sqlserver--role--permissions--type) * [`database`](#-sqlserver--role--permissions--database) * [`instance`](#-sqlserver--role--permissions--instance) ##### `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`](#-sqlserver--sp_configure--config_name) * [`value`](#-sqlserver--sp_configure--value) * [`instance`](#-sqlserver--sp_configure--instance) * [`reconfigure`](#-sqlserver--sp_configure--reconfigure) * [`with_override`](#-sqlserver--sp_configure--with_override) * [`restart`](#-sqlserver--sp_configure--restart) ##### `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 ##### ```puppet sqlserver::user{'myUser': database => 'loggingDatabase', login => 'myUser', } ``` #### Parameters The following parameters are available in the `sqlserver::user` defined type: * [`user`](#-sqlserver--user--user) * [`database`](#-sqlserver--user--database) * [`ensure`](#-sqlserver--user--ensure) * [`default_schema`](#-sqlserver--user--default_schema) * [`instance`](#-sqlserver--user--instance) * [`login`](#-sqlserver--user--login) * [`password`](#-sqlserver--user--password) * [`permissions`](#-sqlserver--user--permissions) ##### `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`](#-sqlserver--user--permissions--user) * [`database`](#-sqlserver--user--permissions--database) * [`permissions`](#-sqlserver--user--permissions--permissions) * [`state`](#-sqlserver--user--permissions--state) * [`with_grant_option`](#-sqlserver--user--permissions--with_grant_option) * [`instance`](#-sqlserver--user--permissions--instance) ##### `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`](#-sqlserver_features--install_switches) * [`is_svc_account`](#-sqlserver_features--is_svc_account) * [`is_svc_password`](#-sqlserver_features--is_svc_password) * [`name`](#-sqlserver_features--name) * [`pid`](#-sqlserver_features--pid) * [`provider`](#-sqlserver_features--provider) * [`source`](#-sqlserver_features--source) * [`windows_feature_source`](#-sqlserver_features--windows_feature_source) ##### `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`](#-sqlserver_instance--agt_svc_account) * [`agt_svc_password`](#-sqlserver_instance--agt_svc_password) * [`as_svc_account`](#-sqlserver_instance--as_svc_account) * [`as_svc_password`](#-sqlserver_instance--as_svc_password) * [`as_sysadmin_accounts`](#-sqlserver_instance--as_sysadmin_accounts) * [`install_switches`](#-sqlserver_instance--install_switches) * [`name`](#-sqlserver_instance--name) * [`pid`](#-sqlserver_instance--pid) * [`polybase_svc_account`](#-sqlserver_instance--polybase_svc_account) * [`polybase_svc_password`](#-sqlserver_instance--polybase_svc_password) * [`provider`](#-sqlserver_instance--provider) * [`rs_svc_account`](#-sqlserver_instance--rs_svc_account) * [`rs_svc_password`](#-sqlserver_instance--rs_svc_password) * [`sa_pwd`](#-sqlserver_instance--sa_pwd) * [`security_mode`](#-sqlserver_instance--security_mode) * [`source`](#-sqlserver_instance--source) * [`sql_svc_account`](#-sqlserver_instance--sql_svc_account) * [`sql_svc_password`](#-sqlserver_instance--sql_svc_password) * [`sql_sysadmin_accounts`](#-sqlserver_instance--sql_sysadmin_accounts) * [`windows_feature_source`](#-sqlserver_instance--windows_feature_source) ##### `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`](#-sqlserver_tsql--command) * [`database`](#-sqlserver_tsql--database) * [`instance`](#-sqlserver_tsql--instance) * [`name`](#-sqlserver_tsql--name) * [`provider`](#-sqlserver_tsql--provider) ##### `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.