Azure Sql Overlay Terraform Module

This Overlay terraform module can create a 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 manage related parameters (Private Endpoints, etc.) to be used in a SCCA compliant Network.

SCCA Compliance

This module can be SCCA compliant and can be used in a SCCA compliant Network. Enable private endpoints and SCCA compliant network rules to make it SCCA compliant.

For more information, please read the SCCA documentation.

Contributing

If you want to contribute to this repository, feel free to to contribute to our Terraform module.

More details are available in the CONTRIBUTING.md file.

Resources Used

• Azure SQL Server
• Azure SQL Database
• Azure SQL Elastic Pool
• Azure SQL Firewall Rule
• Private Endpoints
• Private DNS zone for privatelink A records
• Azure Resource Locks

Limitations

• none

Overlay Module Usage for basic sql server

# Azurerm Provider configuration
provider "azurerm" {
  features {}
}

#---------------------------------------------------------
# Azure Region Lookup
#----------------------------------------------------------
module "mod_azure_region_lookup" {
  source  = "azurenoops/overlays-azregions-lookup/azurerm"
  version = "~> 1.0.0"

  azure_region = "eastus"
}


module "acr" {
  depends_on = [
    azurerm_virtual_network.vnet
  ]
  source  = ""azurenoops/overlays-container-registry/azurerm"
  version = "x.x.x"

  # By default, this module will not create a resource group. If you wish 
  # to use an existing resource group, provide the name of the existing resource group.
  # using `existing_resource_group_name` will ignore `create_container_registry_resource_group` and `custom_resource_group_name`.
  # The location of the group  will remain the same if you use the current resource.
  create_container_registry_resource_group = true
  location                                 = module.mod_azure_region_lookup.location_cli
  environment                              = "public"
  deploy_environment                       = "dev"
  org_name                                 = "anoa"
  workload_name                            = "dev-acr"
  sku                                      = "Standard"

  # Tags for Azure Resources
  add_tags = {
    example = "basic_container_registry"
  }
}

Requirements

Name	Version
terraform	>= 1.3
azurenoopsutils	~> 1.0
azurerm	~> 3.22
mssql	>= 0.2.5

Providers

Name	Version
azurenoopsutils	~> 1.0
azurerm	~> 3.22
random	n/a

Modules

Name	Source	Version
custom_users	./modules/sql_db_users	n/a
databases_users	./modules/sql_db_users	n/a
mod_azure_region_lookup	azurenoops/overlays-azregions-lookup/azurerm	~> 1.0.0
mod_sql_rg	azurenoops/overlays-resource-group/azurerm	~> 1.0.1

Resources

Name	Type
azurerm_management_lock.primary_sql_level_lock	resource
azurerm_management_lock.secondary_sql_level_lock	resource
azurerm_mdsql_firewall_rule.fw02	resource
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.elastic_pool	resource
azurerm_mssql_firewall_rule.fw01	resource
azurerm_mssql_server.primary_sql	resource
azurerm_mssql_server.secondary_sql	resource
azurerm_mssql_server_extended_auditing_policy.primary	resource
azurerm_mssql_server_extended_auditing_policy.secondary	resource
azurerm_mssql_server_security_alert_policy.sap_primary	resource
azurerm_mssql_server_security_alert_policy.sap_secondary	resource
azurerm_mssql_server_vulnerability_assessment.va_primary	resource
azurerm_mssql_server_vulnerability_assessment.va_secondary	resource
azurerm_mssql_virtual_network_rule.vnet_rule_primary	resource
azurerm_mssql_virtual_network_rule.vnet_rule_secondary	resource
azurerm_private_dns_a_record.a_rec	resource
azurerm_private_dns_zone.dns_zone	resource
azurerm_private_dns_zone_virtual_network_link.vnet_link	resource
azurerm_private_endpoint.pep	resource
azurerm_sql_active_directory_administrator.ad_user1	resource
azurerm_sql_active_directory_administrator.ad_user2	resource
azurerm_sql_failover_group.fog	resource
azurerm_storage_account.storeacc	resource
azurerm_storage_container.storcont	resource
azurerm_subnet.snet_ep	resource
random_password.main	resource
random_string.str	resource
azurenoopsutils_resource_name.primary_sql	data source
azurenoopsutils_resource_name.secondary_sql	data source
azurenoopsutils_resource_name.sql_dbs	data source
azurenoopsutils_resource_name.sql_pool	data source
azurenoopsutils_resource_name.sql_storage	data source
azurerm_client_config.current	data source
azurerm_private_endpoint_connection.pip	data source
azurerm_resource_group.rgrp	data source
azurerm_virtual_network.vnet	data source

Inputs

Name	Description	Type	Default	Required
ad_admin_login_name	The login name of the principal to set as the server administrator	any	null	no
add_tags	Map of custom tags.	map(string)	{}	no
administrator_login	Administrator login for SQL Server	string	n/a	yes
administrator_password	Administrator password for SQL Server	string	null	no
alerting_email_addresses	List of email addresses to send reports for threat detection and vulnerability assesment	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
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
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
create_sql_resource_group	Controls if the resource group should be created. If set to false, the resource group name must be provided. Default is false.	bool	false	no
custom_resource_group_name	The name of the custom resource group to create. If not set, the name will be generated using the org_name, workload_name, deploy_environment and environment variables.	string	null	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) max_size_gb = 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) storage_account_type = optional(string, "Geo") database_add_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
deploy_environment	The environment to deploy. It defaults to dev.	string	"dev"	no
disabled_alerts	Specifies an array of alerts that are disabled. Allowed values are: Sql_Injection, Sql_Injection_Vulnerability, Access_Anomaly, Data_Exfiltration, Unsafe_Action.	list(any)	[]	no
elastic_pool_add_tags	Extra tags to add on ElasticPool	map(string)	{}	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_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 https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-elastic-pools"	object({ tier = string, capacity = number, })	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
email_addresses_for_alerts	List of email addresses to send reports for threat detection and vulnerability assesment	list(string)	[]	no
enable_database_extended_auditing_policy	Manages Extended Audit policy for SQL database	bool	false	no
enable_elastic_pool	True to deploy the databases in an ElasticPool, single databases are deployed otherwise.	bool	false	no
enable_failover_group	Create a failover group of databases on a collection of Azure SQL servers	bool	false	no
enable_firewall_rules	Manage an Azure SQL Firewall Rule	bool	false	no
enable_identity	If you want your SQL Server to have an managed identity. Defaults to false.	bool	false	no
enable_log_monitoring	Enable audit events to Azure Monitor?	bool	false	no
enable_private_endpoint	Manages a Private Endpoint to Azure Container Registry. Default is false.	bool	false	no
enable_resource_locks	(Optional) Enable resource locks	bool	false	no
enable_sql_vulnerability_assessment	True to enable vulnerability assessment for this SQL Server	bool	false	no
enable_threat_detection_policy	True to enable thread detection policy on the databases	bool	false	no
environment	The Terraform backend environment e.g. public or usgovernment	string	null	no
existing_private_dns_zone	Name of the existing private DNS zone	any	null	no
existing_resource_group_name	The name of the existing resource group to use. If not set, the name will be generated using the org_name, workload_name, deploy_environment and environment variables.	string	null	no
existing_subnet_id	The resource id of existing subnet	any	null	no
existing_vnet_id	ID of the existing virtual network for the private endpoint	any	null	no
firewall_rules	Range of IP addresses to allow firewall connections.	list(object({ name = string start_ip_address = string end_ip_address = string }))	[]	no
location	The location/region to keep all your network resources. To get the list of all locations with table format from azure cli, run 'az account list-locations -o table'	string	n/a	yes
lock_level	(Optional) id locks are enabled, Specifies the Level to be used for this Lock.	string	"CanNotDelete"	no
log_analytics_workspace_id	Specifies the ID of a Log Analytics Workspace where Diagnostics Data to be sent	any	null	no
name_prefix	Optional prefix for the generated name	string	""	no
name_suffix	Optional suffix for the generated name	string	""	no
org_name	A name for the organization. It defaults to anoa.	string	"anoa"	no
outbound_network_restriction_enabled	Whether outbound network traffic is restricted for this server	bool	false	no
point_in_time_restore_retention_days	Point In Time Restore configuration. Value has to be between 7 and 35	number	7	no
private_subnet_address_prefix	The name of the subnet for private endpoints	any	null	no
public_network_access_enabled	True to allow public network access for this server	bool	false	no
random_password_length	The desired length of random password created by this module	number	32	no
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 vulneralibility assessment	string	null	no
server_add_tags	Extra tags to add on SQL Server or ElasticPool	map(string)	{}	no
server_custom_name	Name of the SQL Server, generated if not set.	string	""	no
server_version	Version of the SQL Server. Valid values are: 2.0 (for v11 server) and 12.0 (for v12 server). See https://www.terraform.io/docs/providers/azurerm/r/sql_server.html#version	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 enable_elastic_pool variable is set to false. More documentation here	string	"BC_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
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_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_location_short_name	Use short location name for resources naming (ie eastus -> eus). Default is true. If set to false, the full cli location name will be used. if custom naming is set, this variable will be ignored.	bool	true	no
use_naming	Use the Azure NoOps naming provider to generate default resource name. custom_name override this if set. Legacy default name is used if this is set to false.	bool	true	no
use_naming_for_databases	Use the Azure NoOps naming provider to generate databases names.	bool	false	no
virtual_network_name	Name of the virtual network for the private endpoint	any	null	no
workload_name	A name for the workload. It defaults to acr.	string	"acr"	no

Outputs

Name	Description
custom_databases_users	Map of the custom SQL Databases users
custom_databases_users_roles	Map of the custom SQL Databases users roles
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
identity	Identity block with principal ID and tenant ID used for this SQL Server
primary_sql_server_fqdn	The fully qualified domain name of the primary Azure SQL Server
primary_sql_server_id	The primary Microsoft SQL Server ID
primary_sql_server_private_endpoint	id of the Primary SQL server Private Endpoint
primary_sql_server_private_endpoint_fqdn	Priamary SQL server private endpoint IPv4 Addresses
primary_sql_server_private_endpoint_ip	Priamary SQL server private endpoint IPv4 Addresses
resource_group_location	The location of the resource group in which resources are created
resource_group_name	The name of the resource group in which resources are created
secondary_sql_server_fqdn	The fully qualified domain name of the secondary Azure SQL Server
secondary_sql_server_id	The secondary Microsoft SQL Server ID
sql_administrator_login	SQL Administrator login
sql_administrator_password	SQL Administrator password
sql_databases	SQL Databases
sql_databases_id	Map of the SQL Databases IDs
sql_elastic_pool	SQL Elastic Pool
sql_elastic_pool_id	ID of the SQL Elastic Pool
sql_failover_group_id	A failover group of databases on a collection of Azure SQL servers.
sql_server_private_dns_zone_domain	DNS zone name of SQL server Private endpoints dns name records