View in Browser
Extension to enable right click on an HTML file and open it in a browser.sqldsc.exe
is located. Pressing control-` (back tick) will bring up a command prompt in VSCode.sqldsc init
will create the config
directory and a sample defaults.hcl
, targets.hcl
, and a file for the current computer. Please review these and edit if needed.sqldsc test
will compare the configuration (in defaults.hcl
and a file named after the current computer) to the target servers (in targets.hcl
) and list any required changes.sqldsc apply
will apply the changes. It tests the configuration and then prompts for a confirmation.sqldsc help
will list the parameterssqldsc command [-path _path_]
init
- creates directories and sample filestest
- see what changes it would makeapply
- make changes to the targetsversion
- display the version informationvault
- controls stored logins and passwords. see storing credentialsconfig
--target target_name
- only process one target. This matches the Name OR the FQDN. Applies to test
and apply
--push file_name
- run the file regardless of whether it needs to be run. The file must be identified in a sql_file
resource block (see below).By default, the configuration files are stored in the config
directory.
config\
nodes\
ServerA.hcl
ServerA__instance1.hcl
defaults.hcl
targets.hcl
targets.hcl
lists the targets that will be configureddefaults.hcl
will be applied to all targets.nodes\ServerA.hcl
will override the defaults for all instances on ServerA
.nodes\ServerA__Instance1.hcl
will override the defaults for ServerA\Instance1
. Please note the double underscore between the host name and instance name.A sample targets.hcl
looks like this:
target "D40\SQL2014" {}
target "server.domain.com" {}
The text in quotes is what it will try to connect to. The full layout of a targets file might look like this:
target_defaults {
dial_timeout = 10
connect_timeout = 15
}
target "my-target" {
fqdn = "host:port"
dial_timeout = 90
}
target_defaults
block sets the defaults for all targets.dial_timeout
is the timeout to resolve the name and talk to the port. It is in seconds and defaults to 15 seconds.connect_timeout
is the timeout to login. It defaults to 0 which is no timeout and is measured in seconds.fqdn
is specified, the name in quotes is used (“my-target”).Resource blocks have the following format. The resource_name
isn’t currently used except to identify the blocks in error messages.
resource "resource_type" "resource_name" {
ensure = "present"
... other key value entries ...
}
This is an example Resource Block that creates a SQL Server agent operator:
resource "agent_operator" "june" {
ensure = "present"
name = "june"
email_address = "june@test.com"
}
A fake sample showing all the data types:
resource "fake_resource" "fake" {
ensure = "present"
string_val = "value"
int_val = 37
bool_val = true
}
Please see the SQLDSC_Schema.html document.
SQLSDC provides a simple vault to store credentials securely using the Windows DPAPI. These are stored in a JSON file in %LOCALAPPDATA%\sqldsc\.vault.json
. This directory is specific to each user. You can see the credential name and login name in clear text but the password is encrypted. The login name isn’t used in the current release.
The DPAPI encrypts with a user key and a machine key. That means the file can’t be shared across users or machines.
The following commands are available
vault list
- list the stored credentialsvault test sqldsc_credential
- prompt for a password and test if that is the password stored for sqldsc_credential
vault save sqldsc_credential
- prompt for a password and write that credential and password into the credential store. It will overwrite existing credentials.The most common use case is creating identical logins across multiple servers with the same password. Examples include monitoring tools that require SQL Server logins or Availability Groups that need identical logins.
In the login
resource, you can specify the sqldsc_credential
and it will use the password from that credential for the login.
sqldsc info
prints a summary table that lists the target files and target counts and the resource files and resource countsexpected_rows
to the sql_script
resource. If the test_query
returns a different number of rows than this, the script will be run.target
for dial timeout and connect timeout.target_defaults
block to set defaults for all targets in the file.object_permission
resource to set permissions on database objectssql_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.ensure
attribute is found, default to present
.name
attribute is found, default to the Resouce Name.name
attribute is found, default to the Resouce Name.name
attribute is found, default to the Resouce Name.name
attribute is found, default to the Resouce Name.database_permission
resource blockssql_file
resource, the file name is just file
instead of file_name
database_role_member
resource blocksvault
to store encrypted passwords locallylogin
resource including the SID, expiration check, policy check, and default database.sql_file
resource to create common procedures or objects on serversdatabase_user
resourcelogs
directoryserver_role_member
resourcedefaults.hcl
The following objects are deprecated
database_permission
– this is just too broad to work properly. It needs to be split out. It still works but will be removed once new resources are available.
Summary: GRANT or REVOKE a GRANT of a database, schema, or object permission depending on what is populated. The permission is assumed to be a GRANT if ensure
is “present”. It will revoke a GRANT if ensure
is “absent”. It won’t DENY and it shouldn’t REVOKE a DENY.
database
- (string - required)schema
- (string - optional)object
- (string optional)permission
- (string - required)grantee
- (string - required) The user that is granted the permission. Domain accounts hould be entered in DOMAIN\User format© 2020 ScaleOut Consulting, LLC