Skip to content

Microsoft SQL Role Usage Guide

Requirements

  1. Outbound access to cdn.powershellgallery.com and www.powershellgallery.com to download required PowerShell modules. If outbound access is not available, the required modules can be manually installed. The required modules are:
    • SqlServer
    • SqlServerDsc
    • ServerManager
    • StorageDsc
    • dbatools
    • xNetworking

Prerequisites

  1. Storage must be provisioned on the host(s) Microsoft SQL will be installed on. This can be done using the storage roles. If ephemeral storage is being used for tempdb, it must be manually configured using AWS/Azure best practices.
  2. Required AD accounts (e.g. GMSAs) must be created. The create-gmsa.yml playbook can be used to create GMSAs in Active Directory.
  3. Either a customer provided ISO or a copy of Microsoft SQL Server Developer Edition (e.g. SQLServer2022-x64-ENU-Dev.iso).
  4. The following variables must be defined. Use a secure password generator to create strong passwords. The variables should be all uppercase when defined as environment variables, but all lowercase when defined in host_vars or group_vars files or an ansible vault.
# REQUIRED VARIABLES
export MICROSOFT_SQL_SPADMIN_PASSWORD='USE_PASSWORD_GENERATOR'
export MICROSOFT_SQL_SPUSER_PASSWORD='USE_PASSWORD_GENERATOR'
export MICROSOFT_SQL_SA_PASSWORD='USE_PASSWORD_GENERATOR'

# OPTIONAL VARIABLES
export MICROSOFT_SQL_LICENSE_KEY='XXXXX-XXXXX-XXXXX-XXXXX-XXXXX'

# REQUIRED VARIABLES
microsoft_sql_spadmin_password: 'USE_PASSWORD_GENERATOR'
microsoft_sql_spuser_password: 'USE_PASSWORD_GENERATOR'
microsoft_sql_sa_password: 'USE_PASSWORD_GENERATOR'

# OPTIONAL VARIABLES
microsoft_sql_license_key: 'XXXXX-XXXXX-XXXXX-XXXXX-XXXXX'

Usage

  1. Create a file in host_vars for each host Microsoft SQL will be installed on if it doesn't already exist. The file should be named after the host's inventory name (e.g. host_vars/abcmsql01.yml).
  2. A SQL ISO is required to install Microsoft SQL Server. The playbook supports delivering it via HTTP(S) (e.g. storage account, S3 bucket, caddy file server, etc.) or by uploading it from the Ansible control node.
    • When uploading files from the Ansible control node, place the ISO in a "shared files" directory on the ansible control node (usually a location on the container's persistent storage in /home/ansible/source/files) and set shared_files_directory in group_vars to the path of the directory. If the microsoft_sql_install_source_dir variable is defined in host_vars, it will override shared_files_directory for that host.
    • If the value of shared_files_directory or microsoft_sql_install_source_dir is a URL, the playbook will attempt to download the ISO from that location. If it's a local file path, the playbook will attempt to upload the ISO from the Ansible control node. microsoft_sql_install_source_file should be set to the name of the ISO file (e.g. 'SQLServer2019.iso').

  3. Update each hosts host_vars with the necessary variables using the sample variables below or navigate to templates to see more examples.

    SQL Template Variables

    If you are deploying a multipurpose SQL server for Kuiper/System Pulse, ensure variables in the template are configured to grant database permissions to both the user account running the Kuiper installation Ansible role and the Kuiper GMSA.

  4. Run the playbooks/deploy-microsoft-sql.yml playbook. This playbook calls the microsoft-sql role to install Microsoft SQL and dependencies, configure databases, users, permissions, firewall rules, etc. It also contains some tasks specific to Kuiper/System Pulse deployments. 

    ansible-playbook --limit=app_msql playbooks/deploy-microsoft-sql.yml

Sample Variables

SQL Template Variables

The sample below is for a Kuiper/System Pulse multipurpose SQL Deployment. Be sure to customize environment specific variables in the template. Many of the lines containing environment specific values are highlighted in the code block below. See comments for more information.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# These are sample storage variables used by the storage provisioning roles. They're included here to show how the storage provisioning roles can be used to set up the storage, but are not required to be defined in the SQL playbook variables if the storage is already set up or will be set up separately.

volumes:
  disk1:
    DriveLetter: G
    FSLabel: Data
    AllocationUnitSize: 65536
  disk2:
    DriveLetter: H
    FSLabel: Log
    AllocationUnitSize: 65536
  disk3:
    DriveLetter: S
    FSLabel: SQL_Server
  # TEMPDB STORAGE IS NOT SHOWN HERE BECAUSE IT'S RECOMMENDED TO USE EPHEMERAL STORAGE

microsoft_sql_install_source_file: SQLServer2022-x64-ENU-Dev.iso

# MAKE SURE DRIVE LETTERS IN THE STORAGE VARIABLES MATCH THE DRIVE LETTERS IN THE SQL VARIABLES BELOW
microsoft_sql_dsc_sqltempdbdir: 'T:\TempDB'    # RECOMMENDED TO USE EPHEMERAL
microsoft_sql_dsc_sqltempdblogdir: 'T:\TempDB' # STORAGE FOR TEMPDB
microsoft_sql_dsc_installsqldatadir: 'S:\SQL Server Data'
microsoft_sql_dsc_instancename: MSSQLSERVER
microsoft_sql_dsc_sqlbackupdir: 'S:\SQL Server Backups'
microsoft_sql_dsc_sqluserdbdir: 'G:\Data'
microsoft_sql_dsc_sqluserdblogdir: 'H:\Log'
microsoft_sql_dsc_sqlsysadminaccounts: # THE SQL SETUP REQUIRES AT LEAST ONE SYSTEM ADMINISTRATOR ACCOUNT
  - sapphire\epicadmin

# OPTIONALLY OVERRIDE THE SOURCE AND VERSION OF SQL SERVER MANAGEMENT STUDIO TO INSTALL
# smss_install_source_dir: https://aka.ms/ssms/22/release
# smss_install_file: vs_SSMS.exe

service_username: sapphire\kuiper-gmsa$

# Create Databases
microsoft_sql_databases:
  - name: 'Kuiper'
    db_size: 4096 MB
    db_max_size: 10240 MB
    db_growth: 256 MB
    log_size: 1024 MB
    log_max_size: 10240 MB
    log_growth: 500 MB
    roles:
      - name: db_owner
        members:
          - sapphire\epicadmin # required while running the Kuiper install for SQL test to succeed
          - sapphire\kuiper-gmsa$
      - name: db_ddladmin
        members:
          - sapphire\kuiper-gmsa$
      - name: db_datawriter
        members:
          - sapphire\kuiper-gmsa$
      - name: db_datareader
        members:
          - sapphire\kuiper-gmsa$
    users:
      - sapphire\epicadmin # required while running the Kuiper install for SQL test to succeed
      - sapphire\kuiper-gmsa$
  - name: 'SystemPulse'
    db_size: 4096 MB
    db_max_size: 10240 MB
    db_growth: 256 MB
    log_size: 1024 MB
    log_max_size: 10240 MB
    log_growth: 500 MB
    roles:
      - name: db_owner
        members:
          - spadmin
      - name: db_datawriter
        members:
          - spuser
      - name: db_datareader
        members:
          - spuser
      - name: sp_user
        members:
          - spuser
    users:
      - spadmin
      - spuser

Azure SQL Database (SaaS)

The microsoft_sql role targets a SQL Server instance it installs and manages locally, so it does not apply to Azure SQL Database. That platform has no Windows or Kerberos integrated authentication, which means an AADDS-joined VM cannot connect as its machine account or as a gMSA — applications authenticate with a contained SQL user instead.

playbooks/ire/configure-azure-sql-db-user.yml provisions those users. It is idempotent: if a user can already log in and holds the roles it is meant to hold, it changes nothing; otherwise it creates or re-keys the user as the server admin, grants the requested database roles, and then verifies both the login and the role membership.

The SQL work is delegated to azure_sql_management_host — a Windows host with network line of sight to the server's private endpoint, defaulting to the first Kuiper server — because the Ansible controller is not on that VNet.

Each application declares its own databases in its group's group_vars, next to the vaulted credentials it references. azure_sql_databases has the same shape as microsoft_sql_databases — a list of databases, each with its users and its roles, whose members are drawn from those users — so both SQL platforms are declared the same way:

azure_sql_server: sph-ire-epicsql-centralus-sql.database.windows.net
azure_sql_databases:
  - name: Kuiper
    users:
      - name: "{{ kuiper_sql_user }}"
        password: "{{ kuiper_sql_password }}"
    roles:
      - name: db_owner
        members:
          - "{{ kuiper_sql_user }}"
Variable Description
azure_sql_server FQDN of the Azure SQL server.
azure_sql_admin_user Server admin login, used only to create or re-key users.
azure_sql_admin_password Server admin password. Falls back to the AZURE_SQL_ADMIN_PASSWORD environment variable.
azure_sql_databases Databases to provision, each with name, users, and roles.
azure_sql_management_host Windows host the SQL work runs from. Defaults to the first host in app_kuiper.

A users entry is either a bare login name or a mapping with a name and an optional password. Password precedence matches the microsoft_sql role: the user's own password attribute, then the azure_sql_<user>_password Ansible variable, then the AZURE_SQL_<USER>_PASSWORD environment variable.

Roles are additive — like the role's MembersToInclude, the playbook grants missing memberships and never revokes one. Every role member must be one of that database's declared users; the playbook asserts this before it connects.

azure_sql_admin_user / azure_sql_admin_password are the server admin credentials and are set once per environment — on Azure IRE they come from group_vars/ire_copier_azure_platform/vault.yml (see Secret Management).

Hosts that do not define azure_sql_databases are skipped, so a single run covers every application in the inventory:

ansible-playbook playbooks/ire/configure-azure-sql-db-user.yml

The databases themselves are created by Terraform, not by this playbook.