BREAKING CHANGE: How roles are processed has changed. Please see PRO: Roles.
Roles now support nesting. This allows a role to include additional roles. For example, an Availibility Group role can include all the needed roles rather than defining them for each node.
login blocks allow a default_schema to be set for database and user_databases blocks
sqldsc list lists all the targets and their roles after applying filters
Early support for case-sensitive servers and databases
The application logging format has changed to make it easier to read.
Targets support SQL Server logins.
Targets can define roles which override the default Roles or added_roles which add to the default Roles.
Both test and apply stop processing on errors. Originally they would stop the current target but process other targets. However the safest action is to stop immediately if there’s a problem. If the test is running multi-threaded, each thread will finish its target but won’t start new a one.
Testing a password in the Vault can test both passwords and password hashes.
Added a --parallel flag for the PRO. This sets the number of testing threads equal to the number of targets. Applying is always done sequentially.
The application checks if a user is in the sysadmin role. If they are not, the target is skipped. This better supports running with two different accounts.
The PRO edition can configure the multi-threaded settings using a settings file. Please see the Application Settings section.
The application can update itself by running sqldsc update. If there is a newer version, it will prompt you to install it.
sqldsc init will create a .vscode directory with the suggested extensions.
Improved handling of SQL Server logins. See SQL Server Logins for details.
Capture and reuse SIDs
Store hashed passwords in the Credential Vault
Get the SID and hashed password from an existing login
If a database isn’t writeable, any changes for that database are silently skipped.
sqldsc info prints a summary table that lists the target files and target counts and the resource files and resource counts
1.24.1 (26 April 2020)
Logging is now is written to individual files in a dir tree: YYYY/sqldsc_YYYY_01_Domain_User.log. This should prevent duplicates and eliminate the need to merge files.
1.23 (24 April 2020)
Cleaned up logging. There are now three levels that provide increased detail: Verbose, then Debug, then Trace. Verbose is really just printing the SQL statements.
Added expected_rows to the sql_script resource. If the test_query returns a different number of rows than this, the script will be run.
Added properties for target for dial timeout and connect timeout.
Added a target_defaults block to set defaults for all targets in the file.
1.22 (22 April 2020)
Resource Blocks now overwrite previous definitions. Prior to this they would be merged. It’s just too complicated to figure out where different settings come from in files. For example, assume you define a database resource for a “DBA” database in defaults.hcl. Then in a node-specific configuration file, you define a “DBA” database with a different set of properties. The later more specific resource will now completely overwrite the newer resource. That newer resource will then be applied to the database.
1.21 (19 April 2020)
Assort package updates and bug fixes
1.20.1 (17 April 2020)
Added an object_permission resource to set permissions on database objects
1.18 (12 April 2020)
The sql_file resource has been renamed to sql_script. Please rename the sql directory directory to scripts. The attribute names have also changed. Please see the schema document.
If no ensure attribute is found, default to present.
For logins, if no name attribute is found, default to the Resouce Name.
For agent operators, if no name attribute is found, default to the Resouce Name.
For databases, if no name attribute is found, default to the Resouce Name.
For configurations, if no name attribute is found, default to the Resouce Name.
1.17 (9 April 2020)
Add database_permission resource blocks
Fix messages on existing user when changing database owner
Generate error if the same resource is defined twice in a single file
Moved the Resource Block schema to a separate document
In the sql_file resource, the file name is just file instead of file_name
1.16 (4 April 2020)
Add database_role_member
Clean up attribute names so that a “_name” suffix isn’t requried for SQL Server objects such as logins and roles
If you experience connectivity issues, please contact me
1.0.15 (24 March 2020)
Added vault to store encrypted passwords locally
Added attributes to the login resource including the SID, expiration check, policy check, and default database.
Added a sql_file resource to create common procedures or objects on servers
Added a database_user resource
Added a syntax check for .hcl files at the start of processing
Switched SQL Server drivers. If you experience connectivity issues, please contact me
1.0.14 (15 March 2020)
Moved the audit logs to a logs directory
Allow single backslashes in server and login names
A Test is run before Applying unless you specify the –confirm flag