Features - SQLDSC

This section walks through building up your desired state configuration. Please see the documentation for everything SQLDSC can do.

Getting Started

resource "database" "model" {
    recovery_model = "simple"
}
resource "login" "sa" {
    disabled = true
}
resource "configuration" "xp_cmdshell" {
	value = 0
}

Setting the model database to simple recovery ensures that new databases are in SIMPLE recovery unless you explicitly change them to FULL recovery
Disabling the sa login and xp_cmdshell are common practices to securely configure SQL Server
The configuration resource can set any sp_configure setting

Deploy SysAdmin Group

resource "login" "DOMAIN\Admin-Group" {}
resource "server_role_member" "admin-sysadmin" {
    login = "DOMAIN\Admin-Group"
    role = "sysadmin"
}

Often groups of SQL Servers have a domain group that is granted sysadmin rights
This creates the login if it doesn’t exist and adds it to the sysadmin role if it isn’t already a member
The PRO version provides a Login Block that simplifies this.

resource "login" "sqlmonitor" {
    sqldsc_credential = "sqlmonitor"
}
resource "server_permission" "sqlmon-view-state" {
    login = "sqlmonitor"
    permissions = ["view server state"]
}

Often groups of SQL Servers have a common domain group that is granted sysadmin rights
This creates the login if it doesn’t exist and adds it to the sysadmin role if it isn’t already a member
A password is securely stored in a local password vault and referenced with the sqldsc_credential attribute
The login is created with a consistent SID across different servers
The PRO version provides a Login Block that simplifies this.

A Service Account

login "DOMAIN\AppSvc" {
    database "AppDB" {
        roles = ["db_datawriter", "db_datareader"]
        permissions = "EXEC"
    }
}

Accounts for services often need certain permissions in specific databases.
The PRO version provides a login block to simplify this.
This creates the login and a database user with the appropriate permissions

Please see the documentation site for everything SQLDSC can do