Azure SQL

This Terraform module creates an Azure SQL Server and associated databases in an optional SQL Elastic Pool with DTU purchasing model or vCore purchasing model only along with Firewall rules and Diagnostic settings enabled.

Global versioning rule for Claranet Azure modules

Module version	Terraform version	OpenTofu version	AzureRM version
>= 8.x.x	Unverified	1.8.x	>= 4.0
>= 7.x.x	1.3.x		>= 3.0
>= 6.x.x	1.x		>= 3.0
>= 5.x.x	0.15.x		>= 2.0
>= 4.x.x	0.13.x / 0.14.x		>= 2.0
>= 3.x.x	0.12.x		>= 2.0
>= 2.x.x	0.12.x		< 2.0
< 2.x.x	0.11.x		< 2.0

Contributing

If you want to contribute to this repository, feel free to use our pre-commit git hook configuration which will help you automatically update and format some files for you by enforcing our Terraform code module best-practices.

More details are available in the CONTRIBUTING.md file.

Usage

This module is optimized to work with the Claranet terraform-wrapper tool which set some terraform variables in the environment needed by this module. More details about variables set by the terraform-wrapper available in the documentation.

⚠️ Since modules version v8.0.0, we do not maintain/check anymore the compatibility with Hashicorp Terraform. Instead, we recommend to use OpenTofu.

resource "random_password" "admin_password" {
  special          = true
  override_special = "#$%&-_+{}<>:"
  upper            = true
  lower            = true
  number           = true
  length           = 32
}

# Elastic Pool
module "sql_elastic" {
  source  = "claranet/db-sql/azurerm"
  version = "x.x.x"

  client_name         = var.client_name
  environment         = var.environment
  location            = module.azure_region.location
  location_short      = module.azure_region.location_short
  stack               = var.stack
  resource_group_name = module.rg.name

  administrator_login    = "adminsqltest"
  administrator_password = random_password.admin_password.result
  create_databases_users = true

  elastic_pool_enabled  = true
  elastic_pool_max_size = "50"
  elastic_pool_sku = {
    tier     = "GeneralPurpose"
    capacity = 2
  }

  logs_destinations_ids = [
    module.logs.id,
    module.logs.storage_account_id,
  ]

  databases = [
    {
      name        = "db1"
      max_size_gb = 50
    },
    {
      name        = "db2"
      max_size_gb = 180
    }
  ]

  custom_users = [
    {
      database = "db1"
      name     = "db1_custom1"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db1"
      name     = "db1_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db2"
      name     = "db2_custom1"
      roles    = []
    },
    {
      database = "db2"
      name     = "db2_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    }
  ]
}

# Single Database
module "sql_single" {
  source  = "claranet/db-sql/azurerm"
  version = "x.x.x"

  client_name         = var.client_name
  environment         = var.environment
  location            = module.azure_region.location
  location_short      = module.azure_region.location_short
  stack               = var.stack
  resource_group_name = module.rg.name

  administrator_login    = "adminsqltest"
  administrator_password = random_password.admin_password.result
  create_databases_users = true

  elastic_pool_enabled = false

  logs_destinations_ids = [
    module.logs.id,
    module.logs.storage_account_id,
  ]

  databases = [
    {
      name        = "db1"
      max_size_gb = 50
    },
    {
      name        = "db2"
      max_size_gb = 180
    }
  ]

  custom_users = [
    {
      database = "db1"
      name     = "db1_custom1"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db1"
      name     = "db1_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db2"
      name     = "db2_custom1"
      roles    = []
    },
    {
      database = "db2"
      name     = "db2_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    }
  ]
}

Providers

Name	Version
azurecaf	~> 1.2.28
azurerm	~> 4.0

Modules

Name	Source	Version
custom_users	./modules/databases_users	n/a
databases_users	./modules/databases_users	n/a
elastic_pool_db_logging	claranet/diagnostic-settings/azurerm	~> 8.0.0
pool_logging	claranet/diagnostic-settings/azurerm	~> 8.0.0
single_db_logging	claranet/diagnostic-settings/azurerm	~> 8.0.0

Resources

Name	Type
azurerm_mssql_database.elastic_pool_database	resource
azurerm_mssql_database.single_database	resource
azurerm_mssql_database_extended_auditing_policy.elastic_pool_db	resource
azurerm_mssql_database_extended_auditing_policy.single_db	resource
azurerm_mssql_elasticpool.main	resource
azurerm_mssql_firewall_rule.main	resource
azurerm_mssql_server.main	resource
azurerm_mssql_server_extended_auditing_policy.main	resource
azurerm_mssql_server_security_alert_policy.main	resource
azurerm_mssql_server_vulnerability_assessment.main	resource
azurerm_mssql_virtual_network_rule.main	resource
azurecaf_name.sql	data source
azurecaf_name.sql_dbs	data source
azurecaf_name.sql_pool	data source

Inputs

Name	Description	Type	Default	Required
administrator_login	Administrator login for SQL Server.	string	n/a	yes
administrator_password	Administrator password for SQL Server.	string	n/a	yes
alerting_email_addresses	List of email addresses to send reports for threat detection and vulnerability assessment.	list(string)	[]	no
allowed_cidr_list	Allowed IP addresses to access the server in CIDR format. Default to all Azure services.	list(string)	[ "0.0.0.0/32" ]	no
allowed_subnets_ids	List of Subnet ID to allow to connect to the SQL Instance.	list(string)	[]	no
azuread_administrator	Azure AD Administrator configuration block of this SQL Server.	object({ login_username = optional(string) object_id = optional(string) tenant_id = optional(string) azuread_authentication_only = optional(bool) })	null	no
backup_retention	Definition of long term backup retention for all the databases in this SQL Server.	object({ weekly_retention = optional(number) monthly_retention = optional(number) yearly_retention = optional(number) week_of_year = optional(number) })	{}	no
client_name	Client name/account used in naming.	string	n/a	yes
connection_policy	The connection policy the server will use. Possible values are Default, Proxy, and Redirect.	string	"Default"	no
create_databases_users	True to create a user named _user on each database with generated password and role db_owner.	bool	true	no
custom_users	List of objects for custom users creation. Password are generated. These users are created within the "custom_users" submodule.	list(object({ name = string database = string roles = optional(list(string)) }))	[]	no
databases	List of the databases configurations for this server.	list(object({ name = string license_type = optional(string) sku_name = optional(string) max_size_gb = optional(number) create_mode = optional(string) min_capacity = optional(number) auto_pause_delay_in_minutes = optional(number) read_scale = optional(string) read_replica_count = optional(number) creation_source_database_id = optional(string) restore_point_in_time = optional(string) recover_database_id = optional(string) restore_dropped_database_id = optional(string) collation = optional(string) storage_account_type = optional(string, "Geo") database_extra_tags = optional(map(string), {}) }))	[]	no
databases_collation	SQL Collation for the databases.	string	"SQL_Latin1_General_CP1_CI_AS"	no
databases_extended_auditing_enabled	True to enable extended auditing for SQL databases.	bool	false	no
databases_extended_auditing_retention_days	Databases extended auditing logs retention.	number	30	no
databases_zone_redundant	True to have databases zone redundant, which means the replicas of the databases will be spread across multiple availability zones. This property is only settable for Premium and Business Critical databases.	bool	null	no
default_tags_enabled	Option to enable or disable default tags.	bool	true	no
diagnostic_settings_custom_name	Custom name of the diagnostics settings, name will be default if not set.	string	"default"	no
elastic_pool_custom_name	Name of the SQL Elastic Pool, generated if not set.	string	""	no
elastic_pool_databases_max_capacity	The maximum capacity (DTU or vCore) any one database can consume in the Elastic Pool. Default to the max Elastic Pool capacity.	number	null	no
elastic_pool_databases_min_capacity	The minimum capacity (DTU or vCore) all databases are guaranteed in the Elastic Pool. Defaults to 0.	number	0	no
elastic_pool_enabled	True to deploy the databases in an ElasticPool, single databases are deployed otherwise.	bool	false	no
elastic_pool_extra_tags	Extra tags to add on ElasticPool.	map(string)	{}	no
elastic_pool_license_type	Specifies the license type applied to this database. Possible values are LicenseIncluded and BasePrice.	string	null	no
elastic_pool_max_size	Maximum size of the Elastic Pool in gigabytes.	string	null	no
elastic_pool_sku	SKU for the Elastic Pool with tier and eDTUs capacity. Premium tier with zone redundancy is mandatory for high availability. Possible values for tier are GeneralPurpose, BusinessCritical for vCore models and Basic, Standard, or Premium for DTU based models. See documentation."	object({ tier = string, capacity = number, family = optional(string, "Gen5") })	null	no
elastic_pool_zone_redundant	True to have the Elastic Pool zone redundant, SKU tier must be Premium to use it. This is mandatory for high availability.	bool	false	no
environment	Project environment.	string	n/a	yes
extra_tags	Extra tags to add.	map(string)	{}	no
location	Azure location.	string	n/a	yes
location_short	Short string for Azure location.	string	n/a	yes
logs_categories	Log categories to send to destinations.	list(string)	null	no
logs_destinations_ids	List of destination resources IDs for logs diagnostic destination. Can be Storage Account, Log Analytics Workspace and Event Hub. No more than one of each can be set. If you want to use Azure EventHub as a destination, you must provide a formatted string containing both the EventHub Namespace authorization send ID and the EventHub name (name of the queue to use in the Namespace) separated by the | character.	list(string)	n/a	yes
logs_metrics_categories	Metrics categories to send to destinations.	list(string)	null	no
name_prefix	Optional prefix for the generated name.	string	""	no
name_suffix	Optional suffix for the generated name.	string	""	no
outbound_network_restriction_enabled	Whether outbound network traffic is restricted for this server.	bool	false	no
point_in_time_backup_interval_in_hours	The hours between each differential backup. This is only applicable to live databases but not dropped databases. Value has to be 12 or 24. Defaults to 12 hours.	number	12	no
point_in_time_restore_retention_days	Point In Time Restore configuration. Value has to be between 7 and 35.	number	7	no
public_network_access_enabled	True to allow public network access for this server.	bool	false	no
resource_group_name	Resource group name.	string	n/a	yes
security_storage_account_access_key	Storage Account access key used to store security logs and reports.	string	null	no
security_storage_account_blob_endpoint	Storage Account blob endpoint used to store security logs and reports.	string	null	no
security_storage_account_container_name	Storage Account container name where to store SQL Server vulnerability assessment.	string	null	no
server_custom_name	Name of the SQL Server, generated if not set.	string	""	no
server_extra_tags	Extra tags to add on SQL Server or ElasticPool.	map(string)	{}	no
server_version	Version of the SQL Server. Valid values are: 2.0 (for v11 server) and 12.0 (for v12 server). See documentation.	string	"12.0"	no
single_databases_sku_name	Specifies the name of the SKU used by the database. For example, GP_S_Gen5_2, HS_Gen4_1, BC_Gen5_2. Use only if elastic_pool_enabled variable is set to false. More documentation here	string	"GP_Gen5_2"	no
sql_server_extended_auditing_enabled	True to enable extended auditing for SQL Server.	bool	false	no
sql_server_extended_auditing_retention_days	Server extended auditing logs retention.	number	30	no
sql_server_security_alerting_enabled	True to enable security alerting for this SQL Server.	bool	false	no
sql_server_vulnerability_assessment_enabled	True to enable vulnerability assessment for this SQL Server.	bool	false	no
stack	Project stack name.	string	n/a	yes
threat_detection_policy_disabled_alerts	Specifies a list of alerts which should be disabled. Possible values include Access_Anomaly, Sql_Injection and Sql_Injection_Vulnerability.	list(string)	[]	no
threat_detection_policy_enabled	True to enable thread detection policy on the databases.	bool	false	no
threat_detection_policy_retention_days	Specifies the number of days to keep in the Threat Detection audit logs.	number	7	no
tls_minimum_version	The TLS minimum version for all SQL Database associated with the server. Valid values are: 1.0, 1.1 and 1.2.	string	"1.2"	no
use_caf_naming_for_databases	Use the Azure CAF naming provider to generate databases names.	bool	false	no

Outputs

Name	Description
administrator_login	SQL Administrator login.
administrator_password	SQL Administrator password.
custom_databases_users	Map of the custom SQL Databases users
custom_databases_users_roles	Map of the custom SQL Databases users roles
databases_id	Map of the SQL Databases names => IDs.
databases_resource	SQL Databases resource list.
default_administrator_databases_connection_strings	Map of the SQL Databases with administrator credentials connection strings
default_databases_users	Map of the SQL Databases dedicated users
elastic_pool_id	ID of the SQL Elastic Pool.
elastic_pool_resource	SQL Elastic Pool resource.
identity_principal_id	SQL Server system identity principal ID.
resource	SQL Server resource object.
security_alert_policy_id	ID of the MS SQL Server Security Alert Policy
terraform_module	Information about this Terraform module.
vulnerability_assessment_id	ID of the MS SQL Server Vulnerability Assessment.

Related documentation

Microsoft Azure root documentation: docs.microsoft.com/en-us/azure/sql-database/